SQL 2005 Symmetric Encryption
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp
-
"The potential downfall of automatic key management is that it allows every sysadmin to decrypt the DMK."
This is a significant weakness because the weakest link of the security infrastructure is the login credentials of a sysadmin which can be easily obtained through social penetration.
Also, this solution does not protect against a malicious DBA or sysadmin.
-
Say your malicious person deletes your symmetric key? You can restore a master key, but I've seen nothing that tells me how to restore a symmetric key.
You can recreate the key, but if the KEY-GUID isn't identical, nothing encrypted with the old key can be decrypted with the new one. And with adhoc changes to system tables eliminated, your only choice is to restore a backup of the database to a new location, decrypt all the old info with the old key, and reencrypt it using the new key.
Correct me if I'm wrong. Please!
-
Anyone do any testing to see what the overhead is on the db server while it is encrypting or decrypting large amounts of data? We have always pushed the encryption load onto the application layer. It seemed to us that it was far easier to add more app servers then it was to upgrade the db server.
-
There are other weak points on the encryption architecture.
1. If your company ( like mine
) has a service password change policy the whole thing will be impractical 
2. Be careful to encrypt only the necessary stuff or table scans will fly all over the place.
3. Last but not least I still don't know what would happen if I need to restore a DB with some encrypted data on a different server ( which of course has a different master key)
The article was a nice synthesis but I would consider as one of the most important messages to the DBAs out there to try ALL possible scenarios BEFORE they jump into this feature because there are many "surprises" lingering around
Cheers,
* Noel -
1. You're right, a regular password change policy would make it more difficult. I think that's why they introduced the Service Master Key to encrypt the lower level keys (instead of encrypting them directly from the DPAPI). You can change the Service Master Key and it will automatically re-encrypt all the certs and keys it is currently responsible for encrypting.
2. That's the beauty of the SMK/DMK system. The SMK only encrypts DMKs. When you Backup DMKs, they are encrypted using the password you specify; when you Restore the DMK into a new server, it is decrypted using your password and then encrypted using the local SMK. Now transferring encrypted data (without first decrypting) and symmetric keys... that might require a little tinkering
-
I'll have to check into that, but I didn't think the KEY-GUID was required; my understanding was that it is just a tag that allows you to determine what data is encrypted with a particular key. Being able to recreate a symmetric key with a password (basically they just hash your password, then feed it to the DPAPI/CryptoAPI to create a Session Key) should be enough to get the job done.
I'll look into it and see if I can locate any more information on the usage of the KEY-GUID.
-
I haven't done any performance testing, although I have been accessing the CryptoAPI and DPAPI from C++ apps and SQL Server XP's in testing for a while now; the performance of these particular routines is pretty good. The tight integration with the SQL Server should help performance.
Also, your biggest savings will probably come from not having to pull as much data across the wire. If you have to search an encrypted column for a range of data (WHERE LastName LIKE 'A%' for instance), your middle tier is going to have to pull every single last name across the wire, decrypt it, and then send the results forward. If all the encryption is done on the SQL Server itself, you'll give your network cards a break.
OTOH, if you're only performing exact matches (WHERE LastName = 'ADAMS'), that won't be much of a performance benefit.
-
Of course when it comes down to it, this has been true since...? Even if you don't encrypt your data, the malicious DBA or sysadmin is still a threat.
So even if you don't encrypt your data, the credentials of a sysadmin are sill easily obtainable 'through social penetration.' (I don't even know what that means, but it gives me a sudden urge to make a doctor's appointment...)
-
..If you're dealing in "x = y" type WHERE clauses though, simply encrypt y to match x and voila.
NOPE! that is not the case. If you run that, you will be searching for binary values for which not even an index is going to help
Encrypted columns are not good for:
- indexes
- searches
- group by
- order by
Any of those you attempt a table scan will await for you
... that's an example of the "surprises" I was talking about The solution is to create a hash function and search the hashed value instead or search through another column(s) to narrow down the unecryption scope!!
Cheers,
* Noel -
Hi Noel,
I see your point about the scans. The problem is that SQL 2005 generates a "random IV" to encrypt with. So this prevents you from encrypting a your search string and comparing it. RC4 doesn't have this behavior, but apparently that's a bug and MS recommends against using RC4.
Based on cursory tests, it actually looks like they are storing the "random IV" with the encrypted data. There may be other information stored in there as well. I'm in the process of analyzing encrypted SQL 2005 strings now to see if I can determine if storing this information with the encrypted data makes it less secure. MS employees do seem to believe that having a user-managed IV is less secure than their "random IV" generator; so my question is this: is an IV stored with the data any more secure than a user-managed IV?
-
I found a small bug (I only looked at the certificate example, but the symmetric example may have it, too).
The result of DecryptByCert has to be converted to text before stroing it in a text field.
This is the idea:
select Convert(varchar,DecryptByCert(Cert_ID('TestCertificate'),eFirstName))
FROM [Person.#Temp];
You'd have to do this in the UPDATE where it's setting the clear text fields:
SET FirstName = Convert(varchar,DecryptByCert(Cert_ID('TestCertificate'), eFirstName)),
MiddleName = Convert(varchar,DecryptByCert(Cert_ID('TestCertificate'), eMiddleName)),
LastName = Convert(varchar,DecryptByCert(Cert_ID('TestCertificate'), eLastName));
I suppose nvarchar would be the ideal result of the Convert instead of varchar, since the column is defined as nvarchar.
Eric
-
Hi eengler,
You're right, it's good programming practice to explicitly cast your results to the proper type. You should also specify a target size for the cast data type or SQL may truncate your results.
SET FirstName = CAST(DecryptByCert(Cert_ID('TestCertificate'), eFirstName) AS NVARCHAR(100)),
MiddleName = CAST(DecryptByCert(Cert_ID('TestCertificate'), eMiddleName) AS NVARCHAR(100)),
LastName = CAST(DecryptByCert(Cert_ID('TestCertificate'), eLastName) AS NVARCHAR(100));
The VARBINARY result of the EncryptBy... and DecryptBy... functions can be implicitly cast to NVARCHAR or VARCHAR as well. You should get the same results whether you're using implicit or explicit conversions in the sample - so I don't know if I'd classify it as a bug; maybe a best practices "feature request"
.Let me know if you get different results when you use explicit casting versus implicit casting. Thanks.
-
How can I remove access to all members including sysadmin to Decrypt the value opening the symmetric key. Is there a way I can deny access to sysadmin and give access to only one user account who can decrypt the data
-
You want to start by looking at turning off "Automatic Key Management" with the ALTER MASTER KEY statement. Take a look here: http://msdn2.microsoft.com/en-us/library/ms174433.aspx, http://msdn2.microsoft.com/en-us/library/ms186937.aspx
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply