April 9, 2009 at 11:25 am
In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:
In practice, Certificate objects are always recommended over AsymmetricKey objects
Can anyone please provide a justification as to why that should be so?
Is it because of increased security, performance or both?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2009 at 11:44 am
Marios Philippopoulos (4/9/2009)
In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:In practice, Certificate objects are always recommended over AsymmetricKey objects
Can anyone please provide a justification as to why that should be so?
Is it because of increased security, performance or both?
From the same article:
There is little difference between a SQL Server certificate and a SQL Server asymmetric key; the primary difference is that the asymmetric key cannot be exported. For this reason, they are treated as the same thing in this discussion. Create Certificate objects unless you have a reason to prefer an asymmetric key.
That's why. You have a recovery option with certificates that you don't with asymmetric keys.
K. Brian Kelley
@kbriankelley
April 9, 2009 at 11:50 am
K. Brian Kelley (4/9/2009)
Marios Philippopoulos (4/9/2009)
In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:In practice, Certificate objects are always recommended over AsymmetricKey objects
Can anyone please provide a justification as to why that should be so?
Is it because of increased security, performance or both?
From the same article:
There is little difference between a SQL Server certificate and a SQL Server asymmetric key; the primary difference is that the asymmetric key cannot be exported. For this reason, they are treated as the same thing in this discussion. Create Certificate objects unless you have a reason to prefer an asymmetric key.
That's why. You have a recovery option with certificates that you don't with asymmetric keys.
Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2009 at 12:10 pm
Marios Philippopoulos (4/9/2009)
Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?
Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't.
K. Brian Kelley
@kbriankelley
April 9, 2009 at 12:16 pm
K. Brian Kelley (4/9/2009)
Marios Philippopoulos (4/9/2009)
Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?
Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't.
Makes sense, thanks for explaining this.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2009 at 12:18 pm
What do the other 2 letters stand for in C-I-A?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2009 at 12:25 pm
Confidentiality - Ensuring only authorized personnel/services can see the data.
Integrity - Ensuring only authorized personnel/services can modify the data through authorized processes.
K. Brian Kelley
@kbriankelley
April 14, 2009 at 3:53 pm
So I have backed up my certificate (certificate protecting the private key of a symmetric key through which I en(de)crypt data):
use encryption_test;
BACKUP CERTIFICATE ct_data_access
TO FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access'
WITH PRIVATE KEY
(
FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access_key'
,ENCRYPTION BY PASSWORD = '%T^Y&U*IF$#@GT%');
The symmetric key is related to the certificate like this:
CREATE SYMMETRIC KEY
sk_data_access
WITH
ALGORITHM = AES_256
ENCRYPTION BY
CERTIFICATE ct_data_access
GO
I then restore it on another db on the same instance:
USE encr_restore;
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'b^d$H^%$#kofdYUH&^%';
END
ELSE
BEGIN
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'b^d$H^%$#kofdYUH&^%';
END
CREATE CERTIFICATE ct_data_access2
FROM FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access'
WITH PRIVATE KEY
(
FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access_key'
,DECRYPTION BY PASSWORD = '%T^Y&U*IF$#@GT%');
Now I'm not sure what to do next.
How can I retrieve the symmetric key (protected by the certificate) in the new database?
I'm not sure what I have achieved by backing up the certificate...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 24, 2023 at 5:25 am
"Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't."
That all depends on how you integrate your Asymmetric key.
As stated Certs are less secure, but Keys are less available, however, if your Asymetric key is in a KMS/HSM, and has things like Quorum (Zero Trust Methodology to prevent a key from being deleted on purpose, accident or maliciously, a key undo or soft delete policy, and multiple points of failure and high availability then your Asymetric approach becomes more secure, more available, if not more so since Certs need to be backed up or master DB needs to be backed up and restored. Plus, depending on how you implement it could also add RBAC access to the key, and logging of who used the key, where is the cert there is no means to track or limit who uses it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply