Locking Down Your SQL Server

A number of system stored procedures can provide a tempting treat for a hacker. For example, if a hacker is able to obtain access to the extended stored procedure xp_cmdshell, he could perform any number of command-line programs. The following code illustrates a typical scenario:

master..xp_cmdshell "net user hackeraccount /ADD"

This command creates an account for the hacker on your server. He could then issue the command to add himself to the Administrators group as shown here:

master..xp_cmdshell "net localgroup Administrators hackeraccount /ADD"

Keep in mind that these commands are issued under the credentials of the account that starts SQL Server. If the user who starts SQL Server doesn't have the right to add users, the hacker will not be able to issue this command.

As you can see, once the user has access to SQL Server, he can easily obtain access to other servers in the network. He could create shares to other machines, format drives, or issue endless other commands.

Watch out, too, for other extended stored procedures that can read and write registry keys. Keys can be written to your registry to affect the way your programs perform and render them essentially useless. These extended stored procedures include:

• xp_regwrite

• xp_regread

• xp_regremovemultistring

• xp_addmultistring

• xp_regdeletevalue

• xp_regenumvalues

Also be careful with the stored procedures for OLE automation. These stored procedures allow you to create any object that is registered to the Windows server and then execute methods or read properties from the object.

A malicious user could use the FileSystemObject, which all SQL Servers have preinstalled, to perform any number of command functions similar to xp_cmdshell. The following stored procedures should also be watched closely:

• sp_oacreate

• sp_oadestroy

• sp_oastop

• sp_oagetproperty

• sp_oasetproperty

• sp_oamethod

• sp_oageterrorinfo

You should also secure any other type of stored procedure that you believe may pose a threat. None of the stored procedures I've mentioned should be deleted. They're used throughout SQL Server for various functions such as job scheduling.

I'm telling you about these stored procedures so you will know what objects to monitor closely and secure. These stored procedures should be secured so that only users with sysadmin rights can access them.

For information about how to monitor for hackers, read Chapter 5. You can also read the appendices for more information about SQL Server system tables and stored procedures.

Locking Down the Directories and Registry Keys

Often, physical access to directories and servers is neglected in a security inspection. For example, if an employee at your company has access to the SQL Server data files or backups, he could take them offsite and reattach the databases or restore a backup. To help protect against this, you will need to ensure that your files are protected with proper NTFS permissions.

Tip 

If a user is able to get one of your SQL Server MDF or LDF files, he will be able to attach them on a remote system and have full access to your data. There are third-party products you can purchase or you can employ Windows encryption to encrypt those files and prevent this from occurring.

I would recommend starting at the directory and file permissions for SQL Server as a first step. I like to lock down the SQL Server data, backup, logs, and binn directories to only the account that starts the SQL Server services, Administrators, and the local system account. You will want to assign full control to these users and groups.

Caution 

Never make any change like this into production before testing them fully. Don't forget your third-party backup software. It sometimes needs access to directories that you may neglect to give.

The registry often goes neglected from being protected. By default, the SQL Server keys of your registry are vulnerable. You will want to remove the everyone group from the following keys:

HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE

Again, the account that starts the SQL Server services, Administrators, and the local system accounts will all need full control of this registry key. You can set the permissions for the registry by using the Regedt32 program, but again be very careful before implementing these changes into production.