Sql server Technical Article

Authentication

SQL Server Authentication provides authentication for non-Windows-based clients or for applications using a simple connection string containing user ids and passwords. While this logon is easy to use and popular with application developers, it is not as secure as Windows authentication and is not the recommended authentication mechanism

SQL Server 2005 improves on the SQL Server Authentication option. First, it supports encryption of the channel by default through the use of SQL-generated certificates. Administrators do not have to acquire and install a valid SSL certificate to make sure that the channel over which the SQL credentials flow is secure. With SQL Server 2005 automatically generating these certificates, the channel is encrypted by default when transmitting login packets, and only if the client is at the SQL Server 2005 level as well.

Note  The native certificate generated by SQL Server protects against passive man-in-the-middle attacks where the attacker is sniffing the network. To further secure your systems against active man-in-the-middle attacks, it is recommended that you deploy and use certificates that are trusted by the clients as well.

SQL Server Authentication is further enhanced by default in SQL Server 2005 because the database engine now supports Windows Group Policy with regards to password complexity, password expiration, and account lockout on SQL logins when used in combination with a Windows 2003 server. This means that you can enforce the same password policy as your Windows accounts.

Password policy enforcement

Table 3   Windows Server 2003 password policy components

If you are not running Windows Server 2003, SQL Server still enforces password strength by using simple checks, preventing passwords that are:

• 
  Null or empty
  
• 
  The same as the name of computer or login
  
• 
  Any of "password", "admin", "administrator", "sa", "sysadmin"
  

The password policy is always checked by default, but you can suspend enforcement for individual logins with either the CREATE LOGIN or ALTER LOGIN statements as in the following code:

CREATE LOGIN bob WITH PASSWORD = 'S%V7Vlv3c9Es8',

CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

CHECK_EXPIRATION uses the minimum and maximum password age part of the Windows Server 2003 policy, and CHECK_POLICY uses the other policy settings.

Administrative settings also allow turning on and off password policy checks, turning on and off password expiration checks, and forcing a password change the first time a user logs on. The MUST_CHANGE option in CREATE LOGIN forces the user to change the password the next time they log on. On the client side, it allows a password change at logon. All of the new client-side data access technologies will support this, including OLE DB and ADO.NET, as well as client tools such as Management Studio.

If the user unsuccessfully attempts to log on too many times and exceeds the attempts allowed in the password policy, the account may be locked out, based on the settings in the Windows policy. An administrator can unlock the account with the ALTER LOGIN statement:

ALTER LOGIN alice WITH PASSWORD = '3x1Tq#PO^YIAz' UNLOCK

Endpoint authentication

Using SQL Server 2005 as a Web service host requires two general steps, each with plenty of possible variations: defining stored procedures and user-defined functions that provide the Web service methods, and defining an HTTP endpoint that receives method calls via HTTP and routes them to the appropriate procedure. This paper focuses on the security issues involved. For details on configuring and using HTTP endpoints, see SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms130214.aspx).

Because XML Web services in SQL Server uses HTTP and, by default, port 80, most firewalls are configured to allow the traffic to pass. But an unprotected endpoint is a potential vector for attacks and must be secured, so SQL Server is designed with strong authentication and authorization. No endpoints are installed by default with SQL Server and you have to have a high level of permissions to create, alter, and enable HTTP endpoints.

SQL Server 2005 provides five different authentication types, similar to those used by IIS for Web site authentication.

• 
  Basic authentication
  Basic authentication is defined as part of the HTTP 1.1 protocol, which transmits the login credentials in clear text that is base-64 encoded. The credential must map to a Windows login, which is then used to authorize access to database resources. If you use Basic authentication, you cannot set the PORTS argument to CLEAR but must instead set it to SSL and use a digital certificate with SSL to encrypt the communication with the client software.
  
• 
  Digest authentication
  Digest authentication is also specified as part of the HTTP 1.1 protocol. It hashes the credentials with MD5 before sending to the server so that they are not sent across the wire, even in encrypted form. The credentials must map to a valid Windows domain account; you cannot use local user accounts.
  
• 
  NTLM authentication
  NTLM uses the challenge-response protocol originally introduced in Microsoft Windows NT® and supported in all client and server versions of Windows since. It provides secure authentication when both client and server are Windows systems, and requires a valid domain account.
  
• 
  Kerberos authentication
  Kerberos authentication is available with Windows 2000 and later, based on an industry-standard protocol available on many operation systems. It allows for mutual authentication in which both the client and server are reasonably assured of the other’s identity and provides a highly secure form of authentication. To use Kerberos on Windows Server 2003, you must register the Kerberos Service Principal Name (SPN) with Http.sys by using the SetSPN.exe utility that is part of the Windows Support Tools.
  
• 
  Integrated authentication
  Integrated authentication provides the best of NTLM and Kerberos authentication. The server uses whichever of the two authentication types the client requests, allowing the most secure authentication the client supports while making the service available to older versions of Windows. You can configure Http.sys in Windows 2003 to negotiate with the client which protocol to use.
  

CREATE ENDPOINT myEndpoint

SQL Server 2005 supports endpoints that listen both to HTTP as well as a user-defined port on TCP. You can also format requests using a variety of formats: SOAP, Transact-SQL, a format specific to Service Broker, and another used for database mirroring. When using SOAP you can take advantage of WS-Security headers to authenticate SQL Server logins.

Microsoft has implemented Web service endpoint authentication to support a wide variety of protocols and specifications, of which this paper has touched on just a few. You’ll need to explicitly enable your authentication option and ensure that clients are able to provide the type of credentials required. Once the client is authenticated, you can authorize the resources that the login is authorized to access, described in the next section.