Recently we have seen a few questions regarding interoperability between SQL Server encryption built-ins and CLR (or CAPI) encryption. While we have a very limited interoperability in SQL Server 2005 (read http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1529101&SiteID=1 as a reference), we would like to understand your needs.
Please let us know about your cryptographic needs in SQL Server 2005, and why the existing functionality we have for both data at rest (encryptbykey, etc.) and data in transit (SSL) are not sufficient to solve your scenarios as well as what kind of functionality would you expect from SQL Server or the tools we ship in this area.
We will greatly appreciate your time as we are really interested in this feedback as it will help us to improve our existing infrastructure and hopefully help solve your needs in papers, tools, examples or/and future versions of SQL Server.
Thanks a lot for all your feedback and comments in advance.
-Raul Garcia
SDE/T
SQL Server Engine
I may have 25 years of development experience under my belt, but I'm a novice regarding Sql Server and security. (btw, I've been plundering yours and Laurentiu Cristofor's blogs for information. Thanks!)
It seems to me that storing encryption keys separately from data is inherently safer than storing these in the same place.In my current project I have users sharing the same database (same schema and same tables) but I must protect some of their records. All my users are authenticated by Windows (Kerberos) which allows me to encrypt using the DataProtection API.Except that DpApi isn’t directly supported by Sql Server.
So, I create some .NET wrappers; compile and upload the assembly to Sql Server; and it fails because of permissions. I get it to work with setting the db to Trustworthy only to later learn that this is a discouraged practice. Instead, eventually, I figure out to sign the assembly with a certificate, load the same certificate to Sql Server, create a login using the certificate, and then demand and grant the necessary permissions; except that now I need to use UNSAFE when I create the assembly.(I think it odd that DpApi would fall under UNSAFE...) Anyway, in AssemblyInfo.cs I end up with:
[assembly: PermissionSet( SecurityAction.RequestOptional, Unrestricted = false )]
[assembly: DataProtectionPermission( SecurityAction.RequestMinimum, Flags = DataProtectionPermissionFlags.AllFlags )]
And in code:
[DataProtectionPermission( SecurityAction.Demand, Flags = DataProtectionPermissionFlags.ProtectData )]
[SqlFunction( Name = "EncryptDpApi", DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None )]
public static byte[] EncryptDpApi( byte[] data )
Anyway, this seems like a lot of work to use the DpApi. Perhaps there’s a better way?
Another requirement in my app is to share some records between users in the same group or business unit.I may use the built-in Sql Server ability to create and mange shared encryption keys.But following the DpApi model I’ve implemented, it seems easier to call an Encrypt / Decrypt pair in code and pass a shared key.
public static byte[] EncryptCryptoApi( byte[] pswd, byte[] data )
public static byte[] DecryptCryptoApi( byte[] pswd, byte[] data )
For various reasons it makes sense to have the encrypt / decrypt algorithm on both client and server.Yet the EncryptByPassPhrase and DecryptByPassPhrase use a proprietary (unpublished) algorithm. Thus I’d have to send the pass phrase key along the wire. Don’t want to do that.Thus another reason to implement wrappers for AES, etc. and a .NET assembly.
I am not using SSL in the app, and yes this doesn't make a lot of sense. Doing the best I can within some silly constraints and my own ignorance.
Part of why I'm taking the approach I am, is because there really isn't any good primer (at least that I've found) to show how to realistically protect app data. Wish I had a simple shell and setup to work from, as well as explanatory docs. Have you noticed how msft docs have gotten harder and harder to decipher over the years?
|||
Thanks a lot for the feedback. Let me summarize the points for interoperability and improving the encryption infrastructure from your previous post, please correct me if I am missing or misquoting something:
1. Separating key storage from the protected data storage
Motivation: Increase the level of protection of data at rest.
2. Create a layer for allowing encryption/decryption across platforms.
Motivation: Being able to encrypt/decrypt data both in client and server independently
Regarding your concern on Encrypt/decyptByPassphrase, I just want to clarify that we are using conventional algorithms (i.e. 3DES), but we have some extra SQL Server specific information that is part of the plaintext, but not documented. As an additional note, I am also making a note on the feedback regarding your concerns of sending the passphrase as plaintext (TSQL) for this call.
Regarding your DPAPI usage, I would like to point out a couple of pitfalls of DPAPI that may not be obvious but that may affect your scenario:
· DPAPI is machine-specific unless you are using roaming profiles (http://msdn2.microsoft.com/en-us/library/aa380261.aspx)
· DPAPI has limited concurrency support (only up to 10 simultaneous calls if I do remember).
Thanks a lot for all your help and support,
-Raul Garcia
SDE/T
SQL Server Engine
|||
My understanding is that DpApi is either machine OR user specific, from Keith Brown's book:
Using DPAPI, you can encrypt data with a user's login credentials (which means you need to decrypt the data in the same security context in which it was encrypted), or you can encrypt the data using the machine's credentials. If you encrypt with the user’s credentials, when the user is not logged in to the machine, her key is not present on the machine at all, which is fantastic!
http://pluralsight.com/wiki/default.aspx/Keith.GuideBook/HowToStoreSecretsOnAMachine.html
I thought that the user should be able to access the same (encrypted) data from different machines, as long as the user was authenticated by the same ActiveDirectory (kerberos) server...
I had even planned to allow an overnight batch process to access the encrypted data by using Protocol Transitioning and impersonation. However, the clients have nixed this approach as to bleeding edge / risky.
At any rate your summarization of my initial needs is accurate. I'm now taking a more conventional approach: passing encrypted keys back and forth, using server side permissions to "hide" db objects, and server side symmetric keys mapped to users and groups. This is semi-good, but easily compromised because it all hinges on using approle. (Approle as the owener of the objects.) Compromise the approle password and it all falls apart. Sigh.
Again, thanks you're blogs have really helped me ramp up to speed.
|||DPAPI can be used to protect the information based on the machine credentials (CRYPTPROTECT_LOCAL_MACHINE, so anyone on that machine can access it), or based on the Windows user credentials, that is correct. But in the case the data is protected by the User’s credentials, the data can typically only be access on the same machine. I copied the following excerpt from the BOL link I posted earlier:
Typically, only a user with logon credentials that match those of the encrypter can decrypt the data. In addition, decryption usually can only be done on the computer where the data was encrypted. However, a user with a roaming profile can decrypt the data from another computer on the network.
I hope this information will be useful, and once again thanks a lot for your feedback, we really appreciate it.
-Raul Garcia
SDE/T
SQL Server Engine
|||Suggestion from a different thread in teh forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1306030&SiteID=1).
Having an option to specify the IV on Encrypt/Decrypt calls.
|||Besides SQL Server Books Online, can you recommend a Primer and Advanced material for security and permissions in SQL Server 2005? Books, links, anything that is more than a perfunctory chapter on "defense in depth" would be greatly appreciated.
I've come a long ways, but I can't quite reach critical mass. Need some good reading material. Thanks.
|||Other than BOL, have you tried Laurentiu's blog and my blog?
http://blogs.msdn.com/lcris/
http://blogs.msdn.com/raulga/
Unfortunately I don't have any good external resource at hand that I can recommend, I will also appreciate any pointers to external websites and/or books that talk about this topic in depth (SQL Server specific or DB in general).
Thanks a lot,
-Raul Garcia
SDE/T
|||Here is my situation and I am sure that others have the similar issues.
We receive files from many different clients. Most of the files have sensitive information of some kind in them. What we currently do is to either have the client PGP the entire file, or encrypt just the sensitive parts. But this means that we have to decrypt the file on a file or app server, push the data clear text to a bulk load table, and then encrypt the data to the main tables using symmetric keys on SQL2005. We tried doing this with SSIS but found that it was much slower than the way we are currently doing it. Some of our data loads are very large.
This means that the data is in clear text on the app/file server for some short time and in the load table for some short time.
What I would like to see is the ability to give my client a public key that they could use on different platforms (SQL or .NET or some type of PGP program) to encrypt the sensitive data and then send it to me. I could then just load the data to my tables and never have to worry about it sitting in clear text.
We are currently going through a PCI (Payment Card Industry) and data security is of the utmost importance with them. We are having to put all kinds of secondary safeguards in due to the fact that our data is at rest in clear text for some short period of time.
Thanks!
Jim Youmans
St. Louis, Missouri
No comments:
Post a Comment