Subject: Reading an encrypted connection string.
Posted By: asp125 Post Date: 1/16/2008 3:23:32 PM
I am trying to do the following: So far I have been able to just add an empty app.config file to my project. Then I added a connection string to this web.config file dynamically. I then went on to store that database connection string securely and dynamically using the standart RSA Protected Configuration Provider. In short I used regular C# code to create and store a db connection string without ever touching the Web.config file. Now the problem I have is that, I need to now read that encrypted db connection string to be able to do other cool stuff like create a table and then populate it. I am just not able to read this connection string. I am sending my final results to an XML file. I am getting to this step but I don't see anything in SQL Server. I know I am first trying this on a console app using an app.config file, I hope it won't be a problem just going to web.config file later.
Here is the code: Best viewed in Visual Studio or Notepad++

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Ch6DataSets_CreateDataTable
{
    class Program
    {
        private static void WriteEncryptedConnectionStringSection(string name, string constring, string provider)
        {
            //Get the configuration file for the current application. Specify the ConfigurationUserLevel.None
            //argument so that we get the configuration settings that apply to all users.
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
 
            //Get the connectionString section from the configuration file
            ConnectionStringsSection section = config.ConnectionStrings;

            //THIS WAS THE PROBLEM!
            //If the connectionString section does not exist, create it.
            //if (section == null)
            //{
            //    section = new ConnectionStringsSection();
            //    config.Sections.Add("connectionSettings", section);
            //}

            //If it is not already encrypted, configure the connectionString section to be encrypted using
            //the standard RSA Protected Configuration Provider
            if (!section.SectionInformation.IsProtected)
            {
                //Remove this statement to write the connection string in clear text for the purpose of testing
                section.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
            }

            //Create a new connection string element and add it to the connection string config section.
            ConnectionStringSettings cs = new ConnectionStringSettings(name, constring, provider);
            section.ConnectionStrings.Add(cs);

            //Force the connection string section to be saved.
            section.SectionInformation.ForceSave = true;

            //Save the updated configuration file.
            config.Save(ConfigurationSaveMode.Full);
        }
        static void Main(string[] args)
        {
            //The connection string information to be written to the configuration file.
            string conName = "ConnectionString1";
            string conString = @"Data Source=.;Database=ADO20;Integrated Security = SSPI;" +
                                "Min Pool Size=1;Max Pool Size=15;Connection Reset=True;Connection Lifetime=600;";
            //string conString = @"Data Source=.;Database=AdventureWorks;Integrated Security = SSPI;" +
            //                    "Min Pool Size=1;Max Pool Size=15;Connection Reset=True;Connection Lifetime=600;";
            string providerName = "System.Data.SqlClient";

            //Write the new connection string to the application's configuration file.
            WriteEncryptedConnectionStringSection(conName, conString, providerName);

            //Read the encrypted connection string settings from the application's config file.
            ConnectionStringSettings cs2 = ConfigurationManager.ConnectionStrings["ConnectionString1"];

            //Use the connection string to create a new SQL Server connection.
            using (SqlConnection con = new SqlConnection(cs2.ConnectionString))
            {
                //SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Person.Address", con);
                //con.Open();

                ////Execute the command and cast the result
                //int result = (int)cmd.ExecuteScalar();
                //Console.WriteLine("Person address count = {0}", result);

                #region CreateTable
                //Creat the table
                DataTable productsTable = new DataTable("Products");

                //Build the Products schema
                productsTable.Columns.Add("ID", typeof(System.Int32));
                productsTable.Columns.Add("Name", typeof(System.String));
                productsTable.Columns.Add("Category", typeof(System.Int32));

                //Set up the ID column as the primary key
                productsTable.PrimaryKey = new DataColumn[] { productsTable.Columns["ID"] };
                productsTable.Columns["ID"].AutoIncrement = true;
                productsTable.Columns["ID"].AutoIncrementSeed = 1;
                productsTable.Columns["ID"].ReadOnly = true;

                DataRow tempRow;
                //Populate the Products table with 10 cars
                for (int i = 0; i < 10; i++)
                {
                    tempRow = productsTable.NewRow();
                    //Make every even row Caterham Seven de Dion
                    if (Math.IEEERemainder(i, 2) == 0)
                    {
                        tempRow["Name"] = "Caterham Seven de Dion #" + i.ToString();
                        tempRow["Category"] = 1;
                    }
                    else
                    {
                        tempRow["Name"] = "Dodge Viper #" + i.ToString();
                        tempRow["Category"] = 2;
                    }
                    productsTable.Rows.Add(tempRow);
                }

                //Write table to XML
                productsTable.WriteXml(@"C:\DM_Projects\productTable.xml");
                #endregion CreateTable

            }

            //Wait to continue
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}
Reply By: asp125 Reply Date: 1/16/2008 5:19:08 PM
Don't worry, I found the answer. ASP.NET will automatically decrypt the encrypted sections its reading. In short, once the configuration information is encrypted, you don't need to write any further code or take any further action to use that encrypted data in your application.
Reply By: englere Reply Date: 1/18/2008 9:54:49 AM
You can also use the "mod" function in place of IEEERemainder - this is "%" in C#:

 i % 2 == 0

I'd refactor these statements into a new function called "makeTable" just to make it cleaner:

                DataTable productsTable = new DataTable("Products");

                //Build the Products schema
                productsTable.Columns.Add("ID", typeof(System.Int32));
                productsTable.Columns.Add("Name", typeof(System.String));
                productsTable.Columns.Add("Category", typeof(System.Int32));

                //Set up the ID column as the primary key
                productsTable.PrimaryKey = new DataColumn[] { productsTable.Columns["ID"] };
                productsTable.Columns["ID"].AutoIncrement = true;
                productsTable.Columns["ID"].AutoIncrementSeed = 1;
                productsTable.Columns["ID"].ReadOnly = true;

You need to be aware that WriteXml is not thread-safe if you'll have a lot of users executing this code.

Lastly, I'm not a big fan of encypted connection strings built into web.config. That puts the encryption out of my hands and it becomes machine-specific. It depends on the machinekey. Definitely not friendly to shared hosting, but it's more acceptable if you control the servers. Just be careful about how you deploy. Always deploy clear strings and them encrypt them in-place on the server, since it depends on that server's machinekey.

Eric
Reply By: asp125 Reply Date: 1/18/2008 10:24:09 AM
Thank you Eric. I will certainly follow your advice and I do appreciate the post. I will deploy the site to a shared hosting server so your input is greatly appreciated.
Reply By: Erka Reply Date: 8/25/2008 12:35:03 AM
Hello! I am Erka. I am from mongol.
I have a problem. I want to secure some HTML line in my Web.
But i don't know how secure there some line. .
Please, Help me someone.
Thank you.
 

Erka
Reply By: Erka Reply Date: 8/25/2008 12:46:15 AM
Hello! I am Erka. I am from mongol.
I have a problem. I want to hide some HTML line in my Web.
But i don't know how hide there some line. .
Please, Help me someone.
Thank you.

Erka
Reply By: Lee Dumond Reply Date: 8/27/2008 9:52:31 AM
quote:
Originally posted by Erka

Hello! I am Erka. I am from mongol.
I have a problem. I want to hide some HTML line in my Web.
But i don't know how hide there some line. .
Please, Help me someone.
Thank you.

Erka


<%-- this line is hidden --%>

Go to topic 73544

Return to index page 1