Request for feedback: SQL Server encryption interoperability

Tue, 08 May 2007 00:48:05 Z

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

Request for feedback: SQL Server encryption interoperability

Tue, 05 Jun 2007 18:45:28 Z

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?

Request for feedback: SQL Server encryption interoperability

Mon, 11 Jun 2007 20:33:37 Z

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

Request for feedback: SQL Server encryption interoperability

Tue, 12 Jun 2007 15:05:57 Z

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.

Request for feedback: SQL Server encryption interoperability

Wed, 13 Jun 2007 17:30:46 Z

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

Request for feedback: SQL Server encryption interoperability

Fri, 15 Jun 2007 22:24:28 Z

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.

Request for feedback: SQL Server encryption interoperability

Tue, 19 Jun 2007 20:15:08 Z

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.

Request for feedback: SQL Server encryption interoperability

Tue, 19 Jun 2007 22:39:10 Z

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

Request for feedback: SQL Server encryption interoperability

Tue, 31 Jul 2007 14:13:35 Z

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

Request for feedback: SQL Server encryption interoperability

Fri, 28 Sep 2007 11:04:21 Z

One thing comes to mind in terms of interop with apps on multiple platforms. I can create certs on a platform and sign an application with the cert. I can load the same cert into SQL Server. But, the two have no ability to recognize each other. What would be nice is to be able to sign an application with a cert, load the cert to SQL Server and create a login for that cert, then be able to allow the application to login to the SQL Server by means of the cert that it is signed by.

Granted, there might be all kinds of loopholes here that might crack my SQL Server wide open to attacks, but I leave that analysis up to y'all.

Other than that, the further that I go into this with an unending set of progressively more complicated demos, the more I'm beginning to appreciate what is now here. From way back in Beta 1, I knew that the security infrastructure was by far the biggest single change in SQL Server 2005. But, until I was forced to dig into every nook and cranny of all of the features, I didn't have any idea of just how deep it goes. It's getting rather hard to come up with business scenarios that aren't already covered.

Request for feedback: SQL Server encryption interoperability

Fri, 28 Sep 2007 11:32:56 Z

I think this would be very useful. I've had exactly this need--to authenticate an application (vs. a user). I've ended up with a silly hack and then a switch to approle. Because it is .NET my code is easily reverse engineered--not secure. Authenticating the app via certificate signing would definitely be better!

Request for feedback: SQL Server encryption interoperability

Sat, 29 Sep 2007 06:37:41 Z

Sorta, kinda related to encryption since you are using the master key and cert infrastructure. No suggestions, just feedback.

When I first looked at signatures, I was baffled and couldn't see any reasonable way to really deploy them for most of the customer situations that I come across. The examples deal with cross database ownership chaining and other things along those lines. The more I play with signatures, the more I like them. Does wonders for building robust and very secure auditing capability. I can now do things like audit user actions from triggers without needing to grant users access to my audit tables. I can ensure that users have no access whatsoever to my audit tables while at the same time applying a signature to the audit triggers that allows auditing to still function. The longer I play with them, the more applications that I'm finding for signatures.

Request for feedback: SQL Server encryption interoperability

Mon, 01 Oct 2007 12:25:34 Z

So, can you recommend a good text that covers the SqlServer 2005 security apparatus (including decent examples) in depth? Something that might help with all those "nooks and cranies?" OJT and "learn by doing" are great; but for this old dog, the new tricks are getting harder and harder. And please don't point me back to Books Online. Thanks!

Request for feedback: SQL Server encryption interoperability

Mon, 01 Oct 2007 16:59:35 Z

So here's one specific item.

Authenticators exist for symmetric keys and passphrase encryption. Why can't I specify an authenticator when I encrypt with an asymmetric key or a certificate? This leaves asym keys and certs open to copying attacks within a table. While symmetric keys offer better performance, it would be nice to be able to make my own choice as to what to use and I don't see any reason why an authenticator can't be specified as an option when using asym/cert.