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.
| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2007-09-20 : 10:17:25
|
| Does anyone know of a good article on encryption key management in SQL Server 2005?I have seen some basic articles on the subject, but I an interested in hearing about best real-world practices for production systems.I would also like to hear what anyone here has to say about their own experiences with key management in SQL 2005.CODO ERGO SUM |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-20 : 10:26:16
|
| any specific stuff you want to know?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-20 : 10:44:02
|
quote:
Originally posted by spirit1
any specific stuff you want to know?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp
Mainly how to handle keys securely without having to grant end users excessive permissions that would let anyone decrypt data, and without storing keys or passwords in procedures or functions.Really, I'm looking for a good plan on how to design key security, so that we don't just start "doing stuff" and then realize the faults after the systems is developed.CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-20 : 11:04:34
|
| maybe i don't quite understand what you mean but symmetric an asymmetric keys are stored in sys.asymmetric_keys and sys.symmetric_keys.usuall you encrypt data with a symetric key, and then encrypt the symmetrickey with the asymmetric key.Matija has quite some info on the subject of encryption:http://milambda.blogspot.com/search?q=encrypt_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-09-21 : 16:31:18
|
| Michael,The best design that I have seen is to do the following.1. Create a login from certificate and make it a user in the database where the functions are going to live. 2. Create a certificate / keys as necessary for your encrption / decryption requirements.3. Grant permissions on the certificates / keys to the user created above4. Change the execution context of the functions / views that need decryption to the user above.Hope this helps.Thanks,Rich |
|
|
|
|
|
|
Encryption Key Management