App.config how to use encrypted ConnectionString

In my app.config I have a section that for my String connection:

 </configSections>
<connectionStrings>
    <add name="Azure.Onee" connectionString="Server=tcp:tps****.database.windows.net,1433;Data Source=tps****.database.windows.net;Initial Catalog=Onee;Persist Security Info=False;User ID=*****;Password=*****;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" providerName="System.Data.SqlClient" />
</connectionStrings>

So, I encrypted the section using aspnet_regiis -pef, I got as a result:

 <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
    <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
        xmlns="http://www.w3.org/2001/04/xmlenc#">
        <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
        <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
            <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
                <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
                <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
                    <KeyName>Rsa Key</KeyName>
                </KeyInfo>
                <CipherData>
                    <CipherValue>Z3lLfHojiPPq+ACyP3o0nM/XdwDVj2jsDIMwER/xT6gxR7qzBgJPJIb7kpaZIaUJwQQjlV9fnmsNlpOM0dFrH+8J2Z4tpYM5mIcDMaJjW/dIXwXvEXdk7ESgaKSbpPgHOElvRMwQgs5LSWVjdqpP9G39StgoGoeTKlaIi7CXeSo=</CipherValue>
                </CipherData>
            </EncryptedKey>
        </KeyInfo>
        <CipherData>
            <CipherValue>z0dsujvZ7MffBHfNj12d+TYHHbcvdW84vCrZKt0ldps=</CipherValue>
        </CipherData>
    </EncryptedData>
</connectionStrings>

Well, now how do I use recover connectionStrings to open the connection to my database?

To perform the connection I am doing like this:

 private void simpleButton1_Click(object sender, EventArgs e)
    {
        string Usuario = textBox2.Text, Password = textBox1.Text;

        SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Azure.Onee"].ConnectionString);

        if (Usuario != string.Empty && Password != string.Empty)
        {
            try
            {
                consql._sql = @"SELECT id_usu FROM login WHERE usuario = @usuario AND password = @password";

                SqlCommand cmd = new SqlCommand(consql._sql, sqlconn);

                cmd.Parameters.Add("@usuario", SqlDbType.VarChar).Value = Usuario;
                cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = Password;

                sqlconn.Open();

                // etc..
            }
            catch (Exception)
            {

            }
            finally
            {
                sqlconn.Close();
            }
        }

When trying to run, the error is returned:

insert the description of the image here

I tried to run only with ConfigurationManager.ConnectionStrings without the index, here it does not even compile:

insert the description of the image here

With the help of our gypsy colleague, where he suggested to use the index 0, he passes, but this bringing a String totally different from the original String:

insert the description of the image here

Author: Thomas Erich Pimentel, 2016-09-12

1 answers

As I mentioned in comment, use, instead of the name of the connection string :

SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings[1].Conn‌​ectionString);

In encryption, the name of the connection string is lost.

Also be careful when encrypting projects whose Web.config have connection strings of development.

The name of the configuration file must be Web.config, not App.config. After encryption you can change it if you want.

 4
Author: Leonel Sanches da Silva, 2016-09-13 15:15:30