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.

Endpoint-Based Authentication

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:

  • When clients connect to a server by using SQL Authentication, the channel is not secured, and anyone can figure out the login and password used to connect to SQL Server. The only way to secure the client/server channel is to install a certificate from the Certificate Authority on the server and client machines and then use Super Socket Net-Library (Dbnetlib.dll and Ssnetlib.dll) to encrypt the communication. In other words, the channel is not secure by default; extra administrative steps have to be performed to secure it.

  • It is not possible to enable or disable logins.

  • SQL Server 2000 does not support any password policy or account lockout or expiration rules.

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:

  • Enforce password policies to ensure that domain users make use of strong passwords.

  • Enforce account lockouts so that if someone tries the brute-force approach to breaking in using a particular login, that account gets locked out or disabled after a certain number of failed tries.

  • Enforce password expiration so that domain users change their passwords periodically.

  • Force users to change their passwords on first login.

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:

  • CHECK_POLICY ON by default, this option enables the Windows password and lockout policy on SQL logins. For security reasons, it is not recommended to set this option to OFF. Turning off the CHECK_POLICY option puts you back to SQL Server 2000 behavior, where SQL Server does not enforce password complexity or lockout rules. The account lockout mechanism ensures that if there are X number of bad password attempts within Y window of time, the account is locked out for a Z period of time, where X, Y, and Z are the parameters controlled by the policy. It is important to note that the CHECK_POLICY option controls both password complexity and account lockout. Just password complexity is not sufficient to protect against brute-force attack.

  • CHECK_EXPIRATION ON by default, this option specifies whether the password expiration policy should be enforced on the SQL login being created. If you set the CHECK_POLICY option to OFF, the CHECK_EXPIRATION option is also turned off. In other words, you can enable CHECK_POLICY without enabling CHECK_EXPIRATION, but you cannot enable CHECK_EXPIRATION without enabling the CHECK_POLICY option. CHECK_EXPIRATION and CHECK_POLICY are enforced only on Windows 2003 Server and above. On other operating systems, SQL Server 2005 supports only native password complexity.

  • MUST_CHANGE This option is only supported on Windows Server 2003, and when it is specified, SQL Server prompts the user for a new password when the login is used to connect to SQL Server. If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the CREATE LOGIN statement will fail.

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:


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.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150 © 2008-2017.
If you may any questions please contact us: