January 17, 2013 at 12:42 pm
Hi all. I am researching the feasability of encrypting a few columns in one of our databases. I have done much reading on the topic but still have a few questions, and was wondering if anyone could shed some light.
I am thinking of encrypting data using a Database Master Key (DMK) to create a cert, then in turn a symmetric key from the cert. I have read that the DMK is automatically stored in the local database against which it was generated, and is also stored in the master database as well (Automatic Key Management), so that’s all fine.
Once site, however, brought this up as a concern: “The potential downfall of automatic key management is that it allows every sysadmin to decrypt the DMK.” Are they speaking about the literal SA account in SQL Server, or anyone with dbo access and above?
And I have seen this caveat pertaining to the Create Certificate command: “The create-cert process… requires CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.”Okay, I need to break that down a bit. All of my users, internally and externally, are mapped to database roles, so they obviously cannot “own” a certificate, but can they access it? I mean, if I create the cert and store it, can sprocs run by users mapped to database roles then reference/call that cert for encryption/decryption purposes?
And how and where is the cert stored? Is the FILE ='path_to_private_key' attribute used to store or retrive a cert? Seems to me that attribute is only for loading FROM a file. My assumption is that certs default to the database --> Security --> Certificates directory, but we all know where assumptions land us.
And once symmetric keys are generated from that cert, can they be persisted or are they connection scoped? Again, my assumption is that they default to the database --> Security --> Symmetric Keys directory, but... see above.
And to answer the question "Why not just try it..." The environ,ent I work in is restricted and it takes a blood donation and signed Papal Bull to get any sort of access or status changed on the server, so I am trying to do as much of the hypotheical work up front before unleashing even test code.
Thanks! Kurt
January 17, 2013 at 3:11 pm
Okay, I DID manage to move ahead a bit, and in the process answered at least some of my questions. For starters, the certificate and symmetric keys are indeed automatically saved to their respective folders under the Security folder of the spefic database.
But while this T-SQL...
SELECT d.is_master_key_encrypted_by_server
FROM sys.databases AS d
WHERE d.name = 'AdventureWorks';
... shows that I have successfully created a Database Master Key (DMK) on AdventureWorks, I cannot see any keys logged in the sys.symmetric_keys catalog. I return a 0 results, no-error results set. When our DBA elevates me temporarily to SA I can see the Service Master Key (SMK) using this query, but not the DMK. As soon as SA rghts are pulled the sys.symmetric_keys catalog is invisible to me again.
Encryption seems to just fail without error as well. neither of these statements returns anything but nulls.
UPDATE
Sales.CreditCard_ENCRYPTION
SET
CardNumbENC = EncryptByKey(Key_GUID('TestSymmetricKey'), CardNumber);
SELECT
'Enc' = EncryptByKey(Key_GUID('TestSymmetricKey'), CardNumber)
FROM
Sales.CreditCard_ENCRYPTION;
Next I tried to first open the symmetric key using:
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCert;
But I received this error:
Msg 15334, Level 16, State 1, Line 2
The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.
But none of the examples I have seen show a password being needed to open the key. They all use the syntax above.
If I open the symmetric key with the cert + password it works:
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCert
WITH PASSWORD = 'thisIsAP@$$w0rd';
But doesn't this mean I need the password inside EVERY proc I write that touches an encrypted column? isn't this adding insecurity into the system? What if the password needs to change, it will be scattered throughout the system.
Iam sure there must be a way to do this without explicitly calling the password.
January 18, 2013 at 1:25 am
kpwimberger (1/17/2013)
...But doesn't this mean I need the password inside EVERY proc I write that touches an encrypted column? isn't this adding insecurity into the system? What if the password needs to change, it will be scattered throughout the system.
...
By doing this, you shift the responsibility for key management from SQL
Server to yourself. Sometimes this is needed...
kpwimberger (1/17/2013)
...Iam sure there must be a way to do this without explicitly calling the password.
Yes, relay on key management by SQL. User then need a rights to open key, cert.
Unfortunately, there is no "Best practices" from microsoft for SQL Encryption. The encryption and connected with that security is a very complex theme.
January 18, 2013 at 8:21 am
Thanks for the reply, e4d4:
Some further questions, if I may; if I understand correctly, to allow SQL server's Key management to work, the user would need CONTROL permission on the cert, and REFERENCE permission on the symmetric key. Am I correct in that?
What dangers are inherent in granting CONTROL permission to a database role? Is the potential loss of security in this area worth the gain from encrypting data? And is granting CONTROL permission to a database role any more/less secure than placing the password inside each sproc?
You are spot on when you say there really aren't any best practices. It has taken me two solid days of research to get just this far. Your input is appreciated.
Kurt.
January 18, 2013 at 11:09 am
Our DBA has assigned my user account ALL permissions on the certificate, and reference permission on the symmetric key, but when I run this code:
USE AdventureWorks;
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCert;
UPDATE
Sales.CreditCard_ENCRYPTION
SET
CardNumbENC = EncryptBykey(Key_GUID('TestSymmetricKey'), CardNumber);
SELECT
CardNumber
, CardNumbENC
FROM
Sales.CreditCard_ENCRYPTION;
... gets me this error:
Msg 15334, Level 16, State 1, Line 20
The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.
If, as mentioned above, CONTROL permission on the cert, and REFERENCE permission on the symmetric key, should allow me to access SQL Server Automatic Key Management to avoid entering the cert password, what went wrong?
Ugh. :crazy:
January 18, 2013 at 1:26 pm
UGH! Enbarrased to admit that we are actually running SS2k5, NOT 2k8! i pulled the wrong version number. Management Studio tools are 2008, however. I hope posting this in this forum hasn't bolloxed any help I can still get.
Additionally, I found out a bit more about why it keeps asking for the cert password. Apparently NONE of the securables being assigned to my account are saving. No matter how many times the DBA assigns the cert and symmetric keys to my account as secutables, and assigns permissions to those, they are gone the next time we look. There are NO errors while saving I should add.
Has anyone seent his issue? It might explain my last issue above.
Thanks.
Kurt.
January 21, 2013 at 12:59 am
kpwimberger (1/18/2013)
Our DBA has assigned my user account ALL permissions on the certificate, and reference permission on the symmetric key, but when I run this code:
USE AdventureWorks;OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCert;
UPDATE
Sales.CreditCard_ENCRYPTION
SET
CardNumbENC = EncryptBykey(Key_GUID('TestSymmetricKey'), CardNumber);
SELECT
CardNumber
, CardNumbENC
FROM
Sales.CreditCard_ENCRYPTION;
... gets me this error:
Msg 15334, Level 16, State 1, Line 20
The certificate has a private key that is protected by a user defined password. That password needs to be provided to enable the use of the private key.
If, as mentioned above, CONTROL permission on the cert, and REFERENCE permission on the symmetric key, should allow me to access SQL Server Automatic Key Management to avoid entering the cert password, what went wrong?
Ugh. :crazy:
If TestCert is protected with a password you must provide the password to use it, you can check protection type in a view
select * from sys.certificates
CREATE CERTIFICATE CertTest WITH SUBJECT = 'Certificate for test purpose only'
go
CREATE SYMMETRIC KEY TestKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE CertTest
go
select pvt_key_encryption_type_desc from sys.certificates where name='CertTest'
--now cert is protected by DMK and you don't need to pass any password do open it
OPEN SYMMETRIC KEY TestKey DECRYPTION
BY CERTIFICATE CertTest
--close SYMMETRIC KEY
CLOSE SYMMETRIC KEY TestKey
--set CertTest protected by password=pass
alter certificate CertTest WITH PRIVATE KEY(ENCRYPTION BY PASSWORD = 'pass' )
select pvt_key_encryption_type_desc from sys.certificates where name='CertTest'
--now cert is protected by password and you must provide valid password
OPEN SYMMETRIC KEY TestKey DECRYPTION
BY CERTIFICATE CertTest with password='pass'
CLOSE SYMMETRIC KEY TestKey
--set CertTest protection to DMK (cert protected with password)
alter certificate CertTest WITH PRIVATE KEY(DECRYPTION BY PASSWORD = 'pass' )
CLOSE SYMMETRIC KEY TestKey
drop SYMMETRIC KEY TestKey
drop certificate CertTest
January 21, 2013 at 8:35 am
kpwimberger (1/17/2013)
But doesn't this mean I need the password inside EVERY proc I write that touches an encrypted column? isn't this adding insecurity into the system? What if the password needs to change, it will be scattered throughout the system.Iam sure there must be a way to do this without explicitly calling the password.
e4d4 is on the right track, can you post the code you used to create the certificate?
If you specify encrypt by password the password must be specified each and every time you open the cert. Omit the encryption by password and its automatically opened by the DMK
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2013 at 9:52 am
Thanks e4d4 and Perry:
It seems that was the missing ingredient: I HAD been encrypting the cert via password. When I leave off the password I then can access the cert and symmetric key without it. Of course, this is in SSMS and I am DBO on the database, so now it's time to test a field user to see what we can see.
I'll post results when I have them, as I still do not know whether or not a standard user needs CONTROL permission against the cert and REFERENCE permission against the key.
Kurt
PS -the current code:
CREATE CERTIFICATE TestCert2
--ENCRYPTION BY PASSWORD = 'thisIsAP@$$w0rd'
WITH SUBJECT = 'Test certificate'
, START_DATE = '1/17/2013'
, EXPIRY_DATE = '03/26/2013';
OPEN SYMMETRIC KEY TestSymmetricKey2
DECRYPTION BY CERTIFICATE TestCert2 ;
--WITH PASSWORD = 'thisIsAP@$$w0rd';
UPDATE Sales.CreditCard_ENCRYPTION
SET CardNumbENC = EncryptBykey(Key_GUID('TestSymmetricKey2'), CardNumber);
SELECT
CardNumber
, CardNumbENC
, 'Decrypt' = CAST(DecryptByKey(CardNumbENC) AS nvarchar(50))
FROM
Sales.CreditCard_ENCRYPTION;
January 23, 2013 at 3:00 am
According to what I found on the internet and tests, the lowest permission is CONTROL on a certificate and only REFERENCES on a symetric key. Then user can't drop or modify the certifacte and key but still is able to turn off keys management on the certificate and set a password, but maybe I missed something.
"Overview of encryption and types of keys, the key hierarchy, key management, encrypting data, encrypting a database (TDE) and Extensible Key Management (EKM)."
January 23, 2013 at 8:14 am
e4d4:
That is exactly what I have read as well. I have built a basic proof of concept form and it works for me, but I'm DBO. Today I test with a standard user and I expect it to fail as their database role does not, as of now, have those permissions. I will have our DBA add in the permissions one at a time and see what happens.
I have to admit to not being very comfortable giving Control permissions to an entire database role, but that's how we are handling permissions.
I will post results later today. Thanks again for your valuable help!
Kurt
January 25, 2013 at 8:32 am
Success!
Thanks to you both for your input. Giving the database role CONTROL permission on the cert, and REFERENCE permission on the key, worked perfectly. We are good to go.
But I'd like to throw out a high-level follow up question to everyone:
We encrypt data in a database so that if anyone should backup/copy/steal the table the data inside is unusable. But if that person has sufficient server rights to be able to run a backup, or to copy a table, wouldn't that mean they have a high enough permission set to decrypt the data using the cert and key?
I guess I cannot immediately think of a scenario where someone could get access to the entire table but not have enough permissions to decrypt the data. I suppose if a web user somehow knew the schema and submitted a SELECT * FROM tblTheTable they might get all the data, but we deny our web users any permissions they are not supposed to have.
Just wondering...
January 28, 2013 at 3:53 am
kpwimberger (1/25/2013)
Success!Thanks to you both for your input. Giving the database role CONTROL permission on the cert, and REFERENCE permission on the key, worked perfectly. We are good to go.
But I'd like to throw out a high-level follow up question to everyone:
We encrypt data in a database so that if anyone should backup/copy/steal the table the data inside is unusable. But if that person has sufficient server rights to be able to run a backup, or to copy a table, wouldn't that mean they have a high enough permission set to decrypt the data using the cert and key?
If someone steals the backup file or mdf file, without Database Master Key he can't decrypt the data. You also should remember that if you want to restore the db on another instance you must restore DMK. And the DMK should be backuped and protected with a password and proper access to that file. If someone has select to a table where are encrypted columns, still he need permission to key to decrypt the data. You should thinking of both an encryption and permissions.
kpwimberger (1/25/2013)
I guess I cannot immediately think of a scenario where someone could get access to the entire table but not have enough permissions to decrypt the data. I suppose if a web user somehow knew the schema and submitted a SELECT * FROM tblTheTable they might get all the data, but we deny our web users any permissions they are not supposed to have.Just wondering...
I don't know how big will be that web application but remember that encryption on sql level isn't easy scalable, with www maybe you should start thinking about encryption on another application tier.
January 28, 2013 at 8:32 am
Thanks e4d4:
What confuses me is - how likely is it that someone can get all the way into the server to create or steal a backup and NOT have access to the DMK? If someone is already in that deep, it seems we have much bigger issues on our hands. Honestly, in some ways encryption seems to be more trouble than it is worth.
Yes, we are going to have to backup the DMK, that's for certain!
Thanks
Kurt
January 28, 2013 at 4:20 pm
kpwimberger (1/28/2013)
Thanks e4d4:What confuses me is - how likely is it that someone can get all the way into the server to create or steal a backup and NOT have access to the DMK? If someone is already in that deep, it seems we have much bigger issues on our hands. Honestly, in some ways encryption seems to be more trouble than it is worth.
Well, perhaps they picked up the wrong (right) tapes from an offsite storage facility?
Maybe someone put backups in "the cloud" and someone else in "the cloud" made copies?
The trick in security is to make things harder for an adversary at every level.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply