Encryption using MS SQL 2005

Hello,

I have a application server with about 500,000 users. We are trying to tacle the issue of encryption. We are using MS SQL 2005 and I am sure that symmetric encryption would be the best, due to speed. But heres the kicker.....We want the whole database encrypted at rest, and when clients log onto our ASP to gain access to their programms the data must be in plain text. Any sugesstions?

Thanks,

Corliss

You have to choose: either the database is encrypted, or it is not.

If it is encrypted, then each query will have to decrypt the data upon access.

OR, you may choose to use SSL or IPSEC to control and encrypt the data at the transport layer. (But that does not encrypt the database 'at rest'.)

I suggest that you look in Books Online for the topics: Encryption, Encrypted data. Here are some resources about encryption:

Encrypting Connections to SQL Server
http://msdn2.microsoft.com/en-us/library/ms189067.aspx

Encryption -Column-level using the CryptoAPI
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

Encryption -Example
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx

Encryption/Decryption
http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/sqlblog/archive/2006/11/02/part-i-data-security-enhancements-in-sql-server-2005.aspx

|||

Corliss, do you also expect that data is accessible only if clients connect via your ASP application?

Right now, there isn't any such capability, but we're always welcoming suggestions for new features, so we're interested in finding more about what type of encryption feature you would find helpful.

Thanks
Laurentiu

|||

Cristofor,

That woulbe be Ideal, but I also know that it isn't possible. I want to encrypt the entire database and only allow data to be accessible through the applications that we have on the server. this is troulesome for sure because the SQL server works best when authenitcation of users/ groups takes place.

|||Look into using IPSEC. It is not a 'complete' solution for your problem, but perhaps it can narrow down the attack vectors and reduce the risk.|||

Using IPSEC?

In terms of network traffic I don't really need to. All traffic is encrypted due to citrix and a VPN. "At Rest" is the issue:)

Is there a way to decrypt when the data needs to be accessed...like when an odbc connection is made.

|||

At this moment, there isn't any feature that would answer your requirements. The SQL Server 2005 encryption is meant for selective encryption of data - encrypting the entire database would require a different solution.

For the access restricted to applications, this is again not something that can be guaranteed - users can always bypass applications and connect directly by figuring out how the application connects in the first place. Encryption wouldn't address this scenario.

Thanks
Laurentiu

|||

You might find this thread 'insightful'. Similar issue.

http://forums.microsoft.com/MSDN/showpost.aspx?postid=1354173&siteid=1

|||

Corliss,

We have plans to release a feature that may solve your exact requirements in the next version of SQL Server. However, this may be limited to only certain SKUs. What SKU does your organization currently have license to? Would you be interested in participating with us in a public CTP program to try it out?

Thanks

Andy

|||

please forward me more details

corliss