Encrypt connection strings. Where?

Question

I see all different posts about how and where to encrypt connection strings (SQL or storage accounts). It seems like the Microsoft way might be to put those in the web.config file transforms and use the built in pkcs encryption magic. But others seem to indicate that the cscfg file is the correct place to put settings when running in Azure

I've been struggling trying to get the web.config mechanism to work, and I have to say I don't like it in the Azure ecosystem. It seems to have many restrictions when compared to the cscfg file. For example the config transform doesn't occur when debugging/building, only when publishing. And it seems like MS only supports this transformation magic for the web.config and not the app.config (although I see other tools that mimic the behavior for app.config). And for a webrole, the Role code (OnStart and Run) can't read from the web.config file as it's not running in the same process. I have some hacks to get around some of these things, but they are turning ugly and error prone.Plus I can't change settings (like an azure storage key) on the fly if I put it in the web.config, which I can do if I put it in the cscfg file.

I really just want to stick this stuff in the cscfg file, but have them encrypted. I can do the encryption programatically myself, but I'm not sure what library to use for encryption (I want to use the same sort of encryption as web.config, but I think the providers used there are kind of hard wired to with the web.config. I want the generic library that I assume the provider uses internally). If I could do that, the cscfg feels like a way better option than the .config files.

Does MS have an official answer for this? What is the best practice recommendation?

Answer 1

Please have a look at "Encrypting Web.Config" section in the SQL Azure team blog entry

- http://blogs.msdn.com/b/sqlazure/archive/2010/09/09/10059889.aspx

this blog post is 3rd in a series, I recommend you read all of the these

first blog post is here

http://blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx

-------------------------------------------------

Please mark as answered if it helped

---

Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 2

I've seen those, but it doesn't really cover all the facets of this. So for a webrole where you don't need to access the connection strings from the Role code, then it works fine and everyone is happy.

The fact that it doesn't inherently apply to app.config files seems to rarely be discussed. And the fact that you can't access the values from webrole Role code is rarely discussed (and usually those discussions seem to suggest that the cscfg is a better place to store that). I believe I can add entries to the webrole's dll.config file for the Role code to read (in addition to the web.config one), but then I've got it defined in two places and I'm not sure how/if I can setup transforms for that file.

Answer 3

i wont recommend to put settings in webrole's dll.config file, it will be hard to maintain, you loose grip on maintainance when you scale out, have no easy way of refreshing the settings

---

Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 4

cscfg is the place to put settings.. or atleast the optimal way

---

Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 5

Right now I fully agree, but it'll be a hard sell if MS recommends otherwise. But then is there a standard way to encrypt the connection strings when they are in the cscfg file? I want to use the same encryption technique as is used for the web.config magic (since I'm not a security expert, I don't want to try to guess at what encryption to use). Is there a library that will allow me to do that same encryption, but outside of the web.config workflow? Any examples of this?

Answer 6

the high level guidance is that you

1. create a cert

2. encrypt connection string using that cert

3. upload cert to azure storage account so your role instance can access it

4. decrypt using the cert in role instance

the blog post (please go through the post series it explains how to import cert in azure store and other steps)

To encrypt SQL Azure connection, use Encrypt=True and TrustServerCertificate=False in the connection string 

http://social.msdn.microsoft.com/Forums/windowsazure/en-US/065931fd-438f-46bc-8d21-6a0eedc7748d/secure-storage-connection-strings-in-cscfg-files

---

Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 7

I wouldn't think IT would want to have the cert in Azure storage, although I'm not sure of the exact issues. I can gauge their reaction. Feels less secure though. We have a build machine, and I was thinking that the cscfg files could include the cert, and the build machine would have the cert, and it would do the build and publish the service. So it would just be part of the cspkg.

Answer 8

at this point the cert have to be imported using Windows Azure Dashboard

you may want to also explore if Azure REST Api already support performing the cert upload and wiring up of the cert with your services

http://msdn.microsoft.com/en-us/library/windowsazure/ee460799.aspx

-------------------------------

Please mark the post as answered if it helped

---

Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

Answer 9

I know this is an old post, but I thought I would add my experience and hope it helps anyone who stumbles into this post.

I have a solution with a WebRole and WorkerRole. They both need to access the same database, and I want to connection strings to be encrypted. So here is what I did:

1. I used the encryption technique as described in the link http://blogs.msdn.com/b/sqlazure/archive/2010/09/09/10059889.aspx. The only difference being that I used the pkcs12 dll from nuget.
2. I keep the connection strings in the web.config files. I had to encrypt each transformation file seperately, and copy paste these in. It seems the aspnet_regiis does not encrypt transformation files - only web.config.
3. I write a function in the WebRole that returns the unencrypted connection string.
4. I add the WebRole dll as a reference to the WorkerRole. I can then use the function from 3 to get the connection string.
5. If you are using EF -  then there is a constructor that is available where you can pass the connection string.