Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

I'm trying to figure out about the SQL Server encryption.

  • once I've executed the code above, is there any way to find out later what is the password value for the SecureSymmetricKey ?

2)

If now I'm doing this with Certificates : I`m the admin and created:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

only I know the password.

later I :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

until now , Its all ok.

Now, when a hacker comes to the computer, all he has to do is :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

so where is the protection in certificates? No one asked him for a password (like in password encryption (as in my first question)...? He only needed to know the certificate name

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and its not a problem to find out what is the certificate name ...So where is the protection in decypher data from the hacker ?

share|improve this question
    
I am really not sure what your question is exactly ? –  Yahia Oct 20 '11 at 18:59
    
@Yahia : step by step : lets talk about 1) once the admin executed these lines. Is there any command that he would be able to find what is the passowrd which was used to encrypt ? ( cause a hacker can write : OPEN SYMMETRIC KEY SecureSymmetricKey DECRYPTION BY PASSWORD = N'StrongPassword'; and then see the data.... –  Royi Namir Oct 20 '11 at 19:02
    
you write "a hacker can..." - security is a multi-level process and these measures you describe are only one part of many... and when one wants to check security one needs some scenario for that... you neither provide a scenario nor a security architecture... thus making it impossible to a give a real answer... –  Yahia Oct 20 '11 at 19:05
    
@Yahia , what about if hacker got my sql db into his hands (this is the scenario)...Is there any command that he would be able to find what is the passowrd which was used to encrypt ?? –  Royi Namir Oct 20 '11 at 19:20
    
A good tutorial on SQL Server encryption: blog.sqlauthority.com/2009/04/28/… –  Rick Oct 20 '11 at 19:38
show 2 more comments

migrated from stackoverflow.com Oct 25 '11 at 13:10

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

The purpose of a certificate in SQL Server is to protect the symmetric keys, but it's more for protecting your offline data: .mdb files and backups. It's not really designed for preventing access to the data once an attacker has access to the running SQL instance (with enough permission to access the data and run decryption.)

  • If you use a password for your symmetric key, then someone who knows or can guess the password can take a copy of your data and load and decrypt it.
  • If you use a certificate, then the attacker needs a copy of the data, and a backup of the certificate, and the password for the certificate.
share|improve this answer
    
listen man you are awesome . question : for the code CREATE SYMMETRIC KEY SecureSymmetricKey WITH ALGORITHM = DESX ENCRYPTION BY PASSWORD = N'StrongPassword'; , is there any way by sql select command to see the N'StrongPassword' from SecureSymmetricKey ? –  Royi Namir Oct 20 '11 at 19:35
    
I don't think so; I think it's hashed with a seed, so even attacks with a rainbow table against the hash would be difficult. I can't find the BOL reference right now, but that's always been my understanding (and it wouldn't make much sense to store it plaintext!) –  Rick Oct 20 '11 at 19:37
    
I wish to thank you a lot. ( if i'll have more advanced sql question i'll ask you directly :) ) . thanks. –  Royi Namir Oct 20 '11 at 19:38
    
but If I used certificate , The hacker can see wheich certificates are installed , and by the code : OPEN SYMMETRIC KEY MySymetricKey DECRYPTION BY CERTIFICATE MyCertificate he can see the real data... –  Royi Namir Oct 20 '11 at 19:40
1  
Yes, but he would need access to the running SQL instance, using an account that has permission to query the data and that has permission to run the decryption. If you use Windows security (e.g.: Active Directory groups) and grant minimal permission to the accounts, you should keep your attack surface fairly small. –  Rick Oct 20 '11 at 19:47
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.