del.icio.us

Lock Down SQL Server 2005

Last modified on Monday, 18 February 2008 at 05:56 UTC

This article teaches you how to lock down a SQL Server 2005 installation. A step-by-step procedure is given along with the concepts behind it. To show you the end result I provide a world-accessible SQL Server 2005 shell. That's not a recommended practice though :)

The SQL code shown in the article can be downloaded here:

LockDownSQLServer2005.sql applies server-wide security settings
LockDownDatabase.sql hardens a specific database
InspectSqlSecurity.sql inspects security information for the server and current database.

Overview

First off, you need to figure out what you're protecting, what the likely risks are, and who is likely to attack. This is threat modeling and without it you're lost. No amount of strategy can help when you fundamentally don't know what you're doing. Ask George Bush. Threat models are specific, but these assumptions usually hold:

You want the ol' confidentiality, integrity, and availability for your databases
Your server and network must be protected from attacks originating within SQL Server
You need as much protection from user's mistakes as from malicious attacks
Risk from application flaws, such as SQL Injection bugs, must be mitigated
Databases must serve users with diverse needs and privileges, ranging from low-access web application users to internal top dogs
Over the long run, zero-day exploits give attackers a window to subvert SQL Server to some degree

Three important security ideas guide the lock down strategy: least privilege, attack surface minimization, and defense in depth. These ideas apply on two different fronts. You want to protect your network and hosts from SQL Server (external security), and you also want to protect the data within SQL Server (internal security). It's useful to separate these two aspects, external versus internal, when you think about database security.

Protect your network

NEVER run SQL2005 Services as Local System or as an account with Admin privileges, that is madness.
Run each service under a different non-Admin account. Disable all unused services. Here are the steps:
1. Start > Run > SQLServerManager.msc
2. Go to SQL Server 2005 Services
3. Disable all the services you don't use. Double click the service, open the Service tab, change Start Mode to disabled.
4. You're left with the SQL2005 Services you need. If they are running as non-admin, non-LocalSystem accounts, you're done. Otherwise carry on.
5. Create Windows accounts for each service. I use accounts like SqlEngine, SqlAgent, SqlAnalysis, etc. You must decide between a local computer account and a domain account. Below are the steps for creating a SqlEngine local computer account for the database engine:
  1. Start > Run > compmgmt.msc > System Tools > Local Users And Groups > Users > Right Click > New User
  2. Enter SqlEngine for the user name. I suggest a random 20-character password stored in the excellent Password Minder.
  3. Uncheck 'User must change password at next logon', check 'User cannot change password'. The 'Password never expires' option is tricky. If you have a random 20-character password, you should be fine checking it. Otherwise, you need a procedure to change the password periodically. Click Create, then Close.
  4. Now back to the SQL Server Manager. Double click the 'SQL Server' service, and type the user name/password for the new account in the 'Log On' tab. You're done.
6. The steps for the remaining services are identical.
Apply SQL2005 patches as early as possible.
Run the Microsoft Baseline Security Analyzer and the SQL Server Best Practices Analyzer.

Restrict permissions on extended stored procedures. They offer high-value functionality to attackers. By default some are available to the public role, so all users can execute them. Thus any compromised account (e.g., a SQL injection in a low-privilege account) becomes dangerous. This fragment from LockDownSQLServer2005.sql fixes the problem:

-- Revoke EXECUTE permissions from public on all extended stored procedures. These procedures
-- aid attackers in compromising your server and network.
USE master

DECLARE DangerousStoredProcedures CURSOR STATIC READ_ONLY FOR
SELECT name
FROM sys.all_objects
WHERE type = 'X' OR (type = 'P' AND name like 'xp%')

DECLARE
	@SpName			sysname,
	@FullCommand	nvarchar(4000)

OPEN DangerousStoredProcedures
FETCH NEXT FROM DangerousStoredProcedures INTO @SpName

WHILE (@@FETCH_STATUS = 0) BEGIN
	SET @FullCommand = 'REVOKE EXECUTE ON [' + @SpName + '] FROM public'
	EXEC sp_executesql @FullCommand

	FETCH NEXT FROM DangerousStoredProcedures INTO @SpName
