title: Changing connection string at runtime for OData/WCF Data Service which uses basic authentication
body: <p>I have an ODATA services with a single schema. These point to a development database, and is served through a WCF Data Service which is then used by clients running Excel/Powerpivot to fetch their own data for reports and such.</p>
  
  <p>The service is secured at runtime through pretty much the same basic authentication explained here: <a href="http://msdn.microsoft.com/en-us/data/gg192997" rel="nofollow">http://msdn.microsoft.com/en-us/data/gg192997</a></p>
  
  <p>Now how this needs to work in the live environment is sit on the server and connect to different databases based on the username/password supplied. the Users will be typing in  'username@clientID' and 'password'. 'username@clientID' is then split()  and username/password is checked against the SQL database. But the database server URL to check against will be determined by ClientID.</p>
  
  <p>Also, once it is authorized the WCF data service needs to return data from the Database corresponding to the ClientID.</p>
  
  <p>The approach I tried was to modify the connection string in the web.config file, but this doesn't work because it says the file is read-only. I'm not even sure if this would have worked at all. What I need to do is get the EDMX/WCF Data service to return the data from the correct database. Here's what I tried to do:</p>
  
  <pre><code>    private static bool TryAuthenticate(string user, string password, out IPrincipal principal)
      {
  
          Configuration myWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
          myWebConfig.AppSettings.Settings["test"].Value = "Hello";
          myWebConfig.Save();
  
          string newConnStr = myWebConfig.ConnectionStrings.ConnectionStrings["IntelCorpEntities"].ToString();
          newConnStr.ToString().Replace("SERGEIX01", "SERVERX01");
          myWebConfig.ConnectionStrings.ConnectionStrings["IntelCorpEntities"].ConnectionString = newConnStr;            
          myWebConfig.Save();
  
          if (user.ToLower().Equals("admin") &amp;&amp; password.Equals("password"))
          {
              principal = new GenericPrincipal(new GenericIdentity(user), new string[] { "Users" });
              return true;
          }
          else
          {
              principal = null;
              return false;
          }
      }
  </code></pre>