Re: SQL 2005 Encryption questions

rcamarda (rcamarda@xxxxxxxxxxxxxx) writes:
What I want:
1. our social security field to be encrypted so that only the person(s)
that need it can decrypt it.
2. prevent DBA's from decrypting the data themselfs
3. Simple way to encrypt the data on the table (maybe a trigger?)

Nothing is simple when it comes to encryption. For starters, I hope that
you are perfectly aware of that if you encrypt the SSN, you cannot
use it to find a person, at least not effeciently?


I thought I would use asymmetric keys, this way I can embed the public
key into my data warehouse process to encrypt the data.
I thought I would prompt the user for the private key when the report
runs, that way I wont store the key on the server.

Cryptography is not my best game, but I thought you needed the private
key to encrypt something?

And I as I understand the topic for CREATE ASYMMETRIC KEY, you always
create or load a key-pair into the database.

I think what you should prompt the user for is the password to the
key. And the user will have to specify the password to encrypt the
data as well. At least, that is my understanding of it. But as I said,
I don't know cryptography too well.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: SQL Server 2000 / 2005 Encryption
    ... to encrypt your SSL connections will be different. ... SQL Server can generate its own self-signed certificates though, and that should make the SSL encryption/connectivity easier for you. ... Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form. ...
    (microsoft.public.sqlserver.security)
  • Re: Storing Connection String
    ... you can encrypt the traffic to the SQL Server. ... Hitchhiker's Guide to Visual Studio and SQL Server ... My idea is to have just one database user whose username and password ... Which means that if an user get hold of connection string ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Securing an MDF file
    ... Windows 2000 support encrypted file system property. ... Below are the steps encrypt the data files: ... Logon with the SQL Server startup account ... database files are encrypted under the identity of the account ...
    (microsoft.public.sqlserver.server)
  • Re: securing mdf files
    ... You can use the Encrypted File System Support on Windows 2000. ... Below are the steps encrypt the data files: ... Logon with the SQL Server startup account ... database files are encrypted under the identity of the account ...
    (microsoft.public.sqlserver.security)
  • Re: Encription for MDF files
    ... EFS by itself is not a complete solution. ... If the box is stolen - including the EFS recovery keys, ... then encrypt it before it is stored and decrypt it when retrieving. ... > 1) Logon with the SQL Server startup account ...
    (microsoft.public.sqlserver.security)