Security in SQL Server 2005 as seen by a programmer

This article is written by Artur Żarski and was originally published in the September 2005 issue of the Software Developer's Journal. You can find more articles at the SDJ website.

Trustworthy Computing

The concept of Trustworthy Computing is based on three pillars: security, confidentiality of data, and reliability. These pillars could be described in the following way:

• Security: the systems feature security measures, which simplify protection against attacks originating from the network. Security features contribute to an increased level of privacy, integrity, and accessibility of the system and the data.
• Confidentiality of data: users can influence the security of data related to them and how this data is used, as well as verification of compliance of data gathering-, supervision- and usage-related processes with accepted policies of reliable information processing.
• Reliability: guarantee of accessibility for vital system-related and services-related functions, especially in the context of operations which are of core importance to the activity of client organizations.

In the recent years, Microsoft has introduced the Trustworthy Computing initiative, an initiative for a secure computing environment. In order to keep this initiative in effect, the SQL Server team has established the following assumptions for security:

• security by definition,
• security in programming,
• security in communication.

SQL Server 2005 sports the following new security features:

• restrictive access to the server,
• blocking and safe configuration of services,
• reduction of attack space for new elements.

The goal of this article is to present those elements of security in SQL Server, which are important from the point of view of a programmer. Here, security can be divided into two parts, the first being the security of the database, and the second – of the data itself. In the first part of the article, we will demonstrate new techniques of creating and managing users; in the second, we will show how to secure our data – e.g., by encrypting it.

User Management

In SQL Server 2000, in order to create a user, one had to use the stored procedure sp_addlogin. In the new SQL 2005, user management has been severely modified. Very strong emphasis has been put on better integration of users with the operating system and the security policies it defines; for this purpose, the CREATE LOGIN instruction has been introduced. This instruction lets one manage the more advanced functions of password expiration, and enforcement of defined rules of password quality.

The following example demonstrates the instruction's most basic use – creation of a user with a certain password and granting him rights to a default database:

CREATE LOGIN ArturZ
       WITH PASSWORD = 'password', 
       DEFAULT_DATABASE= AdventureWorks

Another step is the option to integrate with operating system policies. For this purpose, we will extend our instruction with additional keywords, thus obtaining the following form:

CREATE LOGIN ArturZ
   WITH PASSWORD = 'password',
   DEFAULT_DATABASE = AdventureWorks,
   CHECK_POLICY = ON,
   CHECK_EXPIRATION = ON

We will be able to check if these settings work properly if we define appropriate policies within the operating system. Let us use the console to set the minimum password length to 8 characters.

Figure 1. The password policy-changing console

In order to check if the policies are in effect, let us attempt to change the password of our user:

ALTER LOGIN ArturZ WITH PASSWORD = 'az'

This should give us the following result:

Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet 
         policy requirements because it is too short.

In order to create a database user, based on an operating system login, the following instruction will come in handy:

CREATE LOGIN [server\ArturZ] FROM WINDOWS
   WITH DEFAULT_DATABASE = AdventureWorks

Database schemas

A schema is a group of objects, which lets one easily secure access to data. This makes it possible to assign a user to a schema in a very simple way, using T-SQL commands. In order to use schemas, we have to create them first, using the CREATE SCHEMA instruction:

CREATE SCHEMA MySchema

Creating tables within a schema requires the schema.table construct. Below we show how to create a table in the schema we have just created:

CREATE TABLE MySchema.MyTable (FieldA int, FieldB int)

There is a special schema called sys. It is automatically created for each database and contains all system objects.

Changing the Security Context

As system designers, we often wonder about ways of enabling a procedure or function to be run by a user who normally is not allowed to do so. To address this issue, we have created a new clause, EXECUTE AS, which allows changing the user context. Let us thoroughly examine the following example to understand how this clause operates. User A possesses execution rights to the ProcedureA procedure. This procedure operates on objects ObjectA, ObjectB, and ObjectC. User A has only got rights to ObjectA; ObjectB and ObjectC belong to user B. If user A calls the procedure, it will be aborted the moment it addresses an object user A does not have access to. In order to prevent this, one can employ the EXECUTE AS clause, which will execute the procedure in the security context of user B.

