Managing SQL Server 2005 Security

In this tutorial you will learn about Managing SQL Server 2005 Security. Microsoft has consciously invested in the creation of a precise and flexible security model of the database platform with security features that include surface area reduction, data encryption, native encryption, authentication and granular permissions and user and schema separations. This is part of the Trustworthy Computing initiative.

SQL Server 2005 Security Overview

The Trustworthy Computing initiative provides a framework for secure computing. Confidentiality, integrity and availability of data and systems are the cornerstones of the software life cycle. The Trustworthy computing initiative insists that the application should be secure by design. Therefore, Microsoft have built in multiple security audits and a threat analysis has been performed to evaluate every issue and additional design and testing work was carried out to neutralize potential risks. The second feature of the initiative is that the software has to be secure by default. Therefore, many of the features are disabled by default and users have to configure them if required. The third aspect that Microsoft focused on was that the SQL Server has to be secure in deployment. Proper security credentials and permissions have to be set. The content and the deployment tools provide the users with the information required for the decisions required to be taken during deployment. Security updates are also easy to find and install. Trustworthy computing initiative also includes use of views to access system tables, adaptable enforcement of password policies and improved database encryption capabilities. These features are further supported by Microsoft’s communication strategy that enables users of SQL Server to be intimated by Microsoft of any security threats that they face and the action they need to take and what tools to use to counter such threats.

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

The enforcement of the password policy for the existing logins can be verified by the DBA from the catalog view outputs. This can be verified in the graphical user interface of SQL Server Management Studio.

The endpoints in SQL Server 2005 are versatile with different transport and payload protocols, listening ports, authentication modes and permissions. When creating or modifying HTTP endpoints using the CREATE ENDPOINT and ALTER ENDPOINT statements the preferred login type is designated by the LOGIN_TYPE option(which can be WINDOWS or MIXED values). While WINDOWS is default, the MIXED mode will have to be configured to operate over a Secure Socket Layer channel. The login credentials must be specified in the Web Services Security headers preceding the SOAP requests of the client application.

The HTTP authentication mechanism can be assigned an Integrated, Digest or Basic value if the communication is SOAP based. The INTEGRATED mechanism applies windows based Kerberos or NTLM authentication protocol when establishing the HTTP communication between the client and server. The SQL Server account must be associated with Service Principal Name for the mutual Kerberos authentication to work. DIGEST is a hashing algorithm applied to user’s windows credentials on the client side. This is compared with the result of the same algorithm being applied on the server side.

BASIC compares the Windows BASE 64 Credentials on the client and server side.

[catlist id=178].