Chapter 3: Securing SQL Server

Securing your SQL Server can be an arduous task, but very rewarding. Nothing makes me prouder than running a penetration test on a server that we just secured and not seeing any hot spots. This chapter focuses on the ways to secure your SQL Server and common entry points for hackers.

Managing Security

SQL Server has two security models. One model is the Windows Only mode. This mode only permits users who have trusted Windows NT accounts to log in to SQL Server. This is the default, and most secure, option. The other security model is SQL and Windows Authentication mode. With this mode, the server accepts connections from Windows accounts as well as standard SQL Server logins.

If your SQL Server is set to Windows Only mode and a user tries to log in with SA or another standard SQL account, he or she would receive the following error:

Caution 

If you assign a Windows NT user to a Windows group and the group is assigned to SQL Server as well, the user will not receive the rights of the group until the user logs off the network and then logs in again. Additionally, if you change the user’s name in Windows, it will not reflect in SQL Server.

Before going further, I must emphasize a typical misunderstanding in SQL Server. SQL Server has two basic levels of security: logins and users. Logins allow access to the server and hold server-level permissions—for example, if the user is a system administrator (sysadmin) of your SQL Server. Users are at the database level and hold permissions to individual objects.

Simply put, when you attempt to log in to the server, SQL Server validates your login, but as you attempt to access items in a database, SQL Server validates your user account.

In the Trenches

When using Windows authentication, all the password information is stored in the Windows NT SAM and is not stored in SQL Server at all. With SQL authentication, SQL Server commonly stores passwords in clear text in the registry and in log files. This provides a large security hole for those who want to cause harm to your system.

Tip 

When creating logins, try to stay unpredictable with your standard. For example, the standard login of bknight (first initial and last name) may be too predictable to deter a hacker. Consider adding a letter before your login name, such as y to make the login ybknight.

Tip 

In a one-product environment, I typically recommend that you create two Windows 2000 or NT groups, one for the SQL Server administrators and one for the people who must read and write to the database. Then add the users into each group that must be assigned these rights. Create these two groups in SQL Server and you’re done. If your security policies are properly set up, users will be forced to change their passwords every month, so you never have to worry about a password being compromised. This also takes the onus off user management in SQL Server and places it on the domain administrator. You will no longer have dozens of users on your phone daily wondering what their SQL Server password is.