This clause can be used with stored procedures and user-defined functions.

CREATE PROCEDURE ProcedureA
WITH EXECUTE AS 'B'
AS
…..

The example shows how to create a procedure which will allow anyone to call it with the rights of user B.

Moreover, it is possible to use this clause in the EXECUTE AS SELF form. EXECUTE AS SELF lets one execute a procedure in the context of the user who calls it.

Rights to Objects

Let us also discuss the possibility of granting rights to objects. In order for the user to be able to use a certain object, they must possess appropriate access rights to it. We use the known GRANT instruction to grant access rights to an object, for example:

GRANT EXECUTE ON procedure TO UserA

We have already know this instruction from the previous version of the database. What is new is the possibility of granting rights to a schema:

GRANT EXECUTE ON Schema::MySchema TO ArturZ

What is more, it is now possible to grant special rights to a user, who is to be allowed to create HTTP ENDPOINT objects but to have no access to other objects. An appropriate instruction would look like this:

GRANT ALTER ANY http ENDPOINT TO User

Using the elements we have just mentioned, we can integrate the security of our operating system and our database. Employing password policies lets database users follow the appropriate policies of the company.

Data Encryption

Encryption of data has always been an extremely important element of the security of database systems. Many programmers develop their own encryption procedures and functions, based on various mechanisms of data encryption and decryption.

Microsoft SQL Server 2005 offers very strong support for encryption mechanisms. This takes a lot of the burden off the programmer, not to mention that the mechanisms built into the database can, in many cases, turn out to be more efficient than custom solutions. The new database supports symmetric and asymmetric encryption, as well as the use of certificates. The following encryption algorithms are available: DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, and AES_256 as symmetric ones and, as an asymmetric one, the RSA algorithm with keys 512, 1024, or 2048 bits long.

The following new functions have been created to make data encryption possible in custom applications: EncryptByKey, DecryptByKey, EncryptByPassPhrase, DecryptByPassPhrase and Key_GUID, Key_ID. The EncryptByKey, DecryptByKey functions are used for encryption and decryption with a defined key, EncryptByPassPhrase and DecryptByPassPhrase are used for encryption and decryption with a given phrase.

The following steps have to be taken to be able to work with encrypted data:

• create a master key (there can be more than one),
• create a certificate, if necessary,
• create a symmetric or an asymmetric key (a symmetric key can be additionally encrypted with a certificate or an asymmetric key),
• open the key,
• once the key has been opened, it is possible to work with encrypted data,
• close the key.

In order to create keys, we use the new elements of the CREATE clause:

• CREATE MASTER KEY – to create a master key,
• CREATE ASYMMETRIC KEY – to create an asymmetric key,
• CREATE SYMMETRIC KEY – to create a symmetric key,
• CREATE CERTIFICATE – to create a certificate.

The syntax for each particular kind of key is demonstrated in Listings 1, 2, and 3.

Listing 1. Creation of an asymmetric key

CREATE ASYMMETRIC KEY Asym_Key_Name 
   [ AUTHORIZATION database_principal_name ]
   {
      FROM <Asym_Key_Source>
      |
      WITH ALGORITHM = { RSA_512 | RSA_1024 | RSA_2048 }
   }
   [ ENCRYPTION BY PASSWORD = ' password ' ]
 
<Asym_Key_Source>::=
   FILE = ' path_to_strong_name_file '
   |
   EXECUTABLE FILE = ' path_to_executable_file '
   |
   ASSEMBLY Assembly_Name

Listing 2. Creation of a symmetric key

CREATE SYMMETRIC KEY key_name [ AUTHORIZATION owner_name ]
    WITH < key_options > [ , ... n ]
    ENCRYPTION BY < encrypting_mechanism > [ , ... n ] 
 
<encrypting_mechanism >::=
    CERTIFICATE Certificate_Name
    |
    PASSWORD = ' password '
    |
    SYMMETRIC KEY Symmetric_Key_Name
    |
    ASYMMETRIC KEY Asym_Key_Name    
 
