Encrypting data in SQL 2005

I have a pretty good understanding of how encryption works in 2005 but
what I am looking for is a real world example of how it has it has been
done. Here is what I am looking for, hopefully someone can point me in
the right direction.
Let's say that I have a table with one or more encrypted columns that
are encrypted by symmetric Key1. I want only authorized users to have
the ability to decrypt the data. I don't want DBA's or the DBO or the
developers to be able to do this.
I would think I would have to create Key1 using a password. But then
how do I protect that password to make sure the DBA's or developers
don't get hold of it.
My thought is that I could install something on the web server (a
encrypted password or certificate or something) that only the
application on that box could access, and then that is used to open
Key1 and decrypt the data.
Any thoughts or suggestions?
Just to clarify,the reason I would want to protect the data from the
DBA's is due to client requirements.
Thanks!
Jim"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147899954.577587.108190@.i40g2000cwc.googlegroups.com...
>I have a pretty good understanding of how encryption works in 2005 but
> what I am looking for is a real world example of how it has it has been
> done. Here is what I am looking for, hopefully someone can point me in
> the right direction.
> Let's say that I have a table with one or more encrypted columns that
> are encrypted by symmetric Key1. I want only authorized users to have
> the ability to decrypt the data. I don't want DBA's or the DBO or the
> developers to be able to do this.
> I would think I would have to create Key1 using a password. But then
> how do I protect that password to make sure the DBA's or developers
> don't get hold of it.
> My thought is that I could install something on the web server (a
> encrypted password or certificate or something) that only the
> application on that box could access, and then that is used to open
> Key1 and decrypt the data.
> Any thoughts or suggestions?
> Just to clarify,the reason I would want to protect the data from the
> DBA's is due to client requirements.
>
Take a look at this blog entry
How Secure Is Your Data?
http://blogs.msdn.com/yukondoit/articles/480854.aspx
David|||I had read that but it does not answer the question of how to protect
the password. I don't want to code it into my stored procedure becase
then someone (a developer or DBA) would be able to look at the code and
then see it.
What I need is a suggestion for a system that allows the application to
get to the password but have that access to the password limited to the
app and the security group that put it on the box.
Any thoughts'
Thanks!
Jim|||If the admin really want to see your data there not a lot you can do about
it. The admin doesn't have to know the password because he can change it.
One of the SQL devs discusses this in his blog:
http://blogs.msdn.com/lcris/archive.../20/506187.aspx If you think
about it, you don't want to be able to block the admin. What if a
disgruntled employee decided to change a password only he could change and
you lost access to all the payroll data in the database? The best way to
limit access is to make sure you limit admin rights to people you can trust
because ultimately you have to trust somebody with the passwords. If you
decide only your CEO should have that power, I give you Enron. The only way
I can think of to block an administrator is to do the encryption and
decryption in your application so the admin has no access to the key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147959831.765199.107920@.i39g2000cwa.googlegroups.com...
>I had read that but it does not answer the question of how to protect
> the password. I don't want to code it into my stored procedure becase
> then someone (a developer or DBA) would be able to look at the code and
> then see it.
> What I need is a suggestion for a system that allows the application to
> get to the password but have that access to the password limited to the
> app and the security group that put it on the box.
> Any thoughts'
> Thanks!
> Jim
>|||I agree. And we have a security group that would be in charge of the
password or key or whatever. My point is, I am looking for a system
that would work for what I have described.
What I would like to do is to have a cert of some type installed on the
web server that no one but the machine admin and the security group
would have access to. This cert would then be used by the web
application to access the key and decrypt the data. If a lead
developer or architect would need that access, then they would request
it through the proper channels with the security admin group and have
it installed on their machine in the same way.
I am not to concerned about the DBA or Admin hacking the box. This is
mostly to comply with a client security request. Only X people have
access to the sensitive data on the database.
Jim|||Why not have the application request the password when the authorized user
logs in to the database. The application can then open the key using that
password and use it for encryption/decryption. Finally, when the user
disconnects, the key should be closed. The key password would be needed in
addition to the credentials required for connecting to the database. As you
are not hardcoding it anywhere, a dbo would not be able to get to it.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147959831.765199.107920@.i39g2000cwa.googlegroups.com...
>I had read that but it does not answer the question of how to protect
> the password. I don't want to code it into my stored procedure becase
> then someone (a developer or DBA) would be able to look at the code and
> then see it.
> What I need is a suggestion for a system that allows the application to
> get to the password but have that access to the password limited to the
> app and the security group that put it on the box.
> Any thoughts'
> Thanks!
> Jim
>|||The application is a web app for checking on personal information
(think www.citibank.com). The app would need to decrypt private data
(like ssn or CC# or whatever) for display to the user. The user would
not have access (or knowledge) of the encryption method or anything
else.
Am I going down the wrong track here? Maybe I need to look at this
from a different angle?
Jim|||"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1147976240.393623.190210@.j73g2000cwa.googlegroups.com...
> The application is a web app for checking on personal information
> (think www.citibank.com). The app would need to decrypt private data
> (like ssn or CC# or whatever) for display to the user. The user would
> not have access (or knowledge) of the encryption method or anything
> else.
> Am I going down the wrong track here? Maybe I need to look at this
> from a different angle?
>
You can store the password in your application's configuration. Then secure
that using the OS.
There is specific tooling to make this easy in asp.net 2.0 applications.
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
http://msdn.microsoft.com/library/d... />
000005.asp
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA
http://msdn.microsoft.com/library/d... />
000006.asp
Otherwise you need to use the crypto API stuff to put the password (or a key
to encrypt it) in the OS secure store.
Davidsql