encrypted data store

For a while, I've been struggling with how to persist database connection strings. Some of them have user accounts and passwords, so I don't want them in plain text in the config file. I don't really want them hard coded either. I have played with encryption mechanisms on the config files but can't find anything I like. Using Microsoft's default framework, either the encrytpion is tied to the user id --- so when you move from a dev account to a production one, you can't decrypt --- or it's tied to the machine, which affords the same issue. If you manually encrypt, then you’re just displacing the issue, since you need to find a way to store an encryption key or the like.

So, here’s my solution. I’ve set up an encrypted database repository to store database connection strings. The connection string to this repository is in a plain text config file, but since it is SqlServer, it uses user auth to connect – so, no passwords needed. Since it’s an encrypted table, anyone looking to decrypt needs access to the database, the table, the cert and the symmetric key. Without all those privs, you get nothing. If additional security is needed, you can add certificate enforcement on the connection itself.

Here’s how to set up the basics.

First, you’d need to create a table. I’ll ignore the DDL for this. I used a “database name” for the key. For the value, you’d want something like this:

ALTER TABLE DataConnectionValues

ADD ConnectionString varbinary(255);

GO

It should be a big enough column, of course. I picked 255, since it has to account for an encrypted string, which would be longer than the non-encrypted one. And I picked binary since the encryption may use non-standard characters.

Ok. Next we’ll need a master symmetric key, if one doesn’t exist

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = ‘someCleverPassword’

GO

The inner select and existence check are clearly not needed, but I added them anyway.

Now a certificate:

CREATE CERTIFICATE DBConnection

WITH SUBJECT = 'Encryption for database connection strings';

GO

Next, a symmetric key using the cert

CREATE SYMMETRIC KEY DBConnection_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE DBConnection;

GO

I used AES 256, but SQL Server supports other encryption types.

That’s pretty much it for the set up.

Now you can insert a row into the table, then do an update to add the encrypted value.

To update a value, first open the key:

OPEN SYMMETRIC KEY DBConnection_01

DECRYPTION BY CERTIFICATE DBConnection;

Then do an update, using the EncryptByKey function (it takes a reference to the key just created above):

UPDATE DataConnectionValues

SET ConnectionString = EncryptByKey(Key_GUID('DBConnection_01'), 1234);

GO

The EncryptByKey will encrypt using the key, while the DecryptByKey function will decrypt, of course.

I’d recommend wrapping these in a set of functions or stored procedures so that the developers don’t have to mess with keys and certs. In addition, that means that the user accounts only need execute privs on the procedures, not select privs on the table.

The coolish thing is that the users will get nothing if their accounts don’t have the right privs.

To get that, you need connection and select privs on the table (or execute on the stored procedures), privs on the key and the cert.

You can add these cert and key privs by:

GRANT REFERENCES ON SYMMETRIC KEY::[DBConnection_01] TO [someUserId]

GRANT CONTROL ON CERTIFICATE::DBConnection TO [someUserId]

If you really want to get cool, you can add connection encryption to the initial connection string to get the database values. This is probably a good idea since you’ll be passing passwords across the wire. Doing that does two things: First, it encrypts the connection, second, it secures it via the certificate. Not only does this make it harder to hack the values, it adds an additional layer of authentication, since now, the user not only has to have the correct user credentials, but also has to have the correct certificate installed.

From the developer perspective, this is as simple as changing the initial connection string to something like:

Data Source=someServer;Initial Catalog=myEncryptedData;Integrated Security=SSPI;Encrypt=true

That last part sets the encryption to true, and secures the connection. To do this, you’ll need to set up a server certificate on the connecting server and also on the database server.

I’m out of space to discuss that here, but Google knows everything.

Next time I'd like to blog about how to wrapper this into a data connections library and use an object factory to really abstract the complexity of connecting to databases.

--kevin