Encryption in SQL Server 2005

[vbkris]

hi,

my doubt is regarding protecting the password given to a key in SQL Server 2005. The password must be given in clear text format when decrypting the data.

How does one go about doing it?

In my current application I have to use ONLY SPs. therefore i am forced to pass the password as a parameter to the SP (so that encryption and decryption can happen).

This info is clearly getting recorded in the profiler!

What is the best way to go about doing this?

Known is handfull, Unknown is worldfull

 

[jbenson001]

Humm interesting question. I am using Simple Symmetric Encryption in my app, so I am not passing a password I am only using the certficate name. I guess you are using a more complicated encryption schema. Is passing the PW necessary? Can it be hard coded in the SP or is that a security issue?

Jim

 

[vbkris]

>> Can it be hard coded in the SP or is that a security issue?


yes it is, unfortunately if the hacker gets access to the DB there might be a problem. this being an ASP.NET application the chances are high.

the "hackers" include the programmers who are working on the project itself. since they have access to the connection string, i am assumung that they COULD get access to the DB. another side is someone directly hacking into the system.

anybody who does the above have to simply pass an sql stmt like this:

select EncryptByXXX(certificate,column) from Table

thats it!

therefore the password was required. now I have this issue...

Known is handfull, Unknown is worldfull

 

[vbkris]

and one more thing.

is the default architecture of 2005 in such a way that encryption is protected ONLY by restricting access to DB objects like certificates and keys etc?

how does one implement this safely in a web project, especially in my scenario???

Known is handfull, Unknown is worldfull

 

[vbkris]

Hi Jim,

Any updates? am i correct in the assumptions that I state above? or am i missing an obvious point (which it usally is)???

any input is appreciated...

Known is handfull, Unknown is worldfull

 

[jbenson001]

Hi sorry I haven't gotten back to you.

You bring up some very interesting points which are quite valid I belive, and also brought them to light for me. I wish I had some good answers and advice for you, but I just recently started using encryption for a project, so this is my first time.
There are some very complex scenarios than can be accomplished with the built in encryption, but from the web side, I see where security for PWs and certificats can become an issue.
I am hoping some some the SQL Gurus on here can help shed some light on this for the both of us. Since built in encryption is new to this version, we may need to wait for a bit for some answers.
I will keep searching for some good answers, and if you find any, please post.

Jim

 

[vbkris]

thanks for your reply dude. will keep ooking for the answer and get back to you guys...

Known is handfull, Unknown is worldfull

 

[jbenson001]

Ok.. I will let you know if I find something as well...

 

[vbkris]

sure, this seems to be a very interesting topic, i am just surprised that this problem did not arise for many, but again i might be jumping to conclusions...



Known is handfull, Unknown is worldfull

 

[jbenson001]

Well it could be that since encryption is new with 2005, not too many people have implemented it yet. I have found lots of info on the web, but nothing yet pertaining to a GUI.

 

[bikerboy718]

I know that one thing that my co-worker ans I have discussed is a way to encrypt from the front end and pass the password with the encryption key type to the database so that furhter processing can be done on the back end. We have not yet revisited the topic but we did bring up interestng questions. By passing the key type and the encrypted password and encrypting it with xml it should be enough to not get hit by profiler in theory. Just an idea.

With Great Power Comes Great Responsibility!!!

Michael

 

[vbkris]

>>By passing the key type and the encrypted password and encrypting it with xml it should be enough to not get hit by profiler in theory

point, but this means that the password must be hardcoded in the front end. this will be a very cyclical issue.

would be greatfull for an example...

Known is handfull, Unknown is worldfull

 

[bikerboy718]

We are still working on the concept. And we would not actually pass the key type but more of a key type representation so no one else knew the exact type itself. We have yet to revisit it but once we do I will post a faq example.

With Great Power Comes Great Responsibility!!!

Michael

 

[vbkris]

thanks dude...

Known is handfull, Unknown is worldfull

 

[mrdenny]

There is no need to pass a password to use the encryption. If you secure your certificates by using the database master key. The cert is then used to sign the symmetric key.

There is an FAQ in forum962 faq962-5964 that gives an example of using SQL 2005 Encryption. In the FAQ I happen to use a password, but the master key can easily be used. There is info in BOL on using the master key under CREATE MASTER KEY and CREATE CERTIFICATE.

I use this method in a couple of production systems at the office where data encryption was required.

To ensure that the developers can't connect via the application account the developers aren't allow on the production systems and therefor can not see the connection string to the production systems. We also use different passwords in Dev / QA / and Production.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

>>To ensure that the developers can't connect via the application account the developers aren't allow on the production systems and therefor can not see the connection string to the production systems.


Who controls this activity? My problem is that even THAT person can hack into the system.

Let me explain the project a bit.

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

Therefore the employees are the potential hackers (including the ones who take the build). It is one of the requirement that once the user (who uses the system) chooses the password it must be closed even to the programmer who deploys them.

Therefore this need...

Known is handfull, Unknown is worldfull

 

[mrdenny]

Have the application connect to the database using Windows Authentication.

We run under the assumption that our DBAs and System Admins can be trusted.

Keep in mind that the DBAs can use there high level access to decrypt the data, even with out the password. All it would take is time.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

>>Have the application connect to the database using Windows Authentication.

But that wont solve anything right? its not the connection string thats the problem, its how i send the password to the SQL Server that is.

>>We run under the assumption that our DBAs and System Admins can be trusted.

Me too, unfortunately the client doesnt feel the same


>>Keep in mind that the DBAs can use there high level access to decrypt the data, even with out the password. All it would take is time.

exactly, which was what I was trying to avoid using the password (make it much more difficult)...

Known is handfull, Unknown is worldfull

 

[mrdenny]

If you use a certificate that is encrypted by the master key the DBAs can then use the certificate to decrypt the data easily.

If the application is connecting to the database server with Windows Authentication the account's password isn't in the connection string.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

>>If you use a certificate that is encrypted by the master key the DBAs can then use the certificate to decrypt the data easily.

OK. Point taken. But I mean to use Keys in this scenario, will this apply to keys also?

>>If the application is connecting to the database server with Windows Authentication the account's password isn't in the connection string.

hmm, let me check into this (need to know if its feasible in this project)...

Known is handfull, Unknown is worldfull