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 ive executed the code above , is there any way to find out later what is the passowrd value for the SecureSymmetricKey ?

2)

If now im doing this with Certificates : Im ( 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 itrying to protect the data from ? the data being sent from client to server ? ( the data is being sent by plaing text - i cant activate sql commands before sending the data...) or people who has access to the sql server?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

The protection is that if someone gets a copy of this database (via stealing the backup, doing a SAN snapshot of the user databases, stealing the hard drives of the user databases, etc) and tries to attach 'em to another SQL Server, they won't be able to do it.

It doesn't protect everything - it's only one part of the picture. For more info, check out the excellent book Securing SQL Server by Denny Cherry:

http://www.amazon.com/Securing-SQL-Server-Protecting-Attackers/dp/1597496251/ref=sr_1_1?ie=UTF8&qid=1325431386&sr=8-1

share|improve this answer
    
Second edition of Cherry's book: amazon.com/Securing-SQL-Server-Second-Protecting/dp/1597499471/… –  BrianCooksey Jan 9 at 17:11
add comment

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.