Friday, May 18, 2007

Presentation Slides and Enabling Database File Encryption

The PowerPoint slideshow of the presentation I held at this year's NT Conference is ready for download. The presentation is in Slovene, but I'm willing to translate it to English (or German or Latin – if you give me some time ;) – leave a comment in this blog if you're interested.

The presentation provides an overview of cryptographic capabilities of SQL Server 2005, focusing on the Database Engine. I also provide a brief (and simplified) general overview of cryptography as a way to improve the security of data exchange.

The demonstration of how digital signatures can be used to allow cross-database access without resorting to cross-database ownership chaining is a simplified version of the demonstration provided by Raul Garcia in his blog, and is also available for download. I've included an additional script for you to play with.

If you've decided on improving SQL Server security using encryption then perhaps this is the first thing to consider: encrypting database files. Microsoft Windows (versions 2000 and later) support file encryption on NTFS drives – this is a certain way of preventing access to database files on illegally acquired devices (e.g. stolen notebooks and/or disks).


How to encrypt database files?

If the database has not been created yet then follow these steps:

  1. Make sure SQL Server 2005 Service is running in an appropriate Local User or Domain User account;
  2. Log on to the server using the service account. This is vital as the service account needs access to encrypted files;
  3. Access the properties of an existing folder or of a newly created one and check the »Encrypt contents to secure data« under »Advanced Attributes« (see illustration bellow). It is recommended that you encrypt the folder rather than individual files – encryption is automatically applied to new files as they are added to the encrypted folder.
Encrypting files and folders in Windows NTFS.

Encrypting files and folders in Windows NTFS.


If the database already exists then do this:

  1. Make sure SQL Server 2005 Service is running in an appropriate Local User or Domain User account;
  2. Detach the database (see Books Online for details);
  3. Log on to the server using the service account. This is vital as the service account needs access to encrypted files;
  4. Move the database files to the encrypted folder(s);
  5. Still logged on as the service user re-attach the database (see Books Online for details). Encrypted database files can only be attached by the user who encrypted them.

After completing the steps above the files are encrypted, and any user with sufficient permissions on the SQL Server instance (e.g. ALTER DATABASE) can create encrypted database files provided the correct folder is specified as the file path.

Simple, isn't it – yet so rarely used.


ML

Posted by at No comments:

Friday, May 11, 2007

SQL Server 2005 Cryptography, Symmetric key performance

As mentioned before, cryptography is a new functionality in SQL Server 2005. Most aspects of data encryption, digital signing of modules, and the use of cryptographic objects for authentication and authorization are covered in Books Online. Additionally, here are the links to a couple of blogs dedicated to cryptography in SQL Server 2005 (and SQL Server security in general) that I found very helpful while researching this new functionality:

I will be presenting most of what I've discovered regarding cryptography in SQL Server 2005 at this year's NT Conference in Portorož (Slovenia) on Wednesday, May 16th 2007. If you're there, you're very welcome to attend – you might even learn something. ;)

If you're interested in SQL Server 2005 security in general, though, you really should check out the SQL Server 2005 Security Workshop by Dejan Sarka on the same day. In fact, if you're administering or designing solutions on SQL Server you really should not miss this workshop. You can find more details at the NT Conference web site.

But now... a topic that I don't intend to discuss in detail at the conference.


Symmetric keys – to open or to auto-open?

Symmetric keys are recommended for data encryption by Books Online as they provide better performance when encrypting and/or decrypting data in SQL Server 2005 compared to asymmetric keys and certificates (at least according to Microsoft). Cryptographic functions provided by the platform (in general) are not entirely problem-free (e.g. data-size limitations discussed in the previous post), but let's not get into all that this time. Symmetric keys in SQL Server 2005 support the following cryptographic algorithms: DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256. The actual availability of these depends on the operating system (e.g. AES is not available on Windows 2000 or XP).

Encrypting data with symmetric keys is done using the EncryptByKey system function. This method requires the symmetric key to be opened prior to the function's invocation using the OPEN SYMMETRIC KEY statement.

