March 7, 2005 at 10:39 am
Do you guys encrypt data on SQL 2K? If you do, you prefer server side (data sent to server and SQL ecrypts data before saving) or client side (data encrypted before sent to server? We are looking at encryption to protect sensitive data such as SSN's and still don't know what is the right way to start and what is the pros and cons for each approach? What tools do you guys use for this purpose? Thanks for your input.
March 7, 2005 at 12:12 pm
To start if you encrypt the data you will complicate queries etc on the back-end and you could even loose the index usability (depending on the encription) on that column. On the other end is probably the only thing you can do to guarrant value security server side.
You could read today's article as one product example to do it
and for more opinions you can follow this links
http://www.sqlservercentral.com/columnists/NWeicher/securityworstpracticeneil.asp
http://www.sqlservercentral.com/columnists/sjones/wp_encryption.asp
HTH
* Noel
March 7, 2005 at 12:31 pm
Thx. At one time we decided not to encrypt then it popped up last week when we had an security guy visited.
March 7, 2005 at 12:44 pm
At that point you will have to decide between performance and security.
If Security goes first then by all means you must use an API/Package/etc and go for it I am not saying one or the other is best, it will always depend on the context!
Just read those links and their respective threads so that you can get a feeling for what others think.
Good Luck,
* Noel
March 8, 2005 at 10:08 am
This depends. If the DBA is trusted to look at the data, SQL Server can do the encryption. If the DBAs aren't trusted, encryption should happen at the application layer. As has been stated, this is a performance hit, if for no other reason the CPU cycles to encrypt/decrypt. If it's a SARGable query and the column in question is encrypted, you pretty much have to decrypt ever entry meaning normal indexing is useless.
To give you an idea of how to think about where to encrypt, we can look at what they're doing in the next version. SQL Server 2005 is the first version to handle encryption natively and it basically takes a 3-tier approach. If the DBAs are trusted but the data needs to be encrypted, let SQL Server control the master keys and encrypt within SQL Server. if the DBAs aren't administrators but you don't want them seeing the data, you use encryption within SQL Server but the keys are controlled from "outside" SQL Server. If you don't even want administrators of the SQL Server seeing the data (or the DBA is an administrator and shouldn't see the data), then you encrypt at the application layer, completely outside of SQL Server. The reason for this last distinction is an administrator can potentially break into a process using a debugger, thus exposing the unencrypted data.
K. Brian Kelley
@kbriankelley
March 8, 2005 at 10:20 am
Thx Brian. I frankly haven't tried encryption on SQL 2005 version even I have installed and previewed other new features in SQL 2005. Will look at it later.
March 8, 2005 at 12:48 pm
You also want to consider that if the data is encrypted/decrypted at the database server, then the data is transmitted over the network as unencrypted, both ways. Especially if you are concerned about encrypted personal data (like ssn's, credit card numbers), you will want to encrypt at the earliest possible point, likely at the application client.
Mark
March 8, 2005 at 1:08 pm
it is used mostly for IIS. IIS and SQL boxes are sitting together at a data center. The only VB app accessing SQL is at the HQ but on VPN when it goes to SQL.
March 8, 2005 at 1:13 pm
This concern can be eliminated by either the use of SSL or IPSec, though. If you don't have a trusted network path, you want to do this for all traffic (if you can get away the performance hit), especially if you've got SQL Server logins. The "encryption" on a SQL Server login password as it goes across the network is trivial to break.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply