Securing Your SQL Server

Out of the box, SQL Server provides a fantastic development environment. It boasts a wealth of features such as sample databases, full-featured stored procedures, and other simple methods to create a system that has low administrative overhead.

However, all too often these convenient development tools, if left in place on a production system, can provide a backdoor to your network. Therefore, it is important to implement a standard for securing your SQL servers before they make it to production.

Authentication

Like most RDBMSs, SQL Server has the capability to manage its own security and maintains its users and their passwords internally in a table called sysxlogins.

SQL Server supports two methods of authentication:

• Native SQL Server authentication, known as Mixed Mode

• Windows authentication

You can configure the authentication mode at installation. The default is Windows authentication, but you can alter this at any time, as shown in Figure 8-7.

Figure 8-7. SQL Server Security Settings

Windows authentication uses Windows integrated security to automatically authenticate the user onto the database using the credentials with which he logged onto the domain. Mixed mode authentication, as its name suggests, allows a user to log in using either his Windows credentials or SQL Server-based security. To use either method, a login must have been explicitly created at the database server level. If the login is a SQL Server login, the password is stored as a one-way hash in the sysxlogins table.

Using Windows authentication is inherently more secure, because Windows manages password integrity such as minimum password length and account lockout after multiple invalid login requests. As previously mentioned, a SQL Server login stores passwords as hashes in a SQL Server table. If the authentication mode is changed from Mixed Mode to Windows Only, any SQL Server logins that already exist can no longer authenticate against the database server.

Service Accounts

During a standard SQL server installation, many services are installed by default. The only service that must be running for SQL Server to function is the MSSQLServer service. However, you can usually find the SQL Server scheduling agent, SQLServerAgent, enabled and running in a standard installation.

In addition to these two services, the following services are installed:

• MSSQLServerADHelper

• MSDTC (Distributed Transaction Coordinator)

• MSSearch Services

Disabling unused services to narrow the attack surface is a recommended best practice.

If a default install is selected, these services are installed to run under the Local System account.

The Local System account has certain privileges on the local machine, including these:

• Act As Part of the Operating System This enables a process to impersonate any user without the need for authentication, allowing the process to gain access to the same local resources as that user would have.

• Generate Security Audits This setting determines the accounts that a process can use to add entries to the security log. A hacker could take advantage of this privilege to add multiple events to the security log, either to cover his tracks or perhaps to initiate a DoS attack.

You should configure these services to run under a Domain User account that does not need to be a member of Local or Domain Administrators to function correctly in most cases. Ensure that the account that the services run under has minimum privileges required to function.

Public Role

Every SQL Server installation, like the Windows environment, has predefined roles. These different roles can affect privilege levels across the whole server or at an individual database level. One of the most important roles is the Public role, which is granted permissions at the database level. Every database created has the Public role added, and every user added to the database becomes a member of the Public role. You cannot change this. You cannot drop the Public role or remove users from it. Therefore, it is imperative that you carefully administer the permissions granted to this role.

Guest Account

SQL Server has an account named guest. If a guest account exists at a database level, any login, even if it does not have explicit access to a database, can access that database. Because the guest account is a member of the Public role, any user who accesses a database via this account already has the permissions that the Public role possesses without being granted access to the database. To mitigate the risk, ensure that your model database (the template from which all other user databases are created) does not contain the guest account. In addition, regularly audit database users to check that a guest account has not been created.

Sample Databases

Two sample databases, Northwind and pubs, are created during installation. Although these are useful tools for teaching and experimentation, for these reasons the Public role has generous access to them and their structure is also widely known by hackers. Always drop them from the database server prior to making it a production system.

Network Libraries

SQL Server supports several network-level protocols to communicate with its clients. These protocols include TCP/IP, IPX/SPX, Named Pipes, and AppleTalk.

To achieve this communication, SQL Server uses dlls (software components) called network libraries (or Net-Libraries) to communicate via one or more protocols. For this communication to take place, these network libraries must be in place on both server and client. The client initiates the connection and can only communicate with the SQL Server if both are configured to support the same network library.

Note

Do not confuse network protocols with network libraries. Although they often use the same name, some network libraries support more than one network protocol.

A default SQL Server installation comes configured with the following network libraries enabled:

• TCP/IP

• Named Pipes

Figure 8-8 shows the Server Network Utility with its default settings.

Figure 8-8. Server Network Utility

Although you cannot actually attack a SQL Server network library, this library does dictate the protocols that a client can use to communicate. Enabling more network libraries than you require increases the risk of database breaching in the same way that numerous doors and windows in your property increases the opportunities for a thief to enter. The following link provides more information on the protocols supported and a deeper insight into their function:

http://www.databasejournal.com/features/mssql/article.php/3334851.

Ports

SQL Server listens on TCP port 1433 by default and on UDP port 1434. As mentioned earlier, various port scanning tools utilize UDP port 1434 to enumerate SQL servers on a network. However, although this port is documented as the SQL Server Resolution Service port, in most cases, you can block it without impacting functionality.

Note

The SQL Slammer worm that caused widespread chaos in January 2003 exploited a buffer overrun via port 1434.

In addition, a function in the Server Network Utility (one of the tools that ships with SQL Server) offers a feature called Hide Server, as Figure 8-9 illustrates. This sounds pretty useful, and you might think that this would prevent something like a scanning tool from discovering your servers. Unfortunately, this option only changes your SQL Server TCP port from 1433 to 2433. Obviously, this change does not prevent UDP port scanning from taking place successfully.

Figure 8-9. Server Network Utility Showing Hide Server Option for TCP/IP