SQL Server 2005 Part 1 - Security (Authentication)

January 24, 2005

In this installment of our series covering new and improved functionality of SQL Server 2005 Beta 2, we will focus on the topic of security, which has been becoming increasingly prominent among the issues on every database and system administrator's agenda. A new approach to software development started with the Trustworthy Computing initiative launched in early 2002, necessitated by the growing number of exploits directed at the Microsoft operating system and applications, resulted in a "secure by default" product with highly customizable security features further increasing the degree of protection. We will start with the features related to authentication (the process of identifying logins connecting to the SQL Server and users accessing databases), and continue with authorization (determining the level of permissions granted once the initial connection is established) and encryption in the future articles. In particular, we will cover here, password policy implementation and management as well as endpoint-based authentication (in the context of native support for HTTTP SOAP requests).

It is worth pointing out that the new version of SQL Server has been designed with the "secure by default" principle in mind, resulting in a system with optimum, from the security standpoint, settings. The typical setup avoids installing or activating non-essential components and features which can expose the server and its data to potential attacks. This applies, for example, to SQL Server Agent, Full-Text Search, and Data Transformation Services (all set to manual startup), Analysis, Reporting, and Notification Services, SQL Browser, Service Broker Network Connectivity, Database Mirroring, SQLMail, or SQL Debugging.

Just like its predecessors, SQL Server 2005 still supports Windows and Mixed authentication modes. In the Windows Authentication Mode, access is granted based on a security token assigned during successful domain (or local server) logon by a Windows account, which subsequently requests access to SQL Server resources. In this case, such accounts must belong to the same Windows environment as the computer hosting SQL Server (this environment can consist of any combination of trusted domains or forests). In the Active Directory domain environment, an additional level of protection is provided by Kerberos protocol, which governs the behavior of the Windows authentication mechanism (on Windows 2003 Server based-clusters, Kerberos authentication can be used against SQL Server 2005 virtual servers). The Mixed Authentication Mode also allows SQL Server Authentication (in addition to Windows Authentication), which relies on the verification of credentials stored and maintained by the SQL Server. Due to increased security and no need for a separate set of accounts (since existing Windows users and groups are leveraged), Windows Authentication is the preferred option; however, security of SQL Server based logins has improved through its encryption with SQL generated certificates, as long as communication involves the new MDAC client software, based on .NET provider.

New in SQL Server 2005 is the ability to manage SQL Server account password and lockout properties (in addition to Windows accounts used with Windows Authentication) with local and domain-based group policies (remember though that this functionality is available only on Windows 2003 Server systems). This allows you to enforce such restrictions on password complexity, password expiration, and account lockout. Password complexity is defined by a number of the following characteristics:

• length of the password must be at least 6 characters (in general, SQL Server passwords can have between 1 and 128 characters),
• password must contain at least three out of four types of characters such as uppercase letters, lowercase letters, numbers, and non-alphanumeric characters,
• password can not match any of the values: "Admin", "Administrator", "Password", "sa", "sysadmin", name of the compute hosting SQL Server installation, and all or part (three or more consecutive alpha-numeric characters delimited on both ends by space, tab, comma, period, underscore, dash, or hash sign) of the name of currently logged on Windows account.

Note that regardless of authentication mode and policy enforcement, SQL Server 2005 Setup Wizard does not permit non-blank passwords for sa account during the installation. Password expiration is determined by the value of "Maximum password age" group policy setting, while account lockout behavior depends on values assigned to "Account lockout duration", "Account lockout threshold", and "Reset account lockout counter after" entries.

You can use CHECK_EXPIRATION and CHECK_POLICY clauses when creating new logins with the CREATE LOGIN T-SQL statement to enable or disable compliance with respective group policy settings (by setting them to ON or OFF, respectively). CHECK_EXPIRATION controls password expiration, while CHECK_POLICY determines both the state of password complexity and account lockout settings (which limits to some extent the flexibility in configuring these options - further restricted by the fact that a combination of CHECK_POLICY set to OFF and CHECK_EXPIRATION set to ON is not supported). Including the MUST_CHANGE clause forces the user to change the password when logging in for the first time. For example, the following statement creates a new login with password complexity, password expiration, and account lockout enabled, and assigns a new password that will need to be changed at the first login.

