Managing SQL Server 2005 Security

Managing SQL Server 2005 Security

SQL Server 2005 supports Windows and mixed authentication modes and is closely integrated with it. In this mode access is granted based on a security token assigned during successful domain logon by a Windows account and the SQL Server is requested access subsequently. The precondition is that both must belong to the same windows environment. The Active Directory domain environment provides an additional level of protection of the Kerberos protocol. This protocol governs the behaviour of the Windows authentication mechanism. In the mixed mode SQL Server Authentication can also be used. The credentials are verified from the repository maintained by the SQL Server. The increased security has made redundant the need to maintain separate set of accounts. However, the SQL Server logins have been improved with encryption of SQL Generated Certificates for communications that involve MADC client software based on .NET provider.

A very significant enhancement to SQL Server 2005 is the ability to manage account passwords and lockout properties. This can be within the local and domain based group policies. The DBA can impose restrictions on password complexity, password expiration and account lockout. The following complexities can be imposed:

The length of the password can be set to be minimum 6 characters.
The password can contain uppercase characters, lowercase character, numbers and non-alphanumeric characters.
The password cannot be “Admin”, “Administrator”, “Password” etc
The Password expiration can be determined by the values of “Maximum password age” and the lockout behaviour can be determined by “Account lockout duration”, “Account lockout threshold”, “Reset account lockout counter after”. ALTER LOGIN T-SQL statement can be used to unlock locked password.

The DBA uses the CHECK_EXPIRATION and CHECK_POLICY clauses while creating new logins with the CREATE LOGIN T-SQL statement. While CHECK_EXPIRATION controls the password expiration, CHECK_POLICY controls account lockout settings. Both have to be set ON or OFF. Other combinations are not supported. The syntax would be as under:

CREATE LOGIN xxx
WITH
PASSWORD = 'CHANGEPASS' MUST_CHANGE,
CHECK_EXPIRATION = ON, CHECK_POLICY = ON