.NET Encrypting Database Connection String in App.Config

You can encrypt the connection string settings in app.config through  aspnet_regiis tool.

I have researched this method previously and it looks like it will only work on the machine where the aspnet_regiis tool was ran.

This application EXE will be stored in a shared folder where multiple machines will access it. So the Machine1, Machine2 and Machine3 will need to be able to decrypt the same string... Hope that makes sense...

I have been looking at AES encryption but unsure how the Keys and IV are generated or how they will be shared between machines...

Another way through code

public static void EncryptConnectionString(bool encrypt,string fileName)
{
    Configuration configuration = null;
    try
    {
        // Open the configuration file and retrieve the connectionStrings section.
        configuration = ConfigurationManager.OpenExeConfiguration(fileName);
        ConnectionStringsSection configSection =
        configuration.GetSection("connectionStrings") as ConnectionStringsSection;
        if ((!(configSection.ElementInformation.IsLocked)) && 
              (!(configSection.SectionInformation.IsLocked)))
        {
            if (encrypt && !configSection.SectionInformation.IsProtected)
            {
                //this line will encrypt the file
                configSection.SectionInformation.ProtectSection
                      ("DataProtectionConfigurationProvider");
            }

            if (!encrypt && 
            configSection.SectionInformation.IsProtected)//encrypt is true so encrypt
            {
                //this line will decrypt the file.
                configSection.SectionInformation.UnprotectSection();
            }
            //re-save the configuration file section
            configSection.SectionInformation.ForceSave = true;

            // Save the current configuration          
            configuration.Save();
        }
    }
    catch (Exception ex)
    {
 
    }            
}

As has been pointed out in another EE_PAQ

The best option is to use integrated security.

-saige-

Would this work on two different machines and two different user ids?

Unfortunately integrated security is not possible in this instance...

Yes because integrated security uses the security implemented by SQL using Windows Authentication.

-saige-

How would encryption work in this case:

User A sends encrypted string to database
User B retrieves encrypted string from database and decrypts it

If the encryption keys and IV are random how would user B decrypt the string?

Unfortunately integrated security is not possible in this instance as I cannot change the security type on the database. I only have read access, we cannot change the database.

No the user doesn't send anything to the database.  Rather, you configure the users (by either individual user or group membership) into the security model for the database.  This way they can login to the sql instance using their own username and password (which is passed through via integrated security)...

-saige-

@Saige, I understand  integrated security but unfortunately in this instance without database ownership we cannot change the security of the database. This is the reason I need to encrypt the connection string.

If it is your only recourse then I understand.  In this previous EE_PAQ, I demonstrated how to decrypt a connection string from an app.config.  The original author uses a helper class to handle the encryption and decryption process.  All you would need to do is add the ecrypted string in the place of your current connection string and decrypt it when you read it.

-saige-

@Saige, unfortunately it is. I have two databases I am connecting to. My database where I am the DB Admin I  have integrated security, but the third party database does not.

How or where would I store the Key and IV? How are these keys generated are they random or do they relate to the application. This is the part that I do not understand. If they are auto-generated for the app how will the next machine that uses the app know what the key and IV is?

For example would I have in my app two variables:

Dim key = '1234'
Dim iv = '5678'

This doesn't seem right as this code in the app could be de-assembled/hacked...

Thanks, Greg

Couldn't you simply store the connection string in the database that uses integrated security?

-saige-

Yes I could do that -  although I would still like it encrypted. Which is what I am trying to get my head around how to do it properly.

That sounds like a good idea. WIth regards to the MAC address as a hasher would this not be a problem when a different machine opens the application?

Although my connection to the database in integrated I still would like to encrypt this connection string also to hide the server details, e.g. server name, database name etc...

Would really like to know the best approach on how to encrypt a string on PC 1 and then decrypt it on PC 2, where the users are different users.