Just a small correction. Where it says "I use the private to encrypt data and someone else must use the public key to decrypt the data", actually it's the other way around. You or anyone encrypt with the public key, and the owner of the private key decrypts with the private key.

Great write up.  Does the certificates need to be renewed or does it ever exipire?  Thanks.

The CREATE CERTIFICATE T-SQL command has start_date and expiry_date parameters that you can specify for the time that the certificate is valid.  The default as start_date = current datetime and expiry_date = current datetime + 1 year.  It's probably a good idea to at least specify the expiry_date when you create a certificate.

You can get the details for CREATE CERTIFICATE here: http://msdn.microsoft.com/en-us/library/ms187798.aspx

So how do you allow only certain users to read the databases data, even excluding syadmins? i've tried the above, and although the db gets encrypted as you'd expect, it hasnt prevented any users on the system from reading the data held in the encrypted db. how do i achieve that?

Transparent data encryption is really the solution for making sure that someone can't read the data from outside of SQL Server.  In other words the data in the database files is encrypted and if you can access the file you can't read the data because you don't know how to decrypt it.  The transparent part is that SQL Server automatically encrypts and decrypts the data for users who have permission to access it.

User premissions can be handled n SQL Server Management Studio.  Connect to a SQL Server instance then expand the Security node in the Object Explorer.  The security model begins with logins that are given certain roles and access to databases, tables, stored procedures, etc.

There is a whole page of security tips here: http://www.mssqltips.com/sql-server-tip-category/19/security/

Hi, I have one query.. how we can know data is already encrypted OR data data is already decrypted so that a generic procedure do not try to encrypt already encrypted data or vice-versa.. 

use <yourdatabasename>

go

SELECT [name], is_encrypted FROM sys.databases

go

if is_encrypted = 1 then data is encrypted; if 0 then it's not encrypted

Thanks Ray...

If we encrypt a particular column in a table.. then is there a way to find before running script, data is Encrypted or decrypted in script. Problem is if data is decrypted and use to decrypted it agian it returns null. If perfom update to decrypt data (Encrypt/Decrypt update on same column) on same column all data get lost. So if somehow i can find in script that data is decrypted, we can bypass decryption step so data will not lost. Currenly using 'YES'/'NO' flag in some Reference table.

This tip covered transparent data encryption which is at the database level.  

As best as I can tell there is no way to determine whether a column of data is encrypted when you are using the built-in ENCRYPTBY functions in SQL Server.  I think your only choice is a custom solution.  You could stored procedures for all of your selects, inserts and updates so that you are certain that the column is encrypted on every insert and update and decrypted on every select.  Maybe you could use a trigger to make sure that the column get encrypted on every insert and update.

How i will make a connection string in visyal studio 2010 application to connect Encrypted Database. I want to know that after encrypted database the name of Table, its columns, stored procedure's name and their defination will be encrype or it will as readable by anyone.

The encryption is performed automatically as you insert or update data.  The only thing you can do is query to see whether the database is encrypted or not; e.g.

SELECT [name], is_encrypted FROM sys.databases

will show you each database and whether it is encrypted or not

There is nothing needed in the connection string as far as encryption is concerned 

Hey Ray. This is teriffic. Thanks for the clear explanations. I'm basically a programmer, but a complete newbie when it comes to Security and Encryption. We have a number of at rest databases in the organization I work for which are only used for internal purposes through in house developed applications.  We want to provide some level of security since at the moment we have none :-) It sounds like TDE would be a good first step at providing protection to our data without impacting the applications. Is this a correct assumption?

Also and please forgive this stupid question, but are there easy ways to protect data that flows from an application to a database through let's say an update statement? In other words if I have a simple ASP application running on a Server within our organization (no external facing) and enter my name and address in a form and then click OK to submit that data to the database, is my name and address being sent as plain text from the application server to the database server.

Thanks again and sorry for these questions.

Ned

I was wondering if there is anything I need to do when moving a server with encrypted databases from one domain to a new domain. Should I remove encryption of all the databases, move the server to the new domain, and then encrypt the databases again?

Thank you,

Wayne

I'm not sure if you have to do that but certainly it would be the conservative approach.

This is an interesting scenario that I will research as time permits.