What is the best method for changing a web.config connectionstring at runtime?

Question

I'm pretty new to the ASP.NET world so I'm having a little trouble coming up with the best way to configure a connection string at runtime and have the entire application use that connection string. Here is a little more info on the application that I plan to build:

• Application uses Forms authentication, not Windows authentication
• There will be a Login page where the user supplies their SQL Server login ID and password
• For simplicity, I would like to have all the SQLDataSource controls point to a web.config connection string. This would be done at design time rather than setting them programatically. So, they would have a property like this: ConnectionString="<%$ ConnectionStrings:MyDB %>"
• I'd like to find a way to change the "MyDB" connection string at runtime so it uses the login id and password that the user provided. But I do NOT want this saved to web.config. It should only be active for that user's session.

What is the "standard" way that people usually do this? I assume one method would be to create a Session variable with the connection string and then programatically change the ConnectionString property of every SQLDataSource control during page load. But I was hoping to avoid that if possible.

Thanks!

Answer 1

Without saving it to web.config there is no way to do this.

Answer 2

Load the Web.config connection string to a session variable in Session_Start. If user provided his/her own credentials, update them in the session variable. Otherwise, the defaults (web.config values) will be in effect.

Answer 3

I would say you have a few options, some work with what you have, others would require you to change things.

1. Store the default connection in the web.config, load it into session, use it until the user is logged in, then update the value in session later.

2. Implement windows authentication and identity impersonation and setup the windows accounts with access to the SQL Server box.

If you go with item number one, create a shared helper function that will get the connection, check session first, then load from web.config. That way all of your datasources can still be bound at design time.

NOTE: this is NOT a typical situation, and there are performance implications of using multiple SQL Server accounts for connection, such as the lack of the ability to utilize connection pooling among other items.

Answer 4

I don't recommend you do this, because you will have scalability problems. The web application won't be able to use connection pooling and will open as many connections as you have users accessing it. You will have many real connection opening/closing as well.

If you decide to proceed:

• If you can use windows authentication, use impersonation+integrated mode for the sql connection. Use windows groups to set up permissions on sql server.
• You can recursively access all items on the page, looking for sqldatasources, and apply the connection to each of them. You can attach to the oninit of the page from the global.asax, so you could apply it to all pages.

Why do you want to do it this way? What are the constraints involved? (usually a trusted subsystem model is used on web applications i.e. authentication/authorization is done at the web app level/or at a separate business tier)

Answer 5

As Mitch says, you lose connection pooling if you have users login in with different credentials (if this causes different connection strings.)

If you're just worried about separating admin users from normal users, just have 2 connection strings, one for admins and one for normal users. Use asp.net role providers to provide appropriate permissions to users.

Answer 6

Since a number of people asked about why I would want to use a unique connection for each user and were concerned about the lack of pooling, I figured I would comment on that here rather than comment on each individual response.

The nature of this application requires that every user connect to the database under their own account. The back-end security is tied to their user account so we can't use generic accounts like "user" and "administrator". We also need to know the specific identity of each user for auditing control. The application usually only has 10 to 20 users, so the lack of pooling isn't a concern. We could debate the merits of this approach another time, but unfortunately I don't have an option here - the project requires that each user connect to the database under their own account.

I would love to require Windows authentication, but unfortunately some implementations of this application will require SQL authentication.

If I could just set the connection string when I declare the SQLDataSource controls like this, it would be a snap:

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString = "<%= Session("MyConnectionString") %>"
    SelectCommand="SELECT * FROM [Customers]">
  </asp:SqlDataSource>

But I get an error because it doesn't like <% %> tags there. If I can't do this when declaring the control, what is the easiest way to do this programatically for every SQLDataSource control in the application?

Thanks very much for everyone's help!

Answer 7

I have a similar situation. I set the connection string to a default connection string from the web.config. Then in code behind, you can set the connection string to a different value. You must do this in Page_Load, before the data source selecting event fires, like so....

SqlDataSource1.ConnectionString = ConnectionString;

If you want to make sure that the default is not used, you can make the default a non-working connection string and handle the exception, or just check if it is the default and throw an error if so. You can also just check if the user is logged in during the selecting event of your data source and cancel the event if not...

Hope this helps...

Answer 8

You can use an aspect programming library like Postsharp, to change the connection string dynamically at runtime.

Answer 9

If you are not wanting to delve into code behind, there is another way you can do this.

First read this article on expression builders. One of my favorite things to bring into to my web-apps!

Now for some code:

First make a class in your project that contains the following:

using System;
using System.CodeDom;
using System.Web.UI;
using System.Web.Compilation;

namespace MyNamespace.Web.Compilation
{
    [ExpressionPrefix("code")]
    public class CodeExpressionBuilder : ExpressionBuilder
    {
     public override CodeExpression GetCodeExpression(BoundPropertyEntry entry,
        object parsedData, ExpressionBuilderContext context)
     {
      return new CodeSnippetExpression(entry.Expression);
     }
    }

}

Then, in web.config register the Expression Builder as follows

...
<compilation debug="false">
  <expressionBuilders>
    <add expressionPrefix="Code" type="MyNamespace.Web.Compilation.CodeExpressionBuilder"/>
  </expressionBuilders>
</compilation>
...

(all code above from taken from here and modified ever so slightly)

Finally change your SqlDataSource to the following (C#):

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString='<%$ code: (string)Session["MyConnectionString"] ?? ConfigurationManageer.ConnectionStrings["myDefaultConn"].ConnectionString %>'
    SelectCommand="SELECT * FROM [Customers]">
</asp:SqlDataSource>

If you wanted (and I would recommend) creating a static class that handles figuring out the connection string for you say something like:

public static ConnectionManager
{
   public static string GetConnectionString()
   {
      return HttpContext.Current.Session["MyConnectionString"] as string ??
             ConfigurationManager.ConnectionStrings["DefaultConnectionStr"].ConnectionString;
   }
}

Then your SqlDataSource would be

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString='<%$ code: ConnectionManager.GetConnectionString() %>'
    SelectCommand="SELECT * FROM [Customers]">
</asp:SqlDataSource>

That way if you ever need to change how get a connection string you can do it one place!

Answer 10

A method I am using (I forget where I learned abbout it) is to add a SettingsLoaded handler at app startup time, then set the new connection string in the handler. I haven't tried this in ASP.NET, just a local app, so your mileage may vary:

Settings.Default.SettingsLoaded += new System.Configuration.SettingsLoadedEventHandler(Default_SettingsLoaded);

void Default_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e) { Settings.Default["ConnectionString"] = "my new connection string"; }

Answer 11

Best pratically way to use Session State depends on your events. I have faced the issue like this thread. I have solved my issue using the Josh' answer.