title: What is the best method for changing a web.config connectionstring at runtime?
body: <p>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:</p>
  
  <ul>
  <li>Application uses Forms authentication, not Windows authentication</li>
  <li>There will be a Login page where the user supplies their SQL Server login ID and password</li>
  <li>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="&lt;%$ ConnectionStrings:MyDB %>"</li>
  <li>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.</li>
  </ul>
  
  <p>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.</p>
  
  <hr>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I would love to require Windows authentication, but unfortunately some implementations of this application will require SQL authentication.</p>
  
  <p>If I could just set the connection string when I declare the SQLDataSource controls like this, it would be a snap:</p>
  
  <pre><code>  &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" 
      ConnectionString = "&lt;%= Session("MyConnectionString") %&gt;"
      SelectCommand="SELECT * FROM [Customers]"&gt;
    &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>But I get an error because it doesn't like &lt;% %> 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?</p>
  
  <p>Thanks very much for everyone's help!</p>