Sql-server – SYMMETRIC key & certificates role in sql server

certificateSecuritysql 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) 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 have 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 protedtion in decypher data from the Hacker ?

3)

When im using

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


DECLARE  @str NVARCHAR(100)
SET @str = 'lala';

OPEN SYMMETRIC KEY SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongPassword';

Whom am I trying to protect the data from ?
the data being sent from client to server ? ( the data is being sent by plain text – I can't activate SQL commands before sending the data…) or people who has access to the SQL server?

Best Answer

You are protecting agains accidental media loss (a lost laptop with the database, your drive showing up on a flea market with a copy of the database or of a backup) etc etc. Any scheme in which the processing itself (the database engine or the application) requires to access the data without the user providing a password only offer access agains media loss. The encryption key hierarchy is rooted on the system DPAPI encrypted key, in other words on the password of the service account. Such a scenario never protects agains a hacker that gets access to your SQL Server, and is not meant to protect such.

The alternative is to ask the user for a password each time it uses the application and use this password to open the key at the top of the encryption key hierarchy (usually a certificate in the database). This scheme is very seldom deployed in such cases as some multi-tenant scenarios when the tenants do no trust the hosting operations/administrators.

Related Question