Best Practices for Mixed Authentication

DBAs and application developers have to make security decisions that will provide the best protection for their systems. Although every system is at risk to some degree, DBAs and developers who work with SQL Server can improve their systems' security by understanding the ramifications of the authentication mode they choose.

SQL Server 2000 and 7.0 provide two authentication modes: SQL Server and Windows authentication (also called mixed authentication) and Windows integrated authentication. Mixed authentication lets applications connect to SQL Server by using accounts and passwords stored in SQL Server tables or in a Windows domain or local machine. Although mixed authentication is easy to use, it lacks account-lockout capabilities and can expose your systems to attack through SQL Server's vulnerable and often mismanaged sa account. Windows authentication, which requires you to use a Windows account for all database connectivity, provides a mechanism for account lockout and eliminates the security risks associated with the sa account. It also provides additional logging through Windows Security Event Viewer logs. But convincing an application developer to use Windows authentication, which is more complex to implement, might be difficult.

Windows authentication is clearly the more secure option, and you should use it whenever possible. However, business or application requirements sometimes dictate the use of mixed authentication. For example, some third-party applications support only mixed authentication, and certain programming languages such as Java don't support Windows authentication for SQL Server connections. In other instances, application architects might determine that mixed authentication provides the fastest and easiest path for development, or you might have to work with existing applications that use mixed authentication until you get the time or personnel to rewrite them to use Windows authentication. Whatever the reason, if you have to use mixed authentication, you need to understand the vulnerabilities your SQL Server systems face and how to protect them.

Easy Isn't Necessarily Best

Before looking at mixed authentication's shortcomings and how to protect against them, let's quickly look at why developers and DBAs might mistakenly choose mixed authentication or be reluctant to use Windows authentication. Many application developers and SQL Server DBAs who use mixed authentication aren't aware of the dangers involved. These DBAs and developers usually choose mixed authentication for two reasons. First, most examples that developers find on the Internet or in books show SQL Server connection strings that use SQL Server accounts and don't explain how to use Windows authentication to connect to SQL Server.

Second, most DBAs and developers believe that writing applications that use mixed authentication is quicker and the resulting code is easier to debug. Windows authentication can create an additional layer of application complexity. Because this authentication mode requires you to use a Windows account for all database connectivity, Active Server Pages (ASP) developers, for example, might have to use COM+ to access SQL Server instead of embedding connection information directly in ASP code. When developers use mixed authentication, they code a username and password in a connection string in the ASP page. With this storage method, developers always know the account through which a user is connecting to SQL Server. In contrast, when developers use Windows authentication on a simple ASP page, the account through which a user logs on to the ASP page is also the account that connects to SQL Server. In most cases, the account a user uses to log on to the Web server that hosts the ASP page is the Microsoft IIS anonymous access account (IUSR_machinename). Most DBAs don't want to give this anonymous account access to SQL Server—a wise choice. But this restriction forces the developer to provide one of two logon methods for users. The developer can require users to log on to the Web server that hosts the ASP page by using IIS authentication or a Web form on the ASP page. Or the developer can create a COM+ .dll file and use the properties of the COM+ package to determine what account the ASP page calls to connect to SQL Server. As you can see, the Windows-authentication option seems more complex.

In addition, Web-application developers often use mixed authentication because it provides the easiest mechanism for using ADO to connect to SQL Server databases. And because ADO powers ASP-based, database-driven Web applications, Web developers might think that mixed authentication is their best choice.

Although Windows authentication can be more complex to implement, developers who take the time to fully understand how to use Windows authentication can alleviate the potential security holes that mixed authentication creates. Developers often have to rewrite ASP applications anyway because of mixed-authentication security concerns. Companies need to decide whether writing applications the fast and easy way only to have to rewrite them later is better than taking the time to write more secure applications in the first place. Now, let's look at the specific problems associated with mixed authentication and the sa account and how you can protect against them.

Dangers of the sa Account

To help you fully understand the security problems associated with the sa account and mixed authentication, let's first examine the SQL Server setup process. During SQL Server installation, the sa login is automatically created regardless of the authentication scheme you choose. With Windows authentication, users can't use the sa account to log in to SQL Server, but with mixed authentication, they can. SQL Server adds the sa account to the sysadmin fixed server role, and no one can remove it. The sa account provides irrevocable administrative-level access to SQL Server.

