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; andDecryptByKeyAutoAsymKey– 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)
No comments:
Post a Comment