<Key_Options >::=
    DERIVED_FROM = ' pass_phrase '
    |
    ALGORITHM = < algorithm >
    |
    IDENTIFIED_BY = ' identity_phrase '
 
<algorithm >::=
    DES | TRIPLE_DES | RC2 | RC4 |
    DESX | AES_128 | AES_192 | AES_256

Listing 3. Creation of a master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' password '

Listing 4. Creation of a certificate

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ] 
    {
      FROM < certificate_source >
      |
      WITH < cert_options > 
    }
    [ ACTIVE FOR BEGIN_DIALOG =  { ON | OFF } ]
 
<certificate_source >::= 
    {
      FILE = ' path_to_certificate '
      |
      EXECUTABLE FILE = ' path_to_file '
      |
      ASSEMBLY assembly_name 
   }
   [ WITH PRIVATE_KEY ( < private_key_options > [ , ...] )
 
<private_key_options >::=
    FILE = ' path_to_private_key ' 
    DECRYPTION_PASSWORD = ' key_password ' 
    ENCRYPTION_PASSWORD = ' password '
 
<cert_options >::=
    { 
       SUBJECT = ' certificate_subject_name '    
       [ , START_DATE = ' mm/dd/yyyy ' ]
       [ , EXPIRY_DATE = ' mm/dd/yyyy ' ]
       [ , ENCRYPTION_PASSWORD = ' password ' ]
    }

Having defined all the required elements, we can begin to develop our encryption-based solution. In order to see what the whole process looks like, let us create a table in the database, create the keys, and then populate the table with data, which will be encrypted at the time of insertion. To begin with, let us create a simple table:

CREATE TABLE TabEncr (
    id int identity (1,1), 
    NonEncrField varchar(30),  
    EncrField varchar(30)
)

Then we will create a master key. This key is always encrypted by the database using the triple DES algorithm and stored in the sys.symmetric_keys table. At the same time, the master key for the database gets encrypted with the service key and is stored in this form in the sys.databases table.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'

Now it is necessary to create keys – let us create a symmetric key and an asymmetric one. Information about keys is stored in the tables sys.symmetric_keys and sys.asymmetric_keys. In our case, the symmetric key will be used in conjunction with the asymmetric one.

CREATE ASYMMETRIC_KEY asym_Key WITH ALGORITHM = RSA_1024
CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM = 
       DES ENCRYPTION BY ASYMMETRIC KEY asym_Key

As mentioned above, another step we have to take is to open the key to be used; this is very similar to using cursors. In our case, we will have to indicate that the symmetric key will be used together with the asymmetric one.

OPEN SYMMETRIC KEY sym_Key USING ASYMMETRIC KEY asym_Key

At this point, we have got defined keys, which we have also opened so that they are now ready to use; it is therefore time to commence entering data into our table. The data will be inserted into the table using the sym_Key, or, to be exact, its identifier.

First of all, we have to obtain the identifier (GUID) of the key. We will store this identifier in a temporary variable, which we will be using in our queries. In order to encrypt data, we will make use of the EncryptByKey function. The parameters it takes are the identifier of our key and the text we want to encrypt.

DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = (SELECT key_guid FROM sys.symetric_keys 
             WHERE name = 'sym_Kluczey')
INSERT INTO TabEncr (NonEncrField, EncrField) 
       VALUES ('Unencrypted data', 
       EncryptByKey(@GUID, 'Encrypted data'))

To be able to read the data, we will use the DecryptByKey function, providing the name of the column as its parameter. This function returns a string of characters of the VARBINARY type, we will therefore convert it to VARCHAR using the CAST function.

SELECT CAST(DecryptByKey(EncrField) AS VARCHAR(30)) FROM TabEncr

Our work having finished, we now have to close our key.

CLOSE SYMMETRIC KEY sym_Key

From the administrator's point of view, it is not necessarily desirable for programmers to have access to keys – one might therefore think about creating an intermediate table with a TRIGGER. This trigger would move data from the intermediate table to the proper one and get it encrypted in the process, without divulging information to programmers. Of course, the number of possible approaches to the topic is large and everyone is free to develop their own method, based on the mechanisms described above.