none
Connection string in web.config required for EF? RRS feed

  • Question

  • I created a C# ASP.NET WebForms app (MyApp) and then a referenced class library (MyLib).  I added the ADO.NET Entity Data Model to MyLib and was surprised to read that I needed to copy the connection string from the App.config in MyLib to also the Web.config in MyApp.

    Is this really necessary?  I would really like to not expose the connection string so publicly facing.  If I do have to have it there, how can I at least not have login credentials and grab from somewhere else?

    Thanks

    Thursday, April 2, 2015 11:24 AM

Answers

  • Not sure what Will is referring to but I found a solution (regardless of integrated security vs SQL login).  And on that, by the way, we thought integrated security would be more preferred by our security team but it was not.  The said it could lead to a hacker accessing the ACL or other and they preferred we work with SQL logins instead.

    In the MyModel.Context.Cs I changed the default to instead accept a string passed in:

    public partial class MyEntities : DbContext
    {
        //public MyEntities()
        //    : base("name=MyEntities")
        public CorEntities(string myConnection)
            : base(myConnection)
    

    I call what I need:

    MyEntities myEntities = new ConnectionClass().SelectMyEntities();
    

    Then, this code does the trick in building the connection string dynamically without having to have ANYTHING in the web.config file (because I do it from within a class library):

    public CorEntities SelectCorEntities()
            {

    // Initialize the connection string builder for the underlying provider.             System.Data.SqlClient.SqlConnectionStringBuilder sqlBuilder =                 new System.Data.SqlClient.SqlConnectionStringBuilder();             // Set the properties for the data source.             sqlBuilder.DataSource = Properties.Settings.Default.ServerName;             sqlBuilder.InitialCatalog = Properties.Settings.Default.DatabaseName;             sqlBuilder.UserID = Properties.Settings.Default.UserID;             sqlBuilder.Password = Properties.Settings.Default.Password;             sqlBuilder.IntegratedSecurity = false;             // Build the SqlConnection connection string. string providerString = sqlBuilder.ToString();             // Initialize the EntityConnectionStringBuilder.             System.Data.EntityClient.EntityConnectionStringBuilder entityBuilder =                 new System.Data.EntityClient.EntityConnectionStringBuilder();             //Set the provider name.             entityBuilder.Provider = "System.Data.SqlClient";             // Set the provider-specific connection string.             entityBuilder.ProviderConnectionString = providerString;             // Set the Metadata location.             entityBuilder.Metadata = @"res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl";             Console.WriteLine(entityBuilder.ToString());             MyEntities myEntities = newMyEntities(entityBuilder.ConnectionString);             return myEntities;

    }              

    • Marked as answer by fiverc Thursday, April 9, 2015 7:22 PM
    Thursday, April 9, 2015 7:21 PM

All replies

  • I created a C# ASP.NET WebForms app (MyApp) and then a referenced class library (MyLib).  I added the ADO.NET Entity Data Model to MyLib and was surprised to read that I needed to copy the connection string from the App.config in MyLib to also the Web.config in MyApp.

    Is this really necessary?  I would really like to not expose the connection string so publicly facing.  If I do have to have it there, how can I at least not have login credentials and grab from somewhere else?

    Thanks

    Yeah it's neccessay, becuase .NET looks for configuration information in the root.config of the application. For a Web application, that would be Web.config, and in a solution that creates an exe file, then programname.exe.config is the ruintime root.config that must be in the same location as the programname.exe so .NET can find it, which is built upon the successful build of the exe project and dervied from its project app.config. You can hae 10, 000 projects under the root project, like classlib projects with thier own app.config information, but .NET at program runtime is only look for root.config for all configuration information. So, everything needs to be in the root.config. The only time app.config is used in any project is in debug mode with VS.

    There are planty of articles on Bing and Google on how to encrypt a config file or parts of the config file, like the connectionstring.

    Thursday, April 2, 2015 1:56 PM
  • Thanks.  But, isn't there a way to have the connection string in the web.config file WITHOUT the password?  I saw that as an option i thought.  If so, how then to I authenticate "downstream" in the process?

    It just seems like a huge security issue to have connection string with password in a web.config file.

    Thanks.

    Thursday, April 2, 2015 6:21 PM
  • Thanks.  But, isn't there a way to have the connection string in the web.config file WITHOUT the password?  I saw that as an option i thought.  If so, how then to I authenticate "downstream" in the process?

    It just seems like a huge security issue to have connection string with password in a web.config file.

    Thanks.

    Yeah you can do that too, but that would be for a site  where the user was using thier Windows login credentials (Windows Authentication), which would not apply to a public facing site where the user would use Anonymous Authentication. Therefore, the connectionstring must have a user-id and psw known by the database to allow access to the databse on the behalf of the Anonymous user.
    Thursday, April 2, 2015 8:31 PM
  • Thanks.  But, isn't there a way to have the connection string in the web.config file WITHOUT the password?  I saw that as an option i thought.  If so, how then to I authenticate "downstream" in the process?

    It just seems like a huge security issue to have connection string with password in a web.config file.

    Thanks.

    Yes there is.  In order to do so, you will need to:

    1. Configure a user account in SQL server for the Windows account that the Application Pool is running under, and map that account to your database. 
    2. Configure your web application to connect to your SQL Server using Windows Authentication by using Integrated Security (i.e. connectionString="Data Source=.;Initial Catalog=<database name>;Integrated Security=SSPI;").

    Depending upon where you are deploying this and how the system(s) & network are architected, will determine which type of Windows account you should use for the Application Pool.

    If the application and SQL server are different machines, then you should use NetworkService (machine account) or a domain user account.  If they are on the same machine, then you can use ApplicationPoolIdentity (IIS APPPOOL\<app pool name>), LocalService, and LocalSystem as well.

    If you are not familiar with these types of accounts, their limitations, and their vulnerabilities, please talk with your security administrator and SQL administrator before choosing a specific account type.

    Take it easy,
    Will

    Saturday, April 4, 2015 10:17 AM
  • @Will

    You know this is s hobbist doing this, right?

    Saturday, April 4, 2015 5:36 PM
  • Not sure what Will is referring to but I found a solution (regardless of integrated security vs SQL login).  And on that, by the way, we thought integrated security would be more preferred by our security team but it was not.  The said it could lead to a hacker accessing the ACL or other and they preferred we work with SQL logins instead.

    In the MyModel.Context.Cs I changed the default to instead accept a string passed in:

    public partial class MyEntities : DbContext
    {
        //public MyEntities()
        //    : base("name=MyEntities")
        public CorEntities(string myConnection)
            : base(myConnection)
    

    I call what I need:

    MyEntities myEntities = new ConnectionClass().SelectMyEntities();
    

    Then, this code does the trick in building the connection string dynamically without having to have ANYTHING in the web.config file (because I do it from within a class library):

    public CorEntities SelectCorEntities()
            {

    // Initialize the connection string builder for the underlying provider.             System.Data.SqlClient.SqlConnectionStringBuilder sqlBuilder =                 new System.Data.SqlClient.SqlConnectionStringBuilder();             // Set the properties for the data source.             sqlBuilder.DataSource = Properties.Settings.Default.ServerName;             sqlBuilder.InitialCatalog = Properties.Settings.Default.DatabaseName;             sqlBuilder.UserID = Properties.Settings.Default.UserID;             sqlBuilder.Password = Properties.Settings.Default.Password;             sqlBuilder.IntegratedSecurity = false;             // Build the SqlConnection connection string. string providerString = sqlBuilder.ToString();             // Initialize the EntityConnectionStringBuilder.             System.Data.EntityClient.EntityConnectionStringBuilder entityBuilder =                 new System.Data.EntityClient.EntityConnectionStringBuilder();             //Set the provider name.             entityBuilder.Provider = "System.Data.SqlClient";             // Set the provider-specific connection string.             entityBuilder.ProviderConnectionString = providerString;             // Set the Metadata location.             entityBuilder.Metadata = @"res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl";             Console.WriteLine(entityBuilder.ToString());             MyEntities myEntities = newMyEntities(entityBuilder.ConnectionString);             return myEntities;

    }              

    • Marked as answer by fiverc Thursday, April 9, 2015 7:22 PM
    Thursday, April 9, 2015 7:21 PM
  • Hey fiverc,

    I'm not sure what your security team is referring to, as Integrated Security, using a domain service account w/o any user permissions, is the recommended method of accessing SQL Server from all web applications.  At the very least, if a hacker breaks in they will have the permissions of the application pool user account.  If that account doesn't have any user permissions, only has read access to the application folders and restricted access to the database, that's all they can affect, which is no different that the SQL Authentication route.

    Unfortunately, with your method, the username and password are stored in a location that is accessible with that application pool user account, and they are stored in plain text.  Any hacker who does get in, will easily be able to get this information with very little effort, giving him/her credentials to your SQL Server.  If you truly need to use SQL Authentication, then I would recommend encrypting the connection settings section in your web.config instead.

    Take it easy,
    Will

    Friday, April 10, 2015 3:48 PM