How to Protect My Configuration File

Question

I am writing an SQL Server application in C# built in Visual Studio. It is a Windows Forms Application. The program will be installed on the network where users will run it.

The problem I am struggling with is how to manage the configuration file. It has the server username and password there for all to see. I tried Click Once and an encryption scheme but they both required the programs to run on the computer the program was running from. It failed when I tried to run it from a workstation. This is different from How do I avoid having the database password stored in plaintext in sourcecode? because all of those solutions either suggested using integrated security or machine based encryption. Neither of those options would work for me.

Any help would be deeply appreciated.

Answer 1

If you mean data at app.config it is simple! You have to use these two classes:

EntityConnectionStringBuilder

https://msdn.microsoft.com/en-us/library/system.data.entityclient.entityconnectionstringbuilder(v=vs.110).aspx And

SqlConnectionStringBuilder

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx

I learn it from this page: Programmatic Connection Strings in Entity Framework 6 It is very good guide. In any cases, That link didn't help you!? Just Google something like this:

C# define connection string at runtime

After you put all connection string inside your code, you can go and delete any sensitive data from connectionStrings tag of app.config file because your app will not use it anymore! Then compile your code again.

If you are using DB First in EF, then you can check this Guide too: How to set Connection String with Entity Framework

UPDATED:

I added two of my Classes that I manage and create connection string with them programmatic (Dynamic), One is belong to my Entity Framework project that I used SQL Server Compact Edition (SQL Server CE) and the second one belong to another Entity Framework Project That I used SQL Server Express 2014 with SQL Server authentication (used sa username). I will leave both method here in case anyone need them:

This belong to my SQL Server CE project:

public static string GetDBConnectionString(string dataParentPath = "")
{
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
    SqlCeConnectionStringBuilder sqlCEBuilder = new SqlCeConnectionStringBuilder();

    if (string.IsNullOrEmpty(dataParentPath) == true)
        dataParentPath = @"C:\MyDBFolder\CMS.sdf";

    sqlCEBuilder.DataSource = dataParentPath;
    sqlCEBuilder.Password = "12345687";
    sqlCEBuilder.MaxDatabaseSize = 4090;

    entityBuilder.Metadata = "res://*/CMS.csdl|res://*/CMS.ssdl|res://*/CMS.msl";
    entityBuilder.ProviderConnectionString = sqlCEBuilder.ToString();
    entityBuilder.Provider = "System.Data.SqlServerCe.4.0";

    return entityBuilder.ToString();
}

This belongs to my SQL Server Express project with SQL Server authentication:

using System;
using System.Collections.Generic;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CMS
{
    class mySettings
    {
        public static string GetDBConnectionString()
        {
            // **************************************************
            // This is my "ConnectionString" from App.config file.
            // <connectionStrings>
            //      <add name="CMSEntities" 
            //          connectionString=
            //              "metadata=res://*/CMS.csdl|res://*/CMS.ssdl|res://*/CMS.msl
            //                  ;provider=System.Data.SqlClient
            //                  ;provider connection string=&quot
            //                  ;data source=MY-PC\SQLEXPRESS
            //                  ;initial catalog=CMS
            //                  ;user id=sa
            //                  ;password=12345687
            //                  ;MultipleActiveResultSets=True
            //                  ;App=EntityFramework
            //              &quot;"
            //      providerName="System.Data.EntityClient" />
            //</connectionStrings>
            // **************************************************

            string metaData = "res://*/CMS.csdl|res://*/CMS.ssdl|res://*/CMS.msl";
            string providerName = "System.Data.SqlClient";

            string dataSource = @"MY-PC\SQLEXPRESS";
            string databaseName = "CMS"; // = InitialCatalog
            string userID = "sa";
            string password = "12345687";
            string appName = "EntityFramework";

            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

            // = = = = = = = = = = = = = = = =
            sqlBuilder.DataSource = dataSource;
            sqlBuilder.InitialCatalog = databaseName;
            sqlBuilder.MultipleActiveResultSets = true;
            sqlBuilder.UserID = userID;
            sqlBuilder.Password = password;
            sqlBuilder.ApplicationName = appName;


            // = = = = = = = = = = = = = = = =
            entityBuilder.Provider = providerName;
            entityBuilder.Metadata = metaData;
            entityBuilder.ProviderConnectionString = sqlBuilder.ConnectionString;

            return entityBuilder.ToString();
        }
    }
}

As you can see, My database in both project have same name "CMS" so its Entities will be named "CMSEntities". Now! you have to override its DbContext constructor. It is Important but easiest part! Better description than mine is from this page "http://www.cosairus.com/Blog/2015/3/10/programmatic-connection-strings-in-entity-framework-6":

Now your Entity Model extends from DbContext and DbContext provides a constructor to pass in a Connection String, but your Entity Model does not overload those constructors for you. In order to access the constructor overload, you will need to create a new class partial for your Entity Model database context in the same namespace as your Entity Model with the required constructor signature. Pro Tip: be sure to name the filename of the cs file a different name than the Entity Model database context in the event that future generated code does not overwrite your changes.

So I build a class at root of my Project, The class must be partial:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CMS  // Your Project Namespace
{
    public partial class CMSEntities : DbContext
    {
        public CMSEntities(string connectionString)
            : base(connectionString)
        {
        }
    }
}

and Anytime I wanna access to my Database I will use this code:

    using (CMSEntities db = new CMSEntities(CMSSettings.GetDBConnectionString()))
    {
        // Do your DB stuff here...
    }

I hope It help you or others which I learn all of that from this site "stackoverflow" and users.

Good Luck

Answer 2

This is why software developers created multi-tier designs that include middleware services like web services. Web services can be hosted in IIS and the windows account and password can be configured into the Application Identity section of the application connection pool. Then the web.config connection string can be configured with trusted_connection=true. Configuring it this way uses the Windows Data Protection API to protect the identities.

Answer 3

Don't store passwords in plain text. Period. Full stop.

You should take a cue from SQL Server. Yes, you can store usernames in passwords in plain text in a web/app.config. But for Production servers you never should. Instead for Production deployments you should have a config that uses Integrated Security. That allows for elevated access by accessing credentials which are handled securely by Windows rather than insecurely in a config file.

Similarly, you should use something like WindowsIdentity, or OpenId. Then you can pass around auth tokens in your code rather than storing credentials in plain text.