I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?
I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.
What are your ideas on this matter?
TIA,
barkingdog
Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.
If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?
Thanks
Laurentiu
Laurentiu,
From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?
My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.
Barkingdog
|||Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.
For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.
For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.
For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.
In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.
If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.
Thanks
Laurentiu
Hi,
I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.
This user is used by my UI(ASP.NET) for querying the DB.
All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.
Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:
select EncryptXXX(Key,Column) from table
To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:
Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...
Anybody who is running the profiler can now read the password as the profiler does not block the same.
What is the best way to overcome this?
Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.
Kindly correct me if I have misqouted anything.
1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.
2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.
However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?
Thanks
Laurentiu
Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).
So the programmers themselves MIGHT be hackers...
Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||
As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?
Thanks
Laurentiu
When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"
Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).
Where and how exactly do you want me to store the password that protects the key?|||
I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.
Thanks
Laurentiu
Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:
1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.
I choose the second one therefore the problem.|||
Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).
Thanks
Laurentiu
Before I implement your idea i would like to know more about the defenition of a current session.
I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?
One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||
Hi,
You can use CLR function to open symetric key , The script will be in assembly
and the password is hidden at all.
Thanks,
Tarek Ghazali
SQL Server MVP
web site : www.sqlmvp.com
|||Hi,
I am totally new to this. Could you possibly giude me to some tutorials on the same?
No comments:
Post a Comment