Authentication is the process of validating that someone is who they say they are. In other words, authentication is the process of verifying someone's identity based on things like login name and password, security token, or certificate.
Like previous releases, SQL Server 2005 continues to support two authentication modes: SQL Authentication and Windows Authentication. The latter is still the recommended way of connecting to SQL Server. The authentication model in SQL Server is enhanced to strengthen the SQL Authentication mode. Before we look at this, let's first look at two new security features related to how a connection is made to a SQL Server instance: endpoint-based authentication and the role of the SQL Browser service.
SQL Server 2000 allows clients to connect over network protocols such as TCP/IP and named pipes. The only application-level protocol supported by SQL Server 2000 is the Tabular Data Stream (TDS), which is used to send SQL statements from the client to the server and result sets from the server to the client. The TDS packets are encapsulated in the packets built for the underlying network protocol, such as TCP/IP packets. SQL Server 2005 extends this framework by introducing the concept of endpoints.
An endpoint can be thought of as an entry point into a SQL Server instance. You can create multiple endpoints per instance by using the CREATE ENDPOINT transact-SQL (T-SQL) statement. When you create an endpoint, you have to specify what transport protocol it will be accessed over and what kind of payload it will accept. For instance, you can create an endpoint to work with TDS payload over the TCP/IP network protocol. In other words, an endpoint binds a transport protocol to payload.
The benefit of this approach is that you can manage permissions on a per-endpoint basis. Endpoint security comes in the form of permissions such as CONNECT that can be granted, revoked, or denied to database users. For instance, using endpoints, you can implement a scenario where certain users can access SQL Server over named pipes or shared memory, but not over HTTP, a transport protocol that is now supported by SQL Server 2005. SQL Server 2005 introduces support for HTTP, which can work with XML/SOAP payload and can be used to implement XML web services or Service Oriented Architecture (SOA) in SQL Server 2005. This is discussed in more detail in Chapter 10, "XML and Web Services Support in SQL Server 2005." In addition, an endpoint can also be created to implement database mirroring and work with Service Broker. Database Mirroring is discussed in Chapter 8, "Reliability and High Availability in the Database Engine," and Service Broker is discussed in Chapter 14, "SQL Server 2005 Service Broker."
The other benefit with the endpoint approach is that you can start, stop, or disable an endpoint to have SQL Server listen or not listen on that endpoint. When you create an endpoint and do not specify the STATE = STARTED clause, for security reasons the endpoint is stopped by default.
When SQL Server 2005 is installed, it creates an endpoint for every enabled transport protocol, such as named pipes, TCP/IP, shared memory, and so on. Each authenticated login is assigned the CONNECT endpoint permission on these endpoints, which can be denied on a per-endpoint basis. For security reasons, SQL Server 2005 does not create any HTTP endpoints out of the box. If you need to enable XML web services in SQL Server 2005, you can create HTTP endpoints by using the CREATE ENDPOINT statement.
With the endpoint-based authentication model, when a user connects to a SQL Server instance, in addition to verifying the login credentials, SQL Server also verifies that the login is authorized to connect via the endpoint.
SQL Browser Service and the New Security Model
SQL Server 2000 enables up to 16 instances to be installed on a machine. SQL Server 2005 raises this number to 50. When a client tries to connect to a named instance or an instance listening on a non-default static or dynamic port, the client has no knowledge of where to connect. The instance resolution protocol in previous SQL Server releases was an answer to this problem. With this approach, a client could connect to the SQL Server machine over UDP port 1434 and enquire about all the instances listening on that machine. This open port 1434 was exploited by hackers, and this led to the W32.Slammer attack described at the beginning of this chapter.
This functionality of translating an instance name passed in from the client to the appropriate TCP/IP port or pipe is now separated into a Windows service called SQL Browser. In SQL Server 2005 you can stop or disable the SQL Browser service to turn off the UDP 1434 traffic instead of relying on the firewall.
When the SQL Browser service is running and clients query over UDP 1434, the SQL Browser service returns a list of SQL Server machines (SQL Server 7.0 and above, clustered and non-clustered), Analysis Server machines, Report Server machines, and SQL Mobile databases, along with version numbers for all the servers.
It is recommended that the SQL Browser service be run in the security context of a low-privileged account because all it does is accept unauthenticated requests and map instances to a TCP/IP port or a named pipe. Running this service under a low-privileged account reduces the exposure to malicious attacks.
When the client knows about the TCP/IP port or the named pipe to connect to, the next step is login authentication. For Windows logins, the token from the domain controller is presented to the SQL Server machine, and for SQL logins, the login name and the password hash are presented to the SQL Server machine. After establishing the login credentials, SQL Server 2005 ensures that the login has access over the endpoint through which it is trying to connect. When this is verified, SQL Server switches to the database to obtain the user context, which is then used for database permissions for the most part.
In summary, SQL Server 2005 separates the functionality of listening over UDP 1434 and responding to anonymous clients with instance information or mapping instance names into TCP/IP port or named pipe into a separate service called SQL Browser, which can be turned on or off to control the UDP 1434 traffic. And the notion of endpoints allows you to control access to the server based on transport and payload, as well as the ability to start, stop, or disable individual endpoints to better control access to the server.
Let's now look at how SQL Server strengthens the SQL Authentication mode.
Strengthening SQL Authentication
Windows Authentication is still the recommended approach for connecting to SQL Server because it can leverage the domain password policy and other security infrastructure and is based on NTLM and Kerberos protocols, which are much stronger than the SQL Authentication. However, Microsoft recognizes that customers may need to continue to use SQL Server authentication. Hence, SQL Server 2005 strengthens the SQL Authentication to make it more secure. The following are some of the limitations of SQL Authentication in SQL Server 2000:
SQL Server 2005 fixes these limitations. SQL Server 2005 contains built-in support for securing the channel for SQL Authentication. It first checks whether an SSL certificate is installed for the server communication, and if a certificate exists, SQL Server 2005 uses it to secure the communication. If not, SQL Server 2005 automatically generates a 512-bit certificate that it uses to encrypt the credentials. Note that you don't have to make any changes in your SQL Server 2005 applications to use this feature, and SQL Server 2005 continues to provide support for old-style authentication for down-level clients.
SQL Server 2005 introduces a new DDL statement, ALTER LOGIN, that can be used to enable or disable a login.
Now let's look at the new password policy feature in SQL Server 2005 that fixes the third limitation mentioned earlier.
Password Policy Support
Windows network administrators almost always use the following security features to secure the domain logins and thereby in some way secure their networks:
All this functionality is now available for SQL Authenticated logins. SQL Server 2005 makes use of the Windows 2003 Server API to enforce the Windows password policies of the computer on which SQL Server is running on the SQL Authenticated logins. Because this API is not available on Windows 2000 Server, SQL Server 2005 only supports native password complexity. The native password complexity feature disallows blank or null password and certain words, such as password, admin, sa, administrator, sysadmin, the name of machine, or the same string as the login name.
The sp_addlogin system stored procedure for creating new SQL Server logins is being deprecated in SQL Server 2005. Using the new DDL statement CREATE LOGIN is the recommended approach for creating new logins. For each new SQL Authenticated login that you create, SQL Server by default enforces the password policy.
There are three important options with the CREATE LOGIN DDL statement for SQL authenticated logins:
The is_policy_checked and is_expiration_checked fields in the sys.sql_logins catalog view can be used to determine whether the CHECK_POLICY and CHECK_EXPIRATION options are ON or OFF. This catalog view derives from the sys.server_principals view, which contains fields such as is_disabled, which can be used to figure out whether the account is enabled or disabled.
In addition, you can associate an existing credential with a SQL Authenticated login. SQL Server 2005 uses such credentials when connecting outside the server. The credential can be created by using the CREATE CREDENTIAL DDL statement. One credential can be associated with multiple logins, but a login can have only one credential associated with it.
When specifying the password for SQL Authenticated login, you can use the HASHED option to tell SQL Server that the password string is already hashed and SQL Server should store the string as it is in the database, without hashing it again.
The following is an example of using the CREATE LOGIN and ALTER LOGIN statements to create and alter SQL Authenticated logins:
USE master; GO CREATE LOGIN Bob WITH PASSWORD = 'Bob'; GO CREATE LOGIN Bob WITH PASSWORD = 'Admin'; GO CREATE LOGIN Bob WITH PASSWORD = 'Admin', CHECK_POLICY = OFF, CHECK_EXPIRATION = ON; GO CREATE LOGIN Bob WITH PASSWORD = 'Admin', CHECK_POLICY = OFF; GO CREATE LOGIN Rob WITH PASSWORD = 'WjkKWjkF', CHECK_EXPIRATION = OFF; GO ALTER LOGIN Rob DISABLE; GO SELECT * FROM sys.server_principals; SELECT * FROM sys.sql_logins; GO DROP LOGIN Bob; DROP LOGIN Rob;
The first three CREATE LOGIN statements in this script fail because the first two statements do not specify the strong password, and the third statement tries to keep password expiration on without keeping policy check on, which is not allowed.
The fourth CREATE LOGIN statement succeeds even though it is using a weak password string because it turns off the CHECK_POLICY option. The next login creation succeeds, too, because it uses a strong password and requests to turn off password expiration, while keeping policy check on, which is allowed. The ALTER LOGIN statement illustrates disabling a login.
The LoginProperty built-in function can be used to determine the state of an account with regard to the password policy:
CREATE LOGIN Robert WITH PASSWORD = 'WjKkWjKf'; SELECT LoginProperty('Robert', 'IsLocked'); SELECT LoginProperty('Robert', 'IsMustChange'); SELECT LoginProperty('Robert', 'IsExpired'); SELECT LoginProperty('Robert', 'LastSetTime'); DROP LOGIN Robert;
This concludes the discussion on authentication-related security features in SQL Server 2005. Let's now look at authorization-related enhancements introduced in SQL Server 2005.