Encrypt an existing database in SQL Server 2005

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Encrypt an existing database in SQL Server 2005

Author

Topic

dreamer_0244
Starting Member

7 Posts

Posted - 2007-12-03 : 00:33:06

Hi! I have an existing database in SQL Server 2005 and I would like to apply encryption on my database. Does anyone have an idea on this?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-12-03 : 02:45:03

You can encrypt only some columns
You can not encrypt the whole database
Encrypted columns can not be indexed, if they are indexed, indexes are useless
So you can not search by encrypted columns
So in general, encryption results in a severe performance penalty, so you should carefully chose what to enrypt and what to leave as is.

dreamer_0244
Starting Member

7 Posts

Posted - 2007-12-04 : 03:59:14

I dont intend to encrypt the columns but only the connection? Is is possible? If yes, how?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 04:11:46

Why can't encrypted columns be indexed?

If you search, you search be encrypted value against the encrypted column. Same thing, you only have to remember to encrypt the parameter to search by first.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 04:13:10

Encrypt a connection?
See http://www.connectionstrings.com

quote:
Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

E 12°55'05.25"
N 56°04'39.16"

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-12-04 : 04:15:56

Peso, because encryption is not deteministic:

select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')
select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')
select EncryptByAsymKey(AsymKey_ID('AsymKey'), 'This is my example')

Results (truncated) ...
0x9FD1DDA8A6ACED67C0C24CDD566CAD655E7B7E3D76197896…
0xE18FFFB9EC75CD2093089A5DDB83220A244346F77AA548BF…
0x98D563BA855573A442A278B2565D9216192AD5BC7B664637…

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 04:22:19

Isn't that assumption based on what encryption method being used?

E 12°55'05.25"
N 56°04'39.16"

dreamer_0244
Starting Member

7 Posts

Posted - 2007-12-04 : 04:25:08

Is that all I have to do? Are there any requirements before I can encrypt the connection?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources