Browse DevX
Sign up for e-mail newsletters from DevX


The 'Secure by Default' Features of SQL Server 2005 : Page 3

SQL Server 2005 offers improvements in authentication and authorization, as well as native support for cryptography—features that enable more secure database application design and implementation than previous releases.




Building the Right Environment to Support AI, Machine Learning and Deep Learning


When you use cryptography in an application, you must manage the key (either the private key of an asymmetric algorithm or the shared key of a symmetric algorithm). SQL Server 2005 provides two options for key management:
  • Manage the key yourself
  • SQL Server manages your key

When you manage the key yourself, SQL Server stores the symmetric key with a given password in the database and you must keep the password in a secret place. When SQL Server does the key management for you, it uses the Service Master Key and the Database Master Key. Figure 5 illustrates the idea behind this concept.

Figure 5. Service and Database Master Keys in SQL Server 2005

As you can see, the Service Master Key lives on the server level. This key is created during the installation of SQL Server and is protected through the Data Protection API (DPAPI). Through several T-SQL statements, you can dump the Service Master Key to a file and restore it from a file.

The Database Master Key lives on the database level and must be created explicitly through an administrator. It can be encrypted through a password or through the Service Master Key. Listing 3 shows how you can create the Database Master Key:


As soon as you have created the Database Master Key, you have the following options:

  • Encryption with a symmetric key
  • Encryption with an asymmetric key
  • Encryption with a certificate

You can create a symmetric key with the T-SQL statement CREATE SYMMETRIC KEY. The encryption is based on either a password or the Database Master Key, which is stored in the table sys.symmetric_keys. To work with a symmetric key, you must open it in the first step (this includes the decryption of the key) with the statement OPEN KEY (see Figure 6). After that, you can use the key for your own encryption purposes.

Figure 6. Encryption with a Symmetric Key

The benefit of a symmetric key is its performance, which is about 1,000-10,000 times faster than that of an asymmetric key. The drawback is that only one key is used for encryption and decryption and both parties must know this key. You create an asymmetric key with the statement CREATE ASYMMETRIC KEY. Such a key also can be encrypted with a password or the Database Master Key. Listing 4 shows how you can create an asymmetric key:

Listing 4 -- Encryption with a password CREATE ASYMMETRIC KEY MyKeyName AUTHORIZATION User1 WITH ALGORITHM = RSA_512 ENCRYPTED BY PASSWORD = 'p@ssw0rd1' -- Encryption with the database master key CREATE ASYMMETRIC KEY MyKeyName AUTHORIZATION User1 WITH ALGORITHM = RSA_512

An asymmetric key is always stored in the table sys.asymmetric_keys. After you have created the key, you can use the function EncryptByAsmKey to encrypt data and DecryptByAsmKey to decrypt the encrypted data. Listing 5 shows the usage of both functions:

Listing 5 DECLARE @EncryptedStuff varchar(1000) SELECT @EncryptedStuff = EncryptByAsmKey(AsymKey_ID('MyKeyName'), 'My secret message') SELECT @EncrytedStuff SELECT CAST(DecryptByAsmKey(AsymKey_ID('MyKeyName'), €EncryptedStuff) AS VARCHAR)

Finally, you can use a certificate to encrypt data, either an existing certificate by importing it into the database server or a new one by using the T-SQL statement CREATE CERTIFICATE to create it. A certificate can also be encrypted through a password or through the Database Master Key. Listing 6 shows the two possibilities:

Listing 6 -- Encryption with a password CREATE CERTIFICATE MyCertificateName AUTHORIZATION User1 WITH Subject 'My Subject', EXPIRY_DATE = '12/31/2006', ENCRYPTION_PASSWORD = 'p@ssw0rd1' -- Encryption with the database master key CREATE CERTIFICATE MyCertificateName AUTHORIZATION User1 WITH Subject 'My Subject'

Samples related to the cryptography features are also available as a download in the file Encryption.sql.

New Security Functionality

As you have seen from the samples, SQL Server 2005 provides a lot of new functionality in the area of security. The big improvements are without any doubt the cryptography functionalities. The Service Master Key is also a very nice little feature—you don't have to maintain your own keys because SQL Server handles them for you.

Klaus Aschenbrenner is software architect and consultant for ANECON in Vienna, Austria. He helps software architects and developers design and implement enterprise solutions based on the .NET Framework and Web services. Find further information about Klaus Aschenbrenner at his weblog.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date