Sql-server – SQL Server symmetric key & certificates games by Hacker

Securitysql server

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 ?

Best Answer

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.
Related Question