Similarly, data decryption is done pretty much in the same way, using the DecryptByKey system function (after opening the key). There are two alternatives to this method, though:

  • DecryptByKeyAutoCert – can be used for data decryption by symmetric keys protected by certificates; and
  • DecryptByKeyAutoAsymKey – can be used for data decryption by symmetric keys protected by asymmetric keys.

Both these functions open the symmetric key implicitly.

I was curious whether explicit or implicit opening of the symmetric key influences the performance of these functions in any way. For this purpose I've prepared a rather simplistic set of encrypted values available in this script that you can execute in your own favorite testing database.

The script creates a table and fills it up with short strings (in order not to influence execution significantly). In addition, a certificate is created and then used to protect a newly created symmetric key. This symmetric key uses the DESX cryptographic algorithm.

To see how decryption functions perform on this rather small but IMHO fairly representative set of encrypted values I first traced the execution of two retrieval queries against the data without decrypting the values. This was then followed by two pairs of queries using the OPEN SYMMETRIC KEY/DecryptByKey method and then using the decryptByKeyAutoCert method. BTW: the script produced 148877 rows on 1128 pages with 44 Bytes of encrypted data per row. The only index is the clustered primary key on StringId – the question of how encrypted data could or should be indexed will not be answered in this article.

These are the results (the queries can be found at the bottom of this post – just follow the links in the Query column):

Query Performance
CPU Reads
#1 281 1138
#2 8736 1140
#3 7284 1140
#4 68 1138
#5 100 1140
#6 96 1140

In contrast to queries #1, #2 and #3 (unrestricted) the result sets from queries #4, #5 and #6 are restricted to the »first« 100 rows (i.e. StringId < 101). Queries #1 and #4 retrieve unencrypted values, queries #2 and #5 use the OPEN SYMMETRIC KEY/DecryptByKey method, and queries #3 and #6 use the DecryptByKeyAutoCert method.

Apparently opening keys automatically (i.e. implicitly) outperforms opening keys manually (i.e. explicitly). Who would have thought...? Although the advantage is only slight (5–10%) it may prove significant on larger sets of data. Can you reproduce this on your system?

Of course decrypting values puts additional stress on the CPU (compared to the retrieval of encrypted data) but with only 2 additional reads. The latter is needed to retrieve (and decrypt) the symmetric key. Well, there you have it. In one of the next posts we'll try to discover why asymmetric keys and certificates are said to be less desired for data encryption (performance-wise) than symmetric keys.


ML


  • Query #1: 
    select dbo.Strings.StringId
 ,dbo.Strings.EncryptedString
 from dbo.Strings
  • Query #2: 
    open symmetric key CryptographyPerformanceSymKey
 decryption by certificate CryptographyPerformanceCert

select dbo.Strings.StringId
 ,cast(decryptByKey(
   dbo.Strings.EncryptedString
   ) as nvarchar(max)) as String
 from dbo.Strings

close symmetric key CryptographyPerformanceSymKey
  • Query #3: 
    select dbo.Strings.StringId
 ,cast(decryptByKeyAutoCert(
  cert_id('CryptographyPerformanceCert')
  ,null
  ,dbo.Strings.EncryptedString
  ) as nvarchar(max)) as String
 from dbo.Strings
  • Query #4: 
    select dbo.Strings.StringId
 ,dbo.Strings.EncryptedString
 from dbo.Strings
 where (dbo.Strings.StringId < 101)
  • Query #5: 
    open symmetric key CryptographyPerformanceSymKey
 decryption by certificate CryptographyPerformanceCert

select dbo.Strings.StringId
 ,cast(decryptByKey(
   dbo.Strings.EncryptedString
   ) as nvarchar(max)) as String
 from dbo.Strings
 where (dbo.Strings.StringId < 101)

close symmetric key CryptographyPerformanceSymKey
  • Query #6: 
    select dbo.Strings.StringId
 ,cast(decryptByKeyAutoCert(
   cert_id('CryptographyPerformanceCert')
   ,null
   ,dbo.Strings.EncryptedString
   ) as nvarchar(max)) as String
 from dbo.Strings
 where (dbo.Strings.StringId < 101)
Posted by at No comments:
Subscribe to: Posts (Atom)