END

DEALLOCATE DangerousStoredProcedures

Disable please-hack-me options. Many of the server-wide options accessible via sp_configure have serious security implications. Most are off by default in SQL2005, but for completeness the lockdown script turns them off as well.
Run the SQL Server Surface Area Configuration Tool. The GUI binary is SqlSAC.exe and the command-line is sac.exe in the SQL_DIR\90\Shared folder. The "features" section in the GUI overlaps with the sp_configure options I mentioned above. Since SQL ships secure by default (rock on, Microsoft) there's not much to do here unless you enabled things explicitly in the past. Still, poke around and turn unused things off. Above all, turn off unused endpoints and network connections as these are obviously attack channels.

Protect your databases

Server-wide measures

Firewall SQL Server. By default SQL Server listens on TCP 1433 and UDP 1434. Block all incoming connections, and then make exceptions for hosts that need access.
Use Windows Authentication only instead of mixed-mode authentication, if possible.

Prevent users from seeing metadata for all databases:

-- Prevent users from seeing metadata for databases they can't access.
-- Unfortunately users can still enumerate all database names via db_name
-- This makes database enumeration slightly slower, but that's irrelevant in
-- scenarios.
USE master
REVOKE VIEW ANY DATABASE FROM public

Don't let all users connect to the msdb database:

	-- Don't give msdb access by default. Grant access only to users who need it. USE msdb REVOKE CONNECT TO guest
1 2 3

Disable xp_configure SQL options. This is mentioned in the Protect Your Network section but it also impacts internal security.
Encourage developers to use tools that are immune to SQL Injections. If developers join raw strings to build queries, they will introduce SQL Injections. Engage them in finding an injection-proof toolset or technique. For example, Linq-to-SQL in C# and ActiveRecord in Ruby on Rails both offer injection-proof querying.

Inspect server-wide security permissions and role membership:

-- View server permissions
SELECT
	State_Desc, Permission_Name, class_desc,
	COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
	Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
FROM sys.server_permissions Perms
INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
ORDER BY SecurableName

-- View membership in Server roles
SELECT Roles.Name, Roles.Type_Desc, Members.Name MemberName, Members.Type_Desc
FROM sys.server_role_members RoleMembers
INNER JOIN sys.server_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.server_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id

Audit security events. Otherwise your database will be compromised and you'll never know. Heck, it might be compromised already. You should audit enough to:
1. Stop an attack in progress
2. Notice that an attack happened
3. Figure out how an attack took place
Alerts must be automated. Relevant daily reports are useful, but only if they show real meat, otherwise they are rightfully ignored.
Remove BUILTIN\Administrators from the SQL Server logins. Create a different, specific Windows group for SQL administrators.

Per-database measures

This section presents measures to secure a specific database. Most are packaged in LockDownDatabase.sql for your convenience. You should execute the script for each database you wish to harden.

Don't let all users into your database:

	-- Keep the vagabonds out, make the database invite only. IF (db_name() NOT IN ('master', 'tempdb')) REVOKE CONNECT TO guest
1 2

Revoke all permissions from the public database role. The public database role is evil because you have no control over it. Every user who connects to a database is in the role, end of story. For historical reasons it is granted several permissions, but they can be safely revoked. The snippet below revokes all permissions from the public role and grants them to the FormerPublic role. This has three advantages:

You keep tabs on what the public permissions were (ie, you can rollback)
You can quickly grant all permissions to a user by adding the user to the FormerPublic role. The difference is that it happens at your discretion
You can grant permissions piecemeal

Public permissions are vestigial organs from a gentler era. Rip them out:

-- Move all of the permissions given to public to FormerPublic
CREATE ROLE FormerPublic

DECLARE PublicPermissions CURSOR STATIC READ_ONLY FOR
SELECT
	Permission_Name,
	COALESCE(O.name, DB_NAME(Perms.major_id)) SecurableName,
	SCHEMA_NAME(O.schema_id) [Schema]
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
WHERE Grantees.Name = 'public' AND Grantees.Type_Desc = 'DATABASE_ROLE'

DECLARE
	@PermissionName	sysname,
	@SecurableName	sysname,
	@SchemaName		sysname,
	@Arguments		nvarchar(4000),
	@FullCommand	nvarchar(4000)

OPEN PublicPermissions

FETCH NEXT FROM PublicPermissions
INTO @PermissionName, @SecurableName, @SchemaName

WHILE (@@FETCH_STATUS = 0) BEGIN
	IF (@SecurableName IS NOT NULL) BEGIN
		SET @Arguments = @PermissionName + ' ON '
			+ COALESCE('[' + @SchemaName + '].', '') + '[' + @SecurableName + ']'

		SET @FullCommand = 'REVOKE ' + @Arguments + ' FROM public'
		EXEC sp_executesql @FullCommand

		SET @FullCommand = 'GRANT ' + @Arguments + ' TO FormerPublic'
		EXEC sp_executesql @FullCommand
	END

	FETCH NEXT FROM PublicPermissions
	INTO @PermissionName, @SecurableName, @SchemaName
END

DEALLOCATE PublicPermissions

Restrict access to metadata. Most SQL applications use ready-made queries and don't dynamically query metadata. So why leave metadata accessible? Microsoft's SQL2005 Security Best Practices say that "the catalog views are secure by default" but I say they are a SQL Injector's best friend.

The script above blocks public access to database-level metadata objects. Server-wide objects are still publicly accessible in the master database. If possible, run the lockdown script against master to block metadata access fully (the SQL shell demo is locked in such a way). You can then grant access when required.

Specifically, if you harden master by revoking permissions from public, regular users cannot use SQL Server Management Studio to connect to SQL Server. This may be exactly what you want. If it's not, you have two options. You could add the users that need Studio access to the FormerPublic role, or you could create a MSSQLStudio role and grant it minimal permissions. I've found that granting SELECT on sys.configurations, sys.databases and sys.database_mirroring is enough to connect via MSSQL Studio (however some features may still be blocked, I have not tested this extensively).
Use schemas and roles to give database users least privilege. In SQL2000 enforcing least privilege was a thankless pursuit because there was no easy way to group objects (e.g., tables, stored procedures) and assign permissions on the group. But SQL2005 schemas provide exactly that: they are containers for your database objects and enable group-wide permissions. Coupled with roles, which are containers for users, schemas make it easy to apply least privilege. So do this:
1. Create database roles modeled after reality (e.g., HelpDesk, Developers). Group your users into the roles.
2. Group your tables into schemas according to usage. For example, the CreditCards table goes in one schema, whereas TroubleTickets goes in another. One hopes.
3. Grant permissions on schemas to roles. So permissions on each group of objects are given to each group of users.
You have two conflicting goals: 1) Give each role least privilege, 2) Keep it simple by minimizing the number of GRANTs, DENYs, schemas, and roles. I lean towards simple over strict least privilege, but it's easy either way. This can be especially painless if you leverage Windows groups in your role configurations.

Inspect database permissions and role membership:

-- View membership in Database Roles
SELECT Roles.Name, Roles.Type_Desc, Members.Name MemberName, Members.Type_Desc
FROM sys.database_role_members RoleMembers
INNER JOIN sys.database_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.database_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id

-- View Permissions in database  (you can also run sp_helprotect)
SELECT
	State_Desc, Permission_Name, class_desc,
	COALESCE(O.name,DB_NAME(Perms.major_id)) SecurableName,
	SCHEMA_NAME(O.schema_id) [Schema], Grantees.Name Grantee, Grantees.Type_Desc
FROM sys.database_permissions Perms
INNER JOIN sys.database_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
ORDER BY SecurableName

