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
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 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 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
………..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 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.
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 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.
Native Data Encryption
Encryption is a mechanism for protecting data by obfuscating its content by a kind of masking or encoding. This process can be reversed by unmasking or decoding the content. This process can be symmetric or asymmetric depending on the key used to encrypt and decrypt. Of the two, asymmetric encryption is more complex and performance impact is better. Users can use both methods to doubly protect data.
There are two keys used in asymmetric encryption known as a public key and a private key. Access to the private one is available to the owner while the public key does not have this umbrella of protection. When data is encrypted using the public key, decryption can be done only with the private key. Users receiving a encrypted message can ensure that content has not be tampered with, by using the public key but they cannot access the content.
Closely associated with the process described above, is the Digital certificate. The digital certificate is a digitally signed piece of software that associates a public key with the identity of the private key owner. The signature of the certificate is created by an algorithm using the private key of the Certificate Authority and the corresponding public key is available with the recipient. A number of trusted certifying authorities have been included in the operating system known as Trusted Root Certification Authorities. This list can be added to or modified by the user. Each certificate has a number of properties that reflect the purpose and the subject’s public key and digital signature of the issuer. The application must verify the certificate validity by referencing revocation lists. SQL Server has built in encryption functions to perform such checks.
All required encryption components are available natively. Each SQL server instance has its Service Master Key and is created during the setup. This is encrypted with a Data Protection API provided by the Windows Operating system. The system data is secured using passwords at instance-level settings. Service Master keys secure each Database Master key and within each database, its master key is used to create certificates or asymmetric keys. These can be finally applied to protect data directly or further extend the encryption hierarchy. All certificate management tasks are handled internally by SQL Server 2005. Encryption and decryption are done by function pairs—EncryptByCert() and DecryptByCert() for instance.
The master key is created using the CREATE MASTER KEY ENCRYPTION BY PASSWORD= ‘password’. The administrator defined password is encrypted and stored in the sys.symmetric_keys catalog if CONTROL permissions to the database are available. Simultaneously the database master key is encrypted with the Service Master Key and stored separately in the sys.databases which facilitates automatic opening. The creation of certificates and asymmetric keys can now be done using the CREATE CERTIFICATE DDL T-SQL statement.
A certificate can be new or existing. It will be stored in a file as a signed executable or an assembly. It can be protected using a previously defined database master key or password. If password is used, it must be included in the certificate. The EncryptByCert function takes the certificate identifier and a string of characters as parameters for encryption purposes. It follows that the DecryptByCert function also takes the same parameters to retrieve and decrypt the data.
Certificates are also useful in securing the Service Broker authentication and messaging mechanisms or protecting SQL server authentication, signing of modules or encryption of data. Similar capacities are offered by asymmetric keys though the process is more complex.
In addition to the above functionalities the .NET Framework also allows the creation of user defined functions for encryption.
Code and Module Signing
Signing is the process of verifying authenticity of origin of data and its integrity. Like encryption it relies on certificates for functionality. Verification of integrity involves applying a public key to it and comparing the outcomes with the digital signature. Additionally SQL Server ensures that a particular resource can be accessed only via a module(such as a stored procedure). This functionality has been enhanced with the potential to use T_SQL statements. The digital signatures provide the mapping of user with digital certificate without recourse to ownership chaining.
SQL Server Agent Operations
SQL server no longer requires the Agent to be a member of the local administrators group. It is possible to create multiple proxy accounts and assign separate accounts to individual steps of the Agents jobs. Permissions on SQL Server Agent jobs has also been changed. The user must be a member of the SQLAgentUserRole in the msdb.Management for the purpose of creating roles. Proxy accounts can be created by Sysadmin fixed server roles.
Monitoring and Auditing
SQL profiler can be run without granting membership in the SysAdmin fixed server role. Auditing includes SQL Server 2005 activities and events generated by SQL Server 2005 Analysis services. Enhanced auditing is possible by assigning triggers to Data Definition language operations.
In this lesson we have attempted to provide the reader with a broad overview of the new features of SQL Server 2005. No attempt has been made to demonstrate the usage of these various components since the same was beyond the scope of this tutorial.