CREATE LOGIN drevil
WITH	
	PASSWORD = 'Ch4ngeMe' MUST_CHANGE, 
	CHECK_EXPIRATION = ON, CHECK_POLICY = ON

sys.sql_logins catalog view produces output indicating whether password policy and password expiration have been enforced for existing SQL logins (within is_policy_checked and is_expiration_checked fields). The same information is available for individual accounts through the graphical interface of SQL Server Management Studio. ALTER LOGIN T-SQL statement supports the UNLOCK clause, intended for unlocking SQL Server logins that have been locked by mistyping the password more times than the value assigned via group policy to account lockout threshold setting.

Endpoint-based authentication is used to provide secure communication in scenarios where SQL Server 2005, running on a Windows 2003 Server, functions natively as a Web Service, listening and responding to HTTP SOAP requests. (Windows 2003 Server implements the HTTP.SYS listener process outside of World Wide Web service, which eliminates the need for Internet Information Server functioning as an intermediary). This makes it possible to execute remotely stored procedures, scalar-valued user-defined functions, and T-SQL batches. However, endpoints, which are, in essence, points of entry to a SQL Server 2005 instance, are more versatile, allowing combining different transport (such as Named Pipes, Shared Memory, TCP/IP, or HTTP) and payload protocols (such as SOAP, Service Broker, Tabular Data Stream, or Database Mirroring), listening ports, authentication modes, and permissions (although the choice of a transport protocol has implications on other characteristics, such as, default permissions). Both SQL Server login authentication modes are supported - the preferred one is designated by the LOGIN_TYPE option (which can take on WINDOWS or MIXED values, corresponding, of course, to Windows and Mixed authentication) when creating or modifying HTTP endpoints using CREATE ENDPOINT and ALTER ENDPOINT statements. WINDOWS is the default and recommended choice, since, besides added security, it is also easier to configure. Otherwise, with MIXED LOGIN_TYPE, SQL Server must be configured with Mixed Authentication, endpoints are required to operate over a Secure Socket Layer channel (which is set by assigning an SSL value to the PORTS option of the endpoint), and login credentials need to be specified in the Web Services Security headers preceding the SOAP requests, formed by a client application. For more details on this subject, refer to the "SQL Authentication over SOAP" topic in the SQL Server Books Online.

In the case of SOAP-based communication, CREATE ENDPOINT and ALTER ENDPOINT statements also support the AUTHENTICATION clause, which controls the transport protocol (HTTP) authentication mechanism and which can be assigned one of three values:

• INTEGRATED - applies Windows-based Kerberos or NTLM authentication protocol when establishing HTTP communication between the client and server. The choice of the authentication protocol depends on the capabilities of the client operating system. Older ones (such as Windows 9x and Windows NT 4.0 are limited to NTLM only), while the newer ones will attempt Kerberos first (responding to negotiate challenge from the server). However, in order for mutual Kerberos authentication to work, the SQL Server service account must be associated with a Service Principal Name (SPN). This can be accomplished by running sp_register_kerberos_spn_for_http stored procedure, which creates two SPNs, for NetBIOS and fully qualified DNS names, respectively. If the SQL Server runs in the security context of the Local System account, the SPN is associated with the computer account of the server hosting the SQL Server installation.
• DIGEST - based on MD5 one-way hashing algorithm, which is applied to user's Windows credentials on the client side and compared with the result of the same algorithm being applied on the server side.
• BASIC - compares BASE64-encoded Windows credentials on the client and server side. Note that encoding is easily reversible and therefore must be used in combination with Secure Sockets Layer encryption of HTTP connection, enforced by PORTS=(SSL) option on the endpoints.

In the next article, we will look into authorization improvements in SQL Server 2005 Beta 2.

» See All Articles by Columnist Marcin Policht