Re: SQL Server 2000 / 2005 Encryption


"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

  • Re: Connection to SQL Server CE Windows Service via C# - Error 250
    ... This is probably 3rd time I am hereing to a case the encryption related ... In SQL CE v3.1 case, can you try using Crypto API under the same credentials ... specific to your database or if it's really the password itself. ... connect to it via the windows service under the Local Service ...
    (microsoft.public.sqlserver.ce)
  • Re: An Encryption Strategy - Comments Please
    ... If that is a concern then use a secure connection to SQL. ... >>> I posted in this forum not long ago regarding encryption. ... >>> list in this same database. ... >>> DPAPI encryption is account dependent. ...
    (microsoft.public.dotnet.security)
  • Re: RDA and encryption
    ... You can password protect your SQL CE database and/or encrypt the contents ... > on the PocketPC supports any type of encryption? ...
    (microsoft.public.sqlserver.ce)
  • Re: Secure data in SQL Server 2005 Mobile Edition
    ... It turns out that the algorithm used depends on the version of SQL Compact and also what platform it's running on as not all platforms capable of running SQLce support the same encryption. ... how can the database be encrypted with just ...
    (microsoft.public.sqlserver.ce)
  • Re: Ecrypting a database
    ... Windows comes with a native file/folder encryption, ... account would need access to the database to unencrypt the ... >> I have a SQL database which will only be accessed only ...
    (microsoft.public.sqlserver.security)