Also during installation, the installer must assign an account to start, stop, and pause the service that controls SQL Server. Often, installers select the LocalSystem account instead of an account that has a lower level of privilege because if you choose a lower-level user account and someone accidentally resets the account password, you might not be able to start the service. When you choose the LocalSystem account, you can be assured that the service will always start, regardless of password or permissions changes. However, the LocalSystem account has complete access to all functions in the OS, including the ability to add user accounts, reset passwords, and add users to groups. In this configuration, when a user connects to SQL Server through the sa account and executes a command, the command runs as if LocalSystem initiated it. If attackers gain access to SQL Server through the sa account, they can use commands inside certain stored procedures to add a user to the system, add that user to the local administrators group, and access dangerous system utilities and the Windows registry.

One of the biggest dangers of the sa account is that if attackers discover its password, they can access your SQL Server system. In mixed-authentication mode, SQL Server doesn't lock out users after a specified number of incorrect login attempts. Thus, an attacker can use a password-cracking tool to discover the sa account password. Even when you choose Windows authentication, you still need to set a strong password for the sa account (i.e., one that's at least 12 characters long and includes a combination of uppercase, lowercase, numerical, and ALT characters). Because with Windows authentication you can't use the sa account to log in to SQL Server, many administrators who use Windows authentication don't bother to set a password for sa. But if for any reason you switch from Windows to mixed authentication, the sa account password will be blank—and vulnerable to attack.

In the same vein, ASP and Visual Basic (VB) developers who realize that using the sa account for ADO connections isn't wise can still get into trouble. Instead of using sa, these developers create SQL Server logins and configure them with the appropriate level of permissions for accessing databases, tables, and other objects. But because their applications aren't using the sa account, these developers get a false sense of security and set a simple sa account password instead of creating an appropriately long, complex password. So although developers might correctly configure an application to use lower-privilege accounts, SQL Server might still be vulnerable to direct attack through the sa account.

Attack Mode

Attackers are familiar with the sa account's unique characteristics. Attackers can easily determine which authentication mode you're using as long as they can connect to the TCP port that SQL Server listens on. In SQL Server 2000 releases before Service Pack 3 (SP3), attackers have a quick, easy method for identifying SQL Server's listening port; they can query an unnamed service that I call the SQL Advertisement Service, which listens on UDP port 1434. This service has an unchecked buffer that the SQL Slammer worm exploited (SP3 fixes this vulnerability). When an attacker queries this service, SQL Server 2000 returns the names of all available SQL Server instances and the port that they're listening on. Attackers can also find available SQL Servers by using port-scanning tools that try to find servers that are using TCP port 1433, the default SQL Server listening port, or port 2433.

After attackers identify an available SQL Server by querying the Advertisement Service or scanning ports 1433 and 2433, they can easily figure out which authentication mode you're using. Attackers simply open Query Analyzer, enter the IP address of the SQL Server, type sa for the username, leave the password blank, and click OK. If the subsequent error message says Login failed for user 'sa,' the SQL Server is using mixed authentication. If the error message says Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection, the SQL Server is using Windows authentication. Attackers typically attempt to access servers that return the first error message.

The attackers' next step is to try to determine the sa account's password. Many tools—including SQLdict at http://ntsecurity.nu/toolbox/sqldict and sqlbf at http://www.sqlsecurity.com/scripts.asp—are available for revealing the sa account's password through a dictionary attack or a brute-force attack. Security auditors use tools such as these to test the strength of SQL Server login passwords, but attackers also have easy access to the tools.

