Encryption doubts

Question

Encryption doubts

IT researcher

SSCertifiable

Points: 7602
More actions
March 12, 2013 at 2:21 am

#290867

I want to encrypt my database. So which method is simple and the best. If i use symmetric key then does i encrypt it by using database master key or password? Which is safe here?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why are you encrypting? What are you protecting against?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IT researcher SSCertifiable Points: 7602 More actions · Answer 2

IT researcher

SSCertifiable

Points: 7602

March 12, 2013 at 3:52 am

#1595874

I want to protect some column which are sensitive.

JohnMagnabosco SSC Veteran Points: 232 More actions · Answer 3

Please check out the following book - it will help you in this issue:

https://www.simple-talk.com/books/sql-books/protecting-sql-server-data/

Please note that the link provides a means to download a free pdf version of this book. Also, feel free to peruse my past blog posts on the subject of encryption:

https://www.simple-talk.com/blogs/author/13712-john-magnabosco/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Who are you protecting the data against?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IT researcher SSCertifiable Points: 7602 More actions · Answer 5

I just had look at this [/url] nice article.

Here symmetric key encrypted using certificate. There is also option to encrypt it by using password. So which one is better?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

winmansoft (3/12/2013)
I just had look at this [/url] nice article.
Here symmetric key encrypted using certificate. There is also option to encrypt it by using password. So which one is better?

It depends on what you are trying to protect against, or more to the point, who and in what scenarios you are worried about your data being stolen. For example, stolen tape backup, stolen data from developers who have db_datareader access, etc. Please tell us, and then we can advise you on encryption options.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

IT researcher SSCertifiable Points: 7602 More actions · Answer 7

If a user who has access to the database with admin permission then he can take backup and restore database anywhere. So can i protect the database from being stolen? I want only some columns to be always encrypted.I think if i encrypt the those column by symmetric key with certificate then anyone can easily decrypt it by using the symmetric key and certificate. So if i use symmetric key with password security then i think anyone cannot decrypt it without knowing the password. If i am wrong please correct it.Also which is the best and easiest encryption for my scenario?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

winmansoft (3/14/2013)
If a user who has access to the database with admin permission then he can take backup and restore database anywhere.

Correct

So can i protect the database from being stolen?

By an admin, you can't. An admin has complete control can do anything to the database.

If you're trying to protect against someone who has local admin or sysadmin access, you need to do the encrypting outside of SQL Server, which introduces all the fun of how and where you protect the keys.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IT researcher SSCertifiable Points: 7602 More actions · Answer 9

What if i use ENCRYPTBYPASSPHRASE to encrypt selected colums? How anyone with admin permission can decrypt it without knowing passphrase?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

How does the passphrase get entered to unencrypt the data?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

winmansoft (3/14/2013)
What if i use ENCRYPTBYPASSPHRASE to encrypt selected colums? How anyone with admin permission can decrypt it without knowing passphrase?

If the passphrase is passed to a stored procedure, trivially. If it's dynamic SQL from the app, it's a lot harder, but not impossible.

Also worth noting that the encryptbypassphrase creates a certificate to do the actual encryption with, and that uses one of the lowest strength encryption methods.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

e4d4 SSCertifiable Points: 5777 More actions · Answer 12

You can protect keys by using a password.

Check this "SQL Server Encryption To Block DBAs Data Access" http://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-access/