Encrypting a DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Encrypting a DB

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-19 : 10:28:36

i read http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx
....where is it asking for the password given when creating the master key when decrypting?
It sounds to me like if anyone run SELECT * FROM sys.symmetric_keys
he can verywell find the key and decrypt?
help me understand what i am missing.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-19 : 10:40:03

I do not see anything in that article that says you can find the key and decrypt it using that query.

CODO ERGO SUM

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-19 : 10:52:08

i am not blaming the article... but i am not understanding something.

you can decrypt using
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

SELECT
EmailAddress,
EncryptedEmailAddress,
CONVERT(nvarchar, DecryptByKey(EncryptedEmailAddress)) AS 'Decrypted Email Address'
FROM Person.Contact;
GO

for which you can get the key and certificate by
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates

so what is the point in encrypting? Only admins will be able to run the sys queries?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-19 : 12:08:17

That article does not show the "authorization" key word that is used to specify the user that will have access to use the key.

For example, you can authorize DBO to use the key, and then DBO stored procedures could use the key, but ordinary users would not be able to use it, as long as they are not in the DB_OWNER role.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources