Locking Down SQL Server | Security for Microsoft Visual Basic .NET

There are a number of configurations and best practices you can use to further secure SQL Server:

Restrict Access to Directories You should ensure that SQL server is installed to a Windows NT file system (NTFS) drive. During setup, the installation directory and the database directory will be secured to allow access to the SQL Server service account and members of the Administrators group only.
Use a Low-Privilege Account During installation, you can choose an account for the SQL Server service to run under. This account should be a low-privileged domain user account, with minimal rights (but including the right to run as a service). The reason for using a low-privilege account is to ensure that even if an intruder does manage to break into the SQL Server machine and take over SQL Server, he will be restricted to a domain account that has few privileges. For information on setting up an account for SQL server with enough permissions, see the MSDN article at http://msdn.microsoft.com/library/en-us/instsql/in_overview_6k1f.asp .
Remove the xp_cmdshell extended Stored Procedure The extended stored procedure xp_cmdshell is very powerful, and potentially disastrous. This stored procedure allows the caller to execute system commands (also known as DOS commands) from within SQL Server. For example, the Transact SQL command
```
exec xp_cmdshell ’dir’
```
performs a Dir command, and the Transact SQL command
```
exec xp_cmdshell ’del MyImportantProgram.exe’
```
would delete the program MyImportantProgram.exe, should it exist. The command is run under the permissions of the SQL Server service account. If an intruder gains access to SQL Server and has permissions to execute xp_cmdshell, he can do anything the SQL Server service account is permitted to do. For this reason, it’s recommended you remove xp_cmdshell unless it’s absolutely necessary. You can permanently remove this extended stored procedure by running the following script from SQL Query Analyzer:
```
USE master
go
DROP PROCEDURE xp_cmdshell 
```
Restart SQL Server When Permissions Change If you remove permissions from a user or delete a user and you need to ensure the change becomes effective immediately, you should restart SQL Server. This is because, for performance reasons, SQL Server caches permissions for each user session. Because restarting SQL Server will cause downtime, only do this if it is absolutely necessary to delete the user immediately.
Restrict Access to a Single Computer If your Web site uses SQL Server, you should limit SQL Server access to only the Web server— disallowing access from the Internet or other nonauthorized computers. Although SQL Server does not have native support for restricting access to a particular computer, you can implement this using IPSec, which is available in Windows 2000 and later. IPSec allows you to restrict access based on IP address or port. It also can be used to ensure secure communication between two servers. For information on using IPSec, see the MSDN article at http://msdn.microsoft.com/library/en-us/dnnetsec/html/secnetht18.asp .
Encrypt Data Using Secure Sockets Layer SQL Server can encrypt data passed to and from a client using Secure Sockets Layer (SSL). (For an overview of SSL, see Chapter 5.) Using SSL results in a slight drain on performance, but it ensures the data is not intercepted or tampered with. For information on setting this up, see the MSDN article at http://msdn.microsoft.com/library/en-us/dnnetsec/html/secnetht19.asp .
Don’t Use SA or Hard-Code a Password For SQL Servers that have to use SQL Server Authentication, it is common for people to write Visual Basic .NET applications that hard-code a username and password into the ODBC connection string, global.asax file, or web.config file. To make matters worse, they sometimes hard-code the SA account with the associated password. This should never be done because anyone with a decompiler can examine the program and figure out the administrator password to the database. The best practice is to remove the SA account (so intruders are not tempted to use it to attempt to break in) and instead of hard-coding a password, store it in an encrypted file. (See Chapter 1 for how to do this.)
Turn On Auditing You can capture successful and failed logon attempts. Logging is turned on through SQL Server Enterprise Manager. Right-click the Server node, and choose Properties to open the SQL Server Properties dialog box, as in Figure 12-4. On the Security tab, you can turn on auditing. Be aware, auditing will show you whether people are trying to unsuccessfully break into the database, but it might not help you identify the intruder because SQL Server includes only limited information in the log—it doesn’t include the IP address the unsuccessful logon originated from.

Figure 12-4: Turn on auditing in SQL Server Enterprise Manager