Encrypt highly confidential data. For any piece of complex software, zero-day exploits will come along and let attackers have their way, fleeting as it may be. Luckily the same can't be said for number theory. So if you have truly sensitive data, protect it with SQL2005's encryption features and survive the exploits. Go Euler! 236 years of bug-free dope.
Use CREATE USER instead of the old sp_adduser because the latter creates both a user and a schema with the same name, which leads to a mess.
Rely on Transact-SQL instead of the GUI to manage your server. Despite an initial hit as you acclimate, doing your administration via T-SQL is far more efficient. From a security standpoint, T-SQL gives you more clarity and understanding of what is taking place, the chance to package tasks in stored procedures, transactions and rollback, version control, and so on.

Prevent Denial Of Service (DoS)

Threat models differ a lot regarding DoSes. SQL2005 is nearly bulletproof with respect to crashing, so the issues are around resources (CPU, memory, and I/O). People worry about two things: protecting SQL Server from a rogue query and protecting the computer itself from SQL Server (in computers doing multiple jobs). Threat modeling should clarify your particular requirements or lack thereof.

Fencing the SQL Server hog

You can tell SQL Server the maximum memory it should use, in megabytes:

-- Tune according to your server's memory and situation
-- BEWARE: If you set this to a value that's too low, you can SLAUGHTER your server's
-- performance
--
EXECUTE sp_configure 'max server memory (MB)', 1024

The downside is that a stressed, memory-constrained SQL Server kills its disk subsystem. But if SQL has its own disks, or if the RAM is important for some reason, there you have it.

You can not tell SQL Server its maximum CPU allotment. Use the Windows System Resource Manager to enforce a limit. This is an effective way to conserve your server's CPU time (and by extension other resources if SQL blocks on the CPU). It hurts SQL performance of course.

Molly's report crashed the website!

Inside SQL Server we want to guard against runaway queries that monopolize resources. I don't know of a good solution here (SQL2008 is fixing this). Below are the 2 crappy hackarounds I sometimes use, but if you know better I'd love to hear it.

You can use the query governor cost limit option to try to prevent offending queries from ever starting. There are two serious drawbacks: 1) you might introduce subtle bugs as queries get mysteriously capped, and find yourself lynched by developers; 2) the option doesn't work well in many real-world scenarios where the load is due to statements in loops and batches.
A crude but effective option is enforcing a timeout at the client. If your client is .NET, set SqlCommand.TimeOut to a value appropriate for the types of tasks the client should be performing. When queries or batches exceed this threshold, the client aborts and so does SQL.

Certain unruly queries are immune to both the cost limit and timeouts. A nasty example is WHILE(1=1) SELECT 1. It can bring a server to its knees without expiring its timeout because data arrives furiously. The only method I know to curb this is by using a (here comes) thread abort on the client side (ugh). The upside is that while gross the technique works well, like colonoscopy. Here's some generic C# code for executing a delegate with a timeout.

Try it out

Below is an interface where you can try out queries against a live database that I protected following the steps in this article. Rules:

Feel free to try to escalate your privilege in the server. If you succeed, please let me know so that I can fix the server, amend the article and give you credit.
Queries are set to timeout after 1 second. Please don't DoS the server via resource usage, it's a puny computer.

Masochistic SQL Server, an experiment

Table Quotes allows INSERTs with nvarchar columns Author and Quote.
SELECT * FROM Last10Commands shows the last 10 SQL commands executed in the database.
Stored procedure GetRandomQuote gives you a random quote

Enter query:

Result set:

No results.

Known Issues

Below are queries I would like to block but don't know how to:

-- Any user can enumerate all of the databases in the server, despite REVOKE
-- VIEW ANY DATABASE FROM public (the metadata is blocked, but the database
-- names are readable).
SELECT db_name(1), db_name(2), db_name(3), db_name(4) --etc

-- This will tell an attacker exactly what you're running.
SELECT @@version

-- The server's hostname is revealed via
SELECT @@servername

-- SERVERPROPERTY gives out a lot of information.
-- In particular, any user gets to see whether you run in mixed-mode:
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

Conclusion

SQL2005 is reasonably secure out of the box, but there's a lot you can do to tighten it further. Despite Microsoft's excellent work, the resulting security of your databases depends heavily on your practices, configurations, and permissions model. The steps discussed here will help you run a more secure SQL Server. And Euler rulez.