Click here to Skip to main content
12,820,195 members (23,166 online)
Click here to Skip to main content
Add your own
alternative version


69 bookmarked
Posted 21 Mar 2006

Security in SQL Server 2005 as seen by a programmer

, 28 Aug 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
The goal of this article is to present elements of security in SQL Server such as: restrictive access to the server, blocking and safe configuration of services, and reduction of attack space for new elements. Techniques for creating and managing users and for securing data are illustrated.

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:

       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:

   WITH PASSWORD = 'password',
   DEFAULT_DATABASE = AdventureWorks,

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:


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:


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:


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.


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:


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:


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

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

Listing 2. Creation of a symmetric key

    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


Listing 4. Creation of a certificate

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ] 
      FROM < certificate_source >
      WITH < cert_options > 
<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:

    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.


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.


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.


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.

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.


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer's Journal
Poland Poland
Software Developer's Journal (formerly Software 2.0) is a magazine for professional programmers and developers publishing news from the software world and practical articles presenting very interesting ready programming solutions. To read more

You may also be interested in...


Comments and Discussions

SuggestionGood Article but requires additions and updates Pin
Suchi Banerjee, Pune2-Sep-13 4:12
memberSuchi Banerjee, Pune2-Sep-13 4:12 
GeneralShould be rewritten Pin
Balboos29-Mar-06 3:59
memberBalboos29-Mar-06 3:59 
The article has a lot of interesting and useful information. Being targeted toward beginners, however, earns it poor marks for the following reason:

Of those items with which I was not familiar, there was no word of explaination as to what their use was or why one would care to use them.

It would be better described as a 'feature list' than an as an 'article', and the title is not the best description of the content.

"The difference between genius and stupidity is that genius has its limits." - Albert Einstein

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170308.1 | Last Updated 28 Aug 2013
Article Copyright 2006 by Software Developer's Journal
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid