Re: SQL Server 2000 / 2005 Encryption

Excellent, thank-you Mike! This definitely points me in the right direction. Let me take a step back and explain what I am trying to accomplish and see if this makes sense. We will initially be doing this on a SQL 2000 machine.

We have data in a table that needs to be encrypted. Ultimately, this information needs to end up on a Tablet PC in Excel for users that have permission. So here is how I think we can accomplish this.

After I go through the steps you listed below, we need to distribute the certificate to the Tablet PC's and install it.
Once it is on the Tablet, does Excel just know to use this certificate because the information in the data dump / pivot table needs to be decrypted?
Is the same certificate used to secure the connection as well as decrypting the data in the table?

Does this make sense?

Thanks,
Rubens

"Mike C#" <xyz@xxxxxxx> wrote in message news:uRQC%23yOfIHA.5788@xxxxxxxxxxxxxxxxxxxxxxx

"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message news:uMkfFDNfIHA.4744@xxxxxxxxxxxxxxxxxxxxxxx
Would someone be able to provide some information on SQL 2005 encryption? I've searched online and through Books Online to determine from a high level perspective how it is works. Here is what I gather:

1. A certificate (through a 3rd party provider such as VeriSign or self-assigned by SQL 2005 itself) is installed on the server.
2. This certificate is then installed on a client machine.
3. Encryption is then enforced over the connection.

This appears to be a "connection only" type of encryption. So.

Can you encrypt the actual data in a database? Or more specifically, for a particular column in a table? If so, how?
Is this encryption enabled server wide, or for performance reasons, can you enable it only on a single database?

SQL Server 2005 has the ability to generate self-signed certificates also to encrypt SSL connections, so you don't need to go through Verisign or another CA to get this functionality.

SQL Server 2005 has the capability of encrypting columns within a table using built-in encryption statements. You need to create a database master key, then create a certificate, then create a symmetric key. You can find specifics in BOL under "CREATE MASTER KEY", "CREATE CERTIFICATE" and "CREATE SYMMETRIC KEY". At that point you can encrypt data on a per-column basis using built-in functions like "EncryptByKey" and "DecryptByKey".

You can encrypt columns in any database but the database master key is specific to each database.

How can I enable something similar in SQL 2000?

You need third-party software, like XP's in SQL 2000.

I would appreciate any additional online resources as I haven't been able to find much that answers my questions.

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/

http://www.sqlservercentral.com/articles/Security/sql2000dbatoolkitpart1/2361/




.

Relevant Pages

  • 2005-Problem restoring database with encrypted columns to diff ser
    ... I need to start encrypting several fields in a database and have been doing ... OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3'; ... ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; ... encryption by certificate test; ...
    (microsoft.public.sqlserver.security)
  • Re: Restoring Databases with Encrypted Data to Another Server
    ... SQL Server 2005 database. ... -- Create a Database Master Key ... ENCRYPTION BY CERTIFICATE TestCertificate; ... I backup the database and restore to another server. ...
    (microsoft.public.sqlserver.security)
  • Restoring Databases with Encrypted Data to Another Server
    ... SQL Server 2005 database. ... -- Create a Database Master Key ... ENCRYPTION BY CERTIFICATE TestCertificate; ... -- DecryptByKey demonstration decrypts the previously encrypted data ...
    (microsoft.public.sqlserver.security)
  • RE: 2005-Problem restoring database with encrypted columns to diff ser
    ... On a SQL Express instance, create a database/table, Master key, ... Certificate, symmetric key and insert the record with encrypted column. ... Backup/restore to the SQL Enterprise edition instance. ... >Where this did not work is when i created the intial database using SQL ...
    (microsoft.public.sqlserver.security)
  • Re: Encrypted database deployment
    ... I restored the database, ran the open and alter commands as below, but still ... An error occurred during decryption. ... it isn't the database master key so you now have two different master ... ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ...
    (microsoft.public.sqlserver.security)