Logging in to SQL Server is so routine, it’s usually taken for granted; however, it is of critical importance. Not only is authentication the first line of defense in preventing unauthorized access, it is one of the first items auditors investigate when assessing regulatory compliance. Among the many regulations that directly or indirectly require organizations to have effective authentication mechanisms in place are:

1. Sarbanes-Oxley Act
2. Gramm-Leach-Bliley Act (GLBA) of 1999
3. Health Insurance Portability and Accountability Act (HIPAA) of 1996
4. FDA Title 21 CFR Part 11
5. Basel II Frameworks for financial institutions in G10 nations
6. California Senate Bill 1386

Failure to comply with regulations can result in very severe financial consequences for a company. Not only must a company have effective controls to prevent misuse of computer systems, it is necessary to have clear proof of compliance. SQL Server 2005 has new authentication features that make it easier to be in compliance.

Windows authentication, when coupled with Windows Account Policies, provides highly effective authentication controls. It remains the preferred method of authenticating with SQL Server. But what about when Windows authentication is not an option? SQL Server 2005 offers some secure alternatives.

Account Policies

For those times when SQL Server authentication is required, SQL Server 2005 can enforce Windows Account Policies for the SQL Server logins (this new feature requires Windows 2003 or a later version). Since DBAs and database developers do not usually perform Windows account administration, a brief review of Windows Account Policies is appropriate to establish a beginning context. The Password Policy settings are used to establish requirements such as password length and complexity.

Account Lockout Policy settings provide protection against brute force and other repeated unauthorized login attempts.

The SQL Server Management Studio replaces both the Enterprise Manager and the Query Analyzer, and it is the tool used to create database logins. When creating a SQL Server login in SQL Server 2005, the default is to apply Account Policies. As the following screen capture shows, the defaults can be overridden by unchecking the Enforce password policy and Enforce password expiration checkboxes, which set the CHECK_POLICY and CHECK_EXPIRATION properties, respectively.

There are three additional Login Properties pages (Server Roles, Database Access, Permissions) for granting access privileges. They will be discussed in a separate article on SQL Server 2005 authentication, but a brief mention of the Database Access page is needed.

You should be aware that when this page is used to authorize access to a database, a database user is automatically created.

SQL Server 2005 is designed to make administrative tasks much easier. If you’ve administered an earlier version of SQL Server, you’ve probably wished that tasks performed in the Enterprise Manager could be easily converted to SQL scripts. Now, this can be done painlessly in the SQL Server Management Studio. If you take a close look at the Login Properties dialog box, you’ll see a Script button. Use this to script all of the settings.

The scripting output is shown below:

USE [master]
GO
CREATE LOGIN [DBAzine] WITH PASSWORD=N'c0mPLex'
, DEFAULT_DATABASE=[AdventureWorks]
, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'DBAzine'
, @rolename = N'processadmin'
GO
USE [master]
GO
USE [AdventureWorks]
GO
CREATE USER [DBAzine] FOR LOGIN [DBAzine]
GO
USE [master]
GO
USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_datareader', N'DBAzine'
GO
USE [master]
GO
use [master]
GO
GRANT ADMINISTER BULK OPERATIONS TO [DBAzine]
GO
USE [master]
GO

When both CHECK_EXPIRATION and CHECK_POLICY are ON, SQL Server logins can also take advantage of the MUST_CHANGE feature.

CREATE LOGIN DBAzine] WITH PASSWORD=N'c0mPLex' MUST_CHANGE
, DEFAULT_DATABASE=[AdventureWorks]
, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
, MUST_CHANGE

ALTER LOGIN [DBAzine] WITH PASSWORD=N'Als0c0mPLex' MUST_CHANGE

MUST_CHANGE is a great feature for keeping the auditors happy. Password resets are inevitable. If the DBA resets a password for a user, it causes a security problem that auditors hate: both the DBA and the user know the password. Using MUST_CHANGE with either CREATE LOGIN or ALTER LOGIN forces the user to reset the password during the login process. If password history is enforced, MUST_CHANGE ensures that only the user knows the new password.

From an auditing perspective, MUST_CHANGE is an important feature because it is unacceptable for the person administering a password reset to know the final password that the user actually uses.

Of course, no discussion of resetting passwords would be complete without discussing the companion administrative activity of unlocking a locked account.

Unlocking a locked SQL Server login is accomplished by using the ENABLE keyword:

ALTER LOGIN dbazine ENABLE

An auditor won’t consider a system safe from brute force password attacks unless password lockout is enforced. Now that you have strong passwords, forced password change, and account lockout for SQL Server logins, what more could an auditor want?

SSL

Enforcing password policies on a SQL Server login is all for naught if the password is intercepted during transport. Although SQL Server 2000 supports SSL encryption during the login process, it is off by default. The DBA must undertake specific actions to enable it. With SQL Server 2005, the authentication channel is automatically encrypted using SSL. SQL Server 2005 generates and loads an SSL certificate without any administrative action undertaken by the DBA.

Endpoints

Endpoints are points of entry into SQL Server 2005. They bind transport protocols (named pipes, shared memory, HTTP, TCP, VIA) to payloads (SOAP, TDS, Service Broker, Database Mirroring). SQL Server 2005 HTTP endpoints provide native web service support without using IIS. Unlike all other endpoints, HTTP endpoints are off by default and must be explicitly created. Additionally, permissions on HTTP endpoints must be explicitly created.

Endpoints can be used to prevent an otherwise valid login from connecting over a particular protocol. Best practices not withstanding, sometimes user access is restricted by the line of business application the user should be using. This bothers auditors greatly. The problem is that a user with a valid SQL Server login could, for example, gain access to a SQL Server 2000 database by using the Query Analyzer instead of the business application and thus be free of the restrictions imposed by the business application. In theory this shouldn’t be a problem because with tight authorization rules in place, the user should not be able to gain any additional privileges by using the Query Analyzer. But even if user authorization rules are tightly and properly enforced, a great deal of time would be spent proving that no additional privileges are gained by accessing the database using something other than the intended business application. Authorization occurs after authentication, so if authentication could be applied in a more restrictive manner, elevated authorization concerns could be mitigated. Endpoints provide restrictions to the authorization process itself.

Consider the following example. Suppose we create an HTTP endpoint for our DBAzine login to access the data by means of a Web service. Everything seems to be working just fine until the auditors arrive. They discover our login really should only access the database through the Web service, but it can also connect to the local database server via the SQL Server Management Studio. Furthermore, they discover the login gains unintended additional access privileges when directly connected. This problem can easily be remedied by denying connect access to the tsql local machine endpoint by using the following syntax:

deny connect on endpoint::[tsql local machine] to dbazine

When an attempt is made to connect to the local machine using the SQL Server Management Studio, the login fails:

Endpoints can provide a more granular control over the authentication process by restricting logins to work only over designated protocols.

Conclusion

As you have seen, SQL Server 2005 offers many new and highly useful authentication features. In some cases, upgrading to SQL Server 2005 offers the most cost effective way to achieve regulatory compliance of database applications.

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with regulatory compliance, SQL Server, C#, Oracle, and the .NET framework.