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:
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:
In the next article, we will look into authorization improvements in SQL Server 2005 Beta 2.