Overview of SQL Server 2005 for the Developer-Security

In this tutorial you will learn about Security Features in SQL Server 2005 – Authentication, Password Complexity, Password Expiration, Lockout Behavior, Endpoint-based Authentication, Authorization, Native Data Encryption, Code and Module Signing, SQL Server Agent Operations, Monitoring and Auditing

Authentication

SQL server has been designed to be “secure by default”. However, it has a number of customizable features that further increase the degree of protection—Authentication, Authorization and encryption. The settings have been optimized and avoid installing non essential components and features that can expose the server and its data to attacks.

It uses mixed and windows authentication modes though Windows Authentication is the preferred mode.

1. In Windows Authentication mode access is given based on a security token assigned during successful domain logon by a Windows account, which subsequently accesses the SQL server resources. The precondition is that both must belong to the same windows environment as the computer hosting SQL Server.

2. In Active Directory domain environment protection is provided by the Kerberos protocol which governs the behavior of the Windows Authentication mechanism.

3. The mixed authentication mode stipulates that the SQL Server authentication be done in addition to the Windows authentication. It relies on the verification of credentials stored and maintained by the SQL Server.

A striking new feature of SQL Server is the password and lockout management capabilities. The developer can now enforce restrictions on password complexity, expiration and account lockout.

Password Complexity

Password complexity can be defined as

1. The length of the password

2. The type of characters that can be used.

3. Password must not match system defined values such as “Admin” or “sysAdmin”.

4. Non blank passwords for “sa” account are not allowed during installation by the SQL Server 2005 Setup Wizard.

Password Expiration

Password Expiration is determined by the value of “Maximum password age” group policy setting. The group policy settings are defined using the CREATE_LOGIN T_SQL statements. Compliance can be enabled or disabled during the process. The CHECK_EXPIRATION and CHECK_POLICY clauses can be used to determine the expiration or policy. There is some flexibility in configuring these options but restrictions are imposed when CHECK_POLICY is set to OFF and CHECK_EXPIRATION is set to ON. The MUST_CHANGE clause will force the user change his password on first login. The code will look as under:

CREATE LOGIN xxx
WITH
………..PASSWORD = ‘Ch4ngeMe’ MUST_CHANGE,
………..CHECK_EXPIRATION = ON, CHECK_POLICY = ON

Information about password policy and password expiration can be obtained from the sys.sql_logins. The password expiration and policy for individual accounts can be obtained from the SQL Server Management Studio interface.

Lockout Behavior

Lockout behavior is determined by the values assigned to “Account lockout duration”, “Account lockout threshold” and “Reset account lockout counter after” settings. The ALTER LOGIN T_SQL statement supports the UNLOCK clauses to enable the locked password unlocking mechanism.

Endpoint-based Authentication

When SQL Server runs on Windows 2003 and functions natively as a web service endpoint authentication is used to provide secure communication. It listens and responds to HTTP SOAP requests. As a result remotely stored procedures, scalar valued user defined functions or T-SQL batches can be executed. It allows the combination of different payload protocols, listening ports, authentication modes and permissions. In this scenario both mixed and windows authentication modes are supported and specified by the LOGIN_TYPE option. It is often recommended that the Windows authentication mode be adopted as it has added security and is easy to configure. However, in the Mixed authentication mode endpoints are required to operate over a Secure Socket Layer channel. The login credentials have to be specified in the Web services security header preceding the SOAP request of a Client application. The CREATE ENDPOINT and ALTER ENDPOINT statements support the AUTHENTICATION clause and must be assigned a value. INTEGRATED is assigned if Windows based Kerberos or NTLM authentication protocol is used when establishing HTTP communication. DIGEST is used when MD5 based one-way hashing algorithm is to be used. BASIC is used when BASE64 encoded windows credentials on the client and server side needs to be compared.

Authorization

Authorization features determine the level of access rights assigned to a user in an application.

A significant improvement in SQL Server 2005 is the separation of user schema objects and database objects. A schema is a means of grouping objects so that the set of objects in a schema can be treated as a unit for owning permissions. For instance an execute permission can be assigned to a role on all stored procedures in a schema in a single T_SQL statement. This is a time saving mechanism when there is a large repository of stored procedures and users who have to be assigned roles. The schema can be defined as a container where individual database objects reside. The characteristics of the container must satisfy two tenets laid down by ANSI SQL-92 standards.

1. The schema forms a namespace and no two objects in the schema can have the same name. The four parts of the name (Servername, DatabaseName, SchemaName and ObjectName) must be unique.

2. All objects within a schema must have the same owner or must be owned by a database role or Windows group.

The advantages of the process, is

1. the separation of the user and schema,

2. modifiable context of module execution,

3. increased permission granularity and

4. improved catalog security.

Application specific schemas can be created and referred to consistently. This simplifies development and maintenance. It is easy to remove users as database objects don’t belong to users. A user can have a default schema without owning the schemas and database objects can be grouped together and still have a single owner. Multiple users can manage a schema without having to assign permissions individually.

The SQL Server 2005 database contains some default schemas. A few of them correspond to the pre-defined database users—dbo, guest, INFORMATION_SCHEMA, and sys. Others correspond to fixed database roles—db_owner, db_accesadmin, db_securityadmin, db_dlladmin etc. The list of available schemas can be viewed in the sys.schemas catalog view along with sys.database_principals catalog view. A fresh default schema can also be assigned for each database user. This can be done by calling the DEFAULT_SCHEMA option of the CREATE USER and ALTER USER T_SQL statements.

The module execution context defines how programmable modules and dependable objects are handled with reference to permissions. The mechanism requires the putting in place of an ownership chain for execution of objects that are called from other objects. The same permissions must exist in all objects. SQL Server 2005 provides the developer with the ability to alter the execution context with the EXECUTE AS clause that is available as part of the definition of stored procedures, functions, queues and triggers. The user account is the spring board for evaluating the user permissions and the caller account still needs permissions to call the object. The execution context information is maintained in the sys.sql_modules catalog view. The values that can be used are EXECUTE AS CALLER, EXECUTE AS USER, EXECUTE AS SELF and EXECUTE AS OWNER.

Granular permissions are still supported for server and database roles. Custom database roles, application roles and server logins and database users must be granted granular permissions on a much wider range of objects. Each secure object has been grouped into different scopes—database, server and schema. Server level permissions are stored in the sys.server_permissions catalog view and database related ones reside in sys.database_permissions catalog view. Permissions can be grant, deny or revoke. Permissions at higher levels are imply similar permissions at lower levels of the state. The permissions of SQL Server 2000 open up new possibilities in implementation in SQL Server 2005. Some of the permissions are CONTROL, ALTER, IMPERSONATE, and VEIW DEFINITION.

Direct access to system tables has been barred in SQL Server 2005. The tables are exposed through catalog views that have server and database level security. The permissions are set on the row level with minimum public role. This view is also limited to owners or users with specified permissions. However content can be reviewed using the ANSI INFORMAION_SCHEMA views. The graphical user interface of the SQL Server Management Studio, permits catalog views or they can be viewed using the sys.system_views via a T_SQL statement.