SQL Server Magazine

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.