Password attacks work well because SQL Server lacks account-lockout capabilities. But the APIs that the cracking applications depend on to create logins and send them over your network usually incur enough performance overhead to make them less useful for an impatient attacker. However, Network Intelligence India has a utility called Forcesql (available at http://www.nii.co.in/research/tools.html) that creates the login and sends it over the network itself, avoiding the performance overhead. An attacker or auditor using this tool can specify a character set and password length for a brute-force attack. Forcesql can generate about 200 to 300 password guesses per second. During initial lab testing of Forcesql, I completed more than 1 million password attempts in 90 minutes. Because such tools are easy to obtain, DBAs and developers need to create sa passwords that are long and complex enough to dissuade attackers.

Protecting Your System

Let's look at secure password techniques and other steps you can take to provide additional security for your system in a mixed-authentication environment.

Set a strong sa password. You should use the sa account only when you can't use any other account for administrative purposes. This means you'll use the sa account rarely, so you can set the password for the maximum length. For example, SQL Server 2000 and 7.0 allow a 128-character password. If such a lengthy password seems impractical, you can use the first few sentences of a book or employee manual as a password so that the person who has sa permissions doesn't have to memorize the password. If you want to use a shorter password, you should still use at least 12 characters and include a combination of lowercase, uppercase, numeric, and (if possible) special ALT characters. Few brute-force tools can crack passwords that contain ALT characters such as © and ®. (For a list of available ALT characters, use the built-in Windows utility Character Map—charmap.exe.)

Audit logins. The cornerstone of proactive security administration is auditing. SQL Server lets you audit logins by using the Application log and by using SQL Server Profiler. The Application log has one significant shortcoming: SQL Server logs all login events, regardless of whether they succeed or fail, as Category (4) and Event ID 17055. Several other types of events also use this Category and Event ID combination. So unless you use an additional utility that can further parse the event-viewer logs to find event descriptions, you have the daunting task of reading every log entry to determine whether someone is trying to guess SQL Server logins. Also, the Application log doesn't record the connecting client's IP address for DBAs to use in tracking down attackers. Profiler can track failed login attempts in a more granular fashion, showing an administrator specific login failures, the account name a client used, and sometimes a hostname.

Proactively detect intruders. To compensate for the lack of a robust SQL Server logging mechanism, organizations should consider deploying an intrusion-detection system such as the open-source IDS Snort (available at http://www.snort.org). Snort logs date, time, and IP information for clients that connect to SQL Server, letting you easily see when someone is repeatedly trying to access your system. If you configure SQL Server to use the Windows Application Log for auditing events, consider using a syslog facility (such as those available from Kiwi Enterprises at http://www.kiwisyslog.com) to search through SQL Server auditing events and discover repeated access attempts. Many syslog tools are available for Windows servers.

Block the SQL Advertisement Service. Because attackers can easily locate and query available SQL Server 2000 servers by querying the SQL Advertisement Service, you can protect your system by preventing SQL Server from answering these queries. Open the SQL Server Network Utility and click TCP/IP, Properties. Then, select the Hide server check box, and click OK. You'll need to restart SQL Server for the changes to take effect. When changing the Hide server setting, note that you're changing the TCP port that SQL Server uses from port 1433 to port 2433. Changing the default SQL Server listening port creates more complexity for ASP applications that connect to SQL Server because you need to configure such applications to use the nonstandard port. Because of this additional step, most administrators don't make this change.

Limit client access. You can also help protect your system by considering how clients interact with SQL Server and how open your SQL Servers are to queries from wanted and unwanted clients. In many environments, any client on the internal network can access SQL Server. Decreasing the number of clients that can access a system directly decreases the potential for attack. Where possible, place SQL Servers on a segmented network that provides access only to servers or clients that must access SQL Server data. To limit direct access, you can also deploy a multitiered application in which the only system that accesses SQL Server is the server running the application's data-access code.

Disable defaults that make you vulnerable. After an attacker has gotten into a SQL Server system by using the sa account, complete server compromise is easy. By default, SQL Server installs many stored procedures to perform OS tasks such as reading and writing to the Windows registry. The most powerful of these is the xp_cmdshell extended stored procedure, which lets SQL Server run command-line tools. If SQL Server is configured to start as LocalSystem or an account in the local Windows Administrators group, an attacker can use the following set of SQL commands to create a Windows user account in the local Windows Administrators group:

xp_cmdshell 'net user SQLPen /add'xp_cmdshell 'net localgroup Administrators SQLPen /add'

An attacker can also use xp_cmdshell to invoke the Trivial FTP (tftp.exe) client utility in the system32 directory. Attackers can use this utility to install malicious programs such as Trojan horses, remote-control software, keyloggers, or rootkits. Rootkits are perhaps the deadliest form of malicious software because many rootkits are capable of completely hiding their presence from DBAs while giving attackers complete access to the OS. Remove the tftp.exe utility unless you need TFTP-client functionality on your SQL Server.

You can eliminate access to xp_cmdshell by opening Query Analyzer and executing the following command:

DROP PROCEDURE xp_cmdshell

However, this command doesn't remove the stored procedure. The only way to remove the stored procedure is to unregister the xplog70.dll file. But because legitimate stored procedures depend on this .dll file, this step is impractical. And even if you remove the command, an attacker who has sysadmin-level access can reenable xp_cmdshell. If you do disable this stored procedure, you can use Profiler to determine whether anyone has reenabled it—a sure sign of intrusion.

Due Diligence

Using Windows integrated authentication is a more secure choice than using mixed authentication. But when you have to use mixed authentication, diligently practicing the techniques in this article will help you reduce the risk of a security breach. Although adhering to these best practices won't guarantee the security of your SQL Servers, it will lower the level of risk associated with using the sa account and help guard against the many tools designed to attack it.