Ana səhifə

Sql server Technical Article

Yüklə 0.51 Mb.
ölçüsü0.51 Mb.
1   2   3   4   5


SQL Server 2000 was developed at a time when data and servers required protection but did not have to withstand the relentless onslaught of attacks seen on the Internet today. The basic authentication question remains the same—who are you and how can you prove it?—but SQL Server 2005 provides much more robust authentication features that provide better support at the security outskirts of the server for letting the good guys in and keeping the bad guys out.

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

With SQL Server 2005, password policy enforcement is built into the server. Using the NetValidatePasswordPolicy() API, which is part of the NetAPI32 library on Windows Server 2003, SQL Server validates a password during authentication and during password set and reset in accordance with Windows policies for password strength, expiration, and account lockout. Table 3 lists the settings that comprise the policy.

Table 3   Windows Server 2003 password policy components




Password Policy

Enforce password history

Prevents users from reusing old passwords, such as alternating between two passwords.

Minimum password length

Password must meet complexity requirements

See text below.

Store passwords using reversible encryption

Allows retrieving the password from Windows. Should never be enabled, unless application requirements outweigh the need for secure passwords. (This policy doesn’t apply to SQL Server.)

Password Expiration

Maximum password age

Minimum password age

Account Lockout Policy

Account lockout duration

Time in minutes the account is locked out. Enabled when lockout threshold is > 0.

Account lockout threshold

Maximum number of unsuccessful login attempts.

Reset account lockout counter after

Time in minutes after which the counter of unsuccessful attempts is reset. Enabled when lockout threshold is > 0.

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 same complexity standard is applied to all passwords you create and use in SQL Server, including passwords for the sa login, application roles, database master keys for encryption, and symmetric encryption keys.

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:



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:


Endpoint authentication

SQL Server 2005 supports both the traditional, binary Tabulate Data Stream for client access to data as well as native XML Web service access using HTTP. The primary benefit of allowing access via HTTP is that any client software and development tools that understand Web service protocols can access data stored in SQL Server. This means SQL Server 2005 can provide standalone Web service methods as well as be a complete endpoint in a Service Oriented Architecture (SOA).

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 (

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.

The authentication method used for an endpoint is set with the AUTHENTICATION attribute of the CREATE or ALTER ENDPOINT statement. For example, the following code creates an endpoint that uses Kerberos for authentication:


AS HTTP (PATH = '/MyHttpEndpoint',
SITE = 'MySqlServer')

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.

1   2   3   4   5

Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur © 2016
rəhbərliyinə müraciət