Sql-server – Is encrypted database possible

encryptionSecuritysql-server-2008

I'm not a DBA but I've been asked to see if we can encrypt the data in sql server such that the admin, despite having access to SQL Server won't be able to see the data but application would still be able to query on it and read the data.

My limited knowledge of encryption tells me that if application encrypts the data then db won't be able to query it. If db encrypts the data then DBA can definitely read it.

So is there a way to prevent DBA from peeking into data but still be able to manage database?

Best Answer

You will need to encrypt the data within the application and store the encrypted values within the database. Things like TDE and storage level encryption will not meet your requirements.

Related Solutions

Implementing an encrypted table in SQL Azure

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT. You will see that all of the encryption functions are listed as unsupported.

If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to do your encryption at the application level. This is more difficult to get right as lots of the good encryption practices (using nonces as initialization vectors for each cell, key management for tracking which keys encrypt which columns, integrity verification, layered protection of encryption keys with role based permissions) you will have to implement yourself. I strongly recommend getting a security professional to work with you on the encryption as it is challenging to get right and if you miss something then you have a false sense of security that could really burn you later.

You could look at http://securentity.codeplex.com, which claims to be a solution for managing encrypted data and supports SQL Azure. I have not personally used it, nor can I vouch for it's correctness, but it is a potential option.

Other information:

In SQL Server column level encryption is easy and very secure (even with a view) because you can use roles to manage who can access the encryption keys. Even if a user can select from a view, if they are not granted access to the key that encrypts the underlying data then they will get back NULLs for those encrypted columns. I have a set of sample code for SQL Server 2005 and higher that demonstrates the encryption capabilities of SQL Server here: http://sqlcrypto.codeplex.com

Sql-server – How to restrict SQL server profiler to show the symmetric key password while executing query from C# code

Don't execute the statement as a string by using sp_executesql

Execute the statement directly:

OPEN SYMMETRIC KEY sym_RegAna_Default DECRYPTION BY ASYMMETRIC KEY asym_RegAna_Default WITH PASSWORD = 'admin@123'

That way, SQL Server is able to parse the statement and will notice that it's a command that contains sensitive information and will not show it in any trace output.

it will show up as:

--*OPEN SYMMETRIC KEY----------------------------

Please be aware that this applies to ALL statements that contain passwords. So also when you create the keys.

Best Answer

Related Solutions

Implementing an encrypted table in SQL Azure

Sql-server – How to restrict SQL server profiler to show the symmetric key password while executing query from C# code

Related Question