Real World DBA Episode 17 – SQL Server Features – Encryption

This week:

In the news, CU 8 is now available for SQL Server 2005.

In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about SQL Server Encryption.

The web link is on the maximum capacities for SQL Server 2005,

and the tip this week deals with the file locations and registry entries for SQL Server 2005.


In the News:

The eighth Cumulative Update, or CU, is now available for SQL Server 2005. Makes sure you check out the link and read more about the specific issues it fixes. Find it at 



In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at the features you have available for encryption in SQL Server’s Web Service.


Encryption is simply hiding data. The word itself means “to bury”. There are times when you need to encrypt data so that no one can see it – and SQL Server provides several mechanisms to do that.


You can use three basic methods to encrypt data in SQL Server 2005: symmetric keys, asymmetric keys, and certificates. You can also encrypt your keys with a certificate – more on that in a moment.


Symmetric keys are the easiest method to encrypt data in SQL Server. The symmetric means “equal on both sides”, so all that is needed here is a password that you both encrypt and decrypt with. To create a symmetric key, you can use the CREATE SYMMETRIC KEY Transact-SQL statement, which I’ll cover in a future podcast. This statement creates a key for your use and stores it in the database. Since the password is the same for encrypting and decrypting data, if it ever gets out your security is compromised.


The second option is the Asymmetric Key. This means “not equal”, so in this case one key encrypts the data and another decrypts it. The command here is CREATE ASYMMETRIC KEY, which again, I’ll explain in another podcast.


The final option is to use a certificate to encrypt SQL Server data. A certificate is a long key generated by an authority, which should be someone other than yourself. That third party digitally “signs” the certificate, so both the encrypting and decrypting parties can trust that the contents haven’t been tampered with. In fact, you can secure your keys, your connection to SQL Server, and even backups with a certificate. You can create a certificate using the CREATE CERTIFICATE statement.


With your security methods in place, you can use OPEN SYMMETRIC KEY, OPEN ASYMMETRIC KEY, ENCRYPTION BY CERTIFICATE, ENCRYPTION BY CERTIFICATE, EncryptByKey() and DecryptByKey() statements and functions to encrypt the contents of a column of data.


Of course, nothing in life is free, so it will cost you a little to encrypt and decrypt data. The overhead is small, however, and the security you gain is usually worth the tradeoff for performance.


Web Link:

Haven’t you always wanted to know how far you can push SQL Server 2005? The web link for this week shows you the maximum capacity specs. Find it at


Tip of the Week:

You can find all of the file locations and registry settings for SQL Server 2005 online. Check out


Comments (0)

Skip to main content