Security Audits of SQL Server

The best way to detect that you have an intruder is to put the proper alarm system up. By enabling the Failed Login option (Server Properties | Security tab), you give yourself a tool to see when an unwanted visitor is attempting to access your system. This is especially useful when you have a canned application that only uses a few accounts. If you see any failed logins at all, you know the application is not causing it, so it must be a user. The next step is to turn on Profiler and capture only Failed Logins and the Hostname. That will tell you what computer name the unwanted visitor is coming in from.

Turning on this type of auditing won’t do you a bit of good unless you actually monitor the logs or set up the proper alerting system to alert you when the entry comes through. One of the best ways to do this is to set up SQL Alerts to alert you when these errors come in either through NET SEND or through e-mail.

In environments where you have a large number of servers, you may want to quickly change the auditing level through a registry key. To change the auditing level, simply change the AuditLevel value in the HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\MSSQLServer\MSSQLServer key. Setting the value to 0 means no auditing will be turned on, 1 means successful logins will be audited, 2 means failed logins, and 3 means all logins will be audited. While setting this option to 3 (all activity is logged) is preferred, it may fill up your NT event log and SQL Server log quickly with a lot of noise.

You can also glean valuable security data from your SQL Server by using the xp_loginconfig stored procedure:

master..xp_loginconfig

Running this would give you the following results. As you can see, the Audit Level row contains information about how this server is being audited (failed logins only).

name                         config_value                  ---------------------------- ----------------------------  login mode                   Mixed default login                guest default domain               DOM audit level                  failure set hostname                 false map _                        domain separator map $                        NULL map #                        -

Note 

You will need to stop and start your SQL Server after changing the auditing level.

I also like to turn on auditing of any type of permission-denied error, like error #229, which is the typical error a user would receive if they’re trying to access an object they don’t have rights to. If you find all the items you’d like to audit, you can write a script to update the sysmessages table (which holds all the SQL Server errors) to turn on logging as shown here:

UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229 

If you’re a hacker and you wanted to hide your activity in SQL Server, the ideal way to do this would be to rollover the error log through DBCC ERRORLOG five times, thus eliminating the evidence that you were there. To defend against this, I recommend that you add a registry key (if it doesn’t already exist) to increase the number of logs that SQL Server will keep from 5 to at least 10. By adding the DWORD value of NumErrorLogs into the following registry key you can specify how many logs SQL Server will keep:

 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]

Tip 

To help you better audit changes in the various environments, always create your security changes in a T-SQL script. That way you can ensure that the change will be the same across all environments. It also ensures that the change can be placed into a source control system and can be audited better.

C2-Level Auditing

Beginning in SQL Server 2000, SQL Server was certified to be C2-level compliant. This means that SQL Server can be configured to use this stringent government standard of auditing where every database action can be audited. Essentially, every database login, logoff, use of rights, and access to any SQL Server object is audited in this mode. Turning on C2-level auditing can be done through the sp_configure stored procedure:

sp_configure 'c2 audit mode', 1 GO RECONFIGURE GO

After running the command, a restart of the SQL Server services is required. After that, SQL Server will create a SQL Server trace file in the Data directory that can be read in Profiler. Audit data is written to the trace files in 128K blocks. After you enable the feature, you will notice the file will remain at 0K until the first 128K block of audit data has been written to the trace file or the SQL Server service stops. After 200MB of data is written to the trace file, the log file closes and opens a new file.

Caution 

Since all database activity can be audited, this type of auditing can be taxing on your server’s performance. Make sure you test this fully before implementing to make sure the degraded performance is acceptable. Additionally, you will need to ensure that you have plenty of space on a server that is performing this type of auditing. If the drive holding the SQL Server default Data directory fills, the SQL Server service will stop until space is freed up.

Tip 

If your server locks up due to space issues with the C2-level audits or hangs, you can startup SQL Server using the methods discussed in Chapter 2, “Tweaking Startup Parameters.”  The -f parameter starts SQL Server in minimal mode.

As with any type of security, the more you secure an application, the more you generally affect the overall performance of the application. Pay special attention to this if you’ve been given the instructions to perform this type of audit. You must also keep in mind that the server cannot be considered fully C2-level compliant unless the Windows operating system is using C2-level auditing, which is additional performance overhead.

Auditing for Blank Passwords

Finally, consider auditing regularly for logins that don’t have a password. You can do this through a simple query (note that Windows accounts never store a password so the isntname = 0 parameter filters those out):

use master go SELECT name, password from syslogins where password is null and name is not null and isntname = 0

Changing Passwords

Standard login passwords can be changed by the individual user by using the sp_password command. Additionally, logins that are a member of the sysadmin and securityadmin fixed server roles can change another user’s password. For a user to change his password while he’s signed in, he can execute the following command:

EXECUTE sp_password 'oldpassword', 'newpassword'

If you want to change the password of another user, you can use the following command:

EXECUTE sp_password NULL, 'newpassword', loginname

Caution 

The domain user’s password that starts the SQL Server services should not be set to expire. If the user’s password expires, the SQL Server service will not restart and an outage will occur.

Sacred Accounts

Hear that creak? That’s me, stepping onto my soapbox to talk about a sacred account in SQL Server. As you may know, SQL Server installs the SA account at setup and gives that account administrative access to the database server. The SA account cannot be removed or demoted in its power. The best you can do is change the password, lock it in a safe, and never use it.

At the majority of companies with which I have consulted, I’ve seen developer after developer use the SA account to connect to SQL Server within their applications. I’m frequently told, “We’re only using it for testing and then we’ll change it to the way it should be before going to production.” These, of course, are famous last words.

A number of bugs with Internet Information Server (IIS) have been released over the past few years that allowed anonymous Internet viewers access to see the connection string to your server when they append certain values to the end of a URL. Never, under any circumstances, should the SA account be used.

Note 

If you assign a user to the sysadmin server role, you give him the equivalent access that the SA login has. If you want the user to have administrator rights to all the databases and the server, no further action is required.

Another best practice that should be followed is to start SQL Server with an account that has narrow access to the Windows 2000 server. Bugs could allow users to obtain access to whichever account starts SQL Server. Always deny this account the right to log in interactively. This prohibits a user from logging in to the server with this account and logging in to SQL Server from the console through Windows Authentication.

Caution 

Extended stored procedures like xp_cmdshell run under the security context of the user that starts SQL Server. xp_cmdshell allows a user to execute any command-line program. If you don’t watch the security this account has, a malicious user may be able to obtain access to your Windows server and launch attacks against your network. This is another reason to avoid having applications connect with a user who has sysadmin rights. If the user has sysadmin rights, he in turn has rights to execute any extended stored procedure.

Once you have the SA password changed, make sure you continue to change it periodically to avoid the word slipping out about the account. Have a process in place so if anyone who knows the SA account were to leave the company, you can change it across every SQL Server in just a few hours’ time.

Tip 

A paranoid DBA like me often wonders how many SQL Servers are on the network that I don’t know about. You can use a free tool by E-Eye to scan for SQL Servers with no SA password in your network (or ones that have a password, for that matter). To download the Retina SQL Worm Scanner (Version 1.0.0.0), go to http://www.eeye.com/html/Research/Tools/RetinaSqlWorm.exe .

SQLsnake Worm

In 2002, a worm named SQLsnake began to attack SQL Servers that had no SA password. Once it found a server with no SA password, it attacked the system in the following order using the xp_cmdshell extended stored procedure, which allows a user to shell to a command prompt and execute a DOS command:

• Enables the domain’s Guest account if it wasn’t already enabled.

• Grants the guest account Administrator rights to the local machine.

• Grants the guest account Domain Admin rights to the domain.

• Copies itself to the system directory.

• Gathers information about the databases, network interfaces, and the Windows password hash into a file.

• E-mails the file to [e-mail], which is an e-mail address believed to be originally owned by the worm’s author.

• Begins to look for other SQL Servers to infect within and outside the network.

Before the wave of attacks completed, an estimated 25,000 systems had been affected, but what is even scarier is that this could be only the first attempt of the worm. Back in November of 2001, a similar worm infected a modest amount of SQL Servers but did not propagate as well as SQLsnake. To prevent your SQL Server from being infected, take the following action:

• Change your SA password to a non-blank and hard to crack password.

• If you’re not blocking TCP port 1433 and UDP port 1434 to untrusted areas, do so now.

• Make sure your SQL Server is not running under an account like LocalSystem or a domain account with lots of permissions, like a Domain Admin or an Account Operator.

If you think there’s a risk of infection in your network, you can run a free tool by E-Eye to scan your network for infected machines. The SQL Scanner is available for free on their web site (http://www.eeye.com/).

An additional word of warning is that once the worm infects your outer SQL Server, it can begin to attack and infect other SQL Servers in your network that may be on the other side of a firewall. What worries me most is the ratcheting up in intensity of these worms. This is not a SQL Server flaw, but a user flaw. In a way, it’s like locking the doors of your house, but leaving the window wide open next to the door. No matter what the environment, including development, never have an SA password that is blank.