Database security issue in MSSQL vs. MYSQL_问答_开发者

I have a security issue with one of our distributors. I need to prevent them from accessing certain data in our database. Because they administer the database I can't use password controls on the开发者_开发技巧 database itself to protect the data. I'm thinking that I need to encrypt the sensitive data in the database and have the encryption key embedded in software that we create. That way the distributor can poke around in the database all they want but the sensitive data will be unintelligible to them. I see how this might be done in MYSQL. There is are ENCODE and DECODE functions that allow me to pass a key in as a parameter. I can embed these keys in a piece of software, lock the software down, and this will allow me to insert and retrieve information. But what about MS SQL. How can I encrypt data so that even someone who is an administrator of the database cannot read that data?

Thank you,

Elliott

For MS SQL there is tons of documentation on encryption, and based on what you said about MYSQL I think you will be most interested in the Cryptographic Functions.

Note that I would be careful on which methods you encrypt with your own key, as a trace on the database or other means could be used to still obtain the key by inspecting the SQL commands you are sending.

There is definitely encrypt/decrypt functionality in MSSQL:

http://blogs.msdn.com/b/lcris/archive/2007/10/03/sql-server-2008-transparent-data-encryption-feature-a-quick-overview.aspx

http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/ [this one is a tutorial]

You probably want the code based one, as opposed to file encryption...

From this article: http://www.sql-server-performance.com/articles/dev/encryption_2005_2_p1.aspx

(Which BTW is the first result for a google search of encrypt data sql server)

Symmetric Keys

A Symmetric Key is a one key that is used for both encryption and decryption. This is a fast, commonly used method for encryption. As with a certificate, many parameters are available when creating Symmetric Keys. In most cases, we only need a limited set of parameters. You can refer to Books Online for all the parameters.

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertificateTest2

You can specify an encryption algorithm for a symmetric key. DES, TRIPLE_DES, RC2, RC4, DESX, AES_128, AES_192, and AES_256 are the encryption algorithms available for SQL Server data encryption. AES encryption algorithms are currently supported only on Windows 2003. If you are using Windows 2000 or Windows XP, you do not have access to AES encryption algorithms from SQL Server. If you try to use an AES encryption algorithm, you will get an error:

Either no algorithm has been specified or the bit length and the algorithm specified for the key are not available in this installation of Windows.

You can encrypt a Symmetric Key using PASSWORD, SYMMETRIC KEY, ASYMMETRIC KEY, or CERTIFICATE.

The ALTER SYMMETRIC KEY and DROP SYMMETRIC KEY commands are also available to improve maintenance.

After the creation of a Symmetric Key, it is just a matter of using it. There are three functions you can use with Symmetric Keys: Key_GUID, EncryptByKey, and DecryptByKey.

OPEN SYMMETRIC KEY TestSymKey
     DECRYPTION BY CERTIFICATE CertificateTest2;

DECLARE @Encryptvalsym varbinary(MAX)
SET @Encryptvalsym = EncryptByKey( key_guid('TestSymKey'),'scott')

SELECT convert(varchar(max),DecryptByKey(@Encryptvalsym))

Before using SYMMETRIC KEY, you need to open it by using the OPEN SYMMETRIC KEY command. To encrypt data you need to pass the GUID of the Symmetric Key. However, to decrypt it you don't have to pass the GUID. Instead, you have to open the encrypted Symmetric Key using the correct decryption method and value.

To increase the security of Symmetric Keys, you can add an authenticator during encryption. You have to use the same value during decryption.

OPEN SYMMETRIC KEY TestSymKey
     DECRYPTION BY CERTIFICATE CertificateTest2;

DECLARE @Encryptvalsym1 varbinary(MAX)
SET @Encryptvalsym1 = EncryptByKey(key_guid('TestSymKey'),'scott',1,'SQL Server')

-- Decryption with Symmetric Keys

SELECT convert(varchar(max),DecryptByKey(@Encryptvalsym1,1,'SQL Server'))

I would skip the database centric algorithms and move it to a software managed solution. That way you can completely avoid the key ever being sent over the wire and compromised by a packet sniffing admin. (This is of course assuming you are using default transports for your database connection)

I've used this link as a reference for creating my DataSecurity library. I prefer the software managed scenario because it allows you to go as heavy or as lightweight as the solution requires, and it allows you to manage the keys in a way that compromising your database does not mean that your data gets compromised as well.