Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Encryption basics. Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 12:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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
Post #1408571
Posted Thursday, January 17, 2013 3:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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:
[color=#FF0000]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.[/color]
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.


Post #1408634
Posted Friday, January 18, 2013 1:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 227, Visits: 1,782
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.
Post #1408749
Posted Friday, January 18, 2013 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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.
Post #1408938
Posted Friday, January 18, 2013 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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.
Post #1409026
Posted Friday, January 18, 2013 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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.
Post #1409079
Posted Monday, January 21, 2013 12:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 227, Visits: 1,782
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.


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

Post #1409387
Posted Monday, January 21, 2013 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:20 AM
Points: 6,752, Visits: 14,400
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"
Post #1409586
Posted Tuesday, January 22, 2013 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 4:35 PM
Points: 33, Visits: 100
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;




Post #1410134
Posted Wednesday, January 23, 2013 3:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 227, Visits: 1,782
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)."
http://technet.microsoft.com/en-US/sqlserver/gg429824.aspx
Post #1410432
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse