Best way to encode SqlConnection string information

Question

Hello I have this code below which reads Sqlconnection string information. But the problem is that I store this information as it is. Not encoded so In my opinion is very unsafe to do that. Is there any way to encode it and then decode? Or hash it? Thank you so much for your time and comments.

internal static class DataSource
    {
        private static string _ConnectionString;
        public static string ConnectionString
        {
            get
            {
                if (_ConnectionString == null)
                    _ConnectionString = FunctionToDynamicallyCreateConnectionstring();
                return _ConnectionString;
            }
        }
        private static string FunctionToDynamicallyCreateConnectionstring()
        {
            string path = "C:\\Users\\marek\\Documents\\Visual Studio 2012\\Projects\\tours\\tours\\sql_string.txt";
            StreamReader sr = new StreamReader(File.Open(path, FileMode.Open));

            SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();

            cb.DataSource = sr.ReadLine();
            cb.InitialCatalog = sr.ReadLine();
            cb.UserID = sr.ReadLine();
            cb.Password = sr.ReadLine();

            return cb.ToString();
        }
    }

This is how I store it:

 string path = "C:\\Users\\marek\\Documents\\Visual Studio 2012\\Projects\\tours\\tours\\sql_string.txt";

            StreamWriter sw = new StreamWriter(File.Create(path));
            sw.WriteLine(textBox1.Text);
            sw.WriteLine(textBox2.Text);
            sw.WriteLine(textBox3.Text);
            sw.WriteLine(textBox4.Text);


            sw.Dispose();

Maybe this is not even good way to do that. I'm junior programmer so forgive if my idea is totally bad.

A connection string is: just a string. The simplest way to store it is: *as a string* - for example, `File.WriteAllText`, or as a setting in the config file. You mention "unsafe"; what is it that you want to protect against? is your concern people browsing the file? In which case: what type of security are you using? trusted/sspi? or sql server passwords? — Marc Gravell, Aug 06 '13 at 12:26
also see stackoverflow http://stackoverflow.com/questions/42115/app-config-connection-string-protection-error — lordkain, Aug 06 '13 at 12:26
@MarcGravell Thank you for your time, I thought that if anyone open this .txt file where I store data about initial catalogue, data source, id , password he can simply get access to this sql database, isn't it like that ? I'm not using any type of security yet, what would you recommend to me? Thank you so much for your time again. — Marek, Aug 06 '13 at 12:32
@Marek "I'm not using any type of security yet"... well, yes: you are. You are either using sql server authentication (if a password is included), or you are using integrated security (aka "trusted" aka "sspi"). There is no "none" option. Ultimately, in either case: if the user has access to the database, then *the user has access to the database*. If you *actually* care about security, then personally I'd be using a web-service as an abstraction layer - this avoids the issue of arbitrary sql being issued by malicious clients. Any security at the client is a myth. Treat clients as hostile. — Marc Gravell, Aug 06 '13 at 12:35
@MarcGravell Then I'm using Sql Server Authentication :). Sorry for that mistake. Would you please recommend me any link where can I learn more about "web-service as an abstraction layer". May I also ask what do you think of storing login details for access to the database? If it is encoded? Thank you so much for your time once again. It was pleasure for me to learn something. — Marek, Aug 06 '13 at 12:43

score 1 · Accepted Answer · answered Aug 06 '13 at 12:27

1

Yes you can encrypt the connection string. Protected Configuration model allows you to encrypt data using two Protected Configuration Providers. They are:

RSAProtectedConfigurationProvider: This is the default provider and uses the RSA Public Key Encryption algorithm to encrypt and decrypt data.

DataProtectionConfigurationProvider: This provider uses Windows Data Protection Application Programming Interface (DPAPI) to encrypt and decrypt data.

You can take a look here to see how it is done.

answered Aug 06 '13 at 12:27

Ehsan

31,833
6
56
65

Nice to see you again Ehsan. Do you think that this would work also with .txt files? Because I'm using it to read SqlConnection string right now. Thank you for your time. – Marek Aug 06 '13 at 12:52
@Marek no it won't. it works with web.config/app.config. Though you can write a very similar yourself for txt files – Ehsan Aug 06 '13 at 14:30

Best way to encode SqlConnection string information

1 Answers1