Change Connection String Password Programmatically

Change Connection String Password Programmatically

by jbrooks 6. December 2010 13:07

On one of our projects we have a requirement that we have to change our passwords every 90 day.  So I created a page where I can just click a button and it will generate a password, change the password on the database for the user in the connection string and then change the password in the web.config.

The page just has a button that say “Change Password” and a label called lblMessage to show the results.

The code behind just calls the class that does all of the work and then shows a message:

const string CONNSTRINGNAME = "SampleAppConnString";
const string WEBCONFIGFILE = "~/Web.Config";
protected void btnChangePassword_Click(object sender, EventArgs e)
if (cConnStringPasswordChanger.ChangePassword(HttpContext.Current.Server.MapPath(WEBCONFIGFILE), 
    this.lblMessage.Text = "Password has been changed";
    this.lblMessage.Text = "Password has NOT been changed";

Now for the class that does all of the work:

public static class cConnStringPasswordChanger
    public static bool ChangePassword(string webConfig, string ConnStringName)
        return ChangePassword(webConfig, ConnStringName, 
                Guid.NewGuid().ToString().Replace("-", "").Substring(0, 9));
    // 1. Open the web.config.
    // 2. Change the connString to have the new password.
    // 3. Change the password on the database.
    // 4. Commit the web.config change.
    public static bool ChangePassword(string webConfig, string ConnStringName, 
        string newPassword)
        bool changed = false;
        string oldConnString;
        FileInfo fi = new FileInfo(webConfig);
        if (fi.IsReadOnly)
            File.SetAttributes(webConfig, FileAttributes.Normal);
        fi = null;
        XmlDocument cfgDoc = new XmlDocument();
        XmlNode connNode = cfgDoc.SelectSingleNode("//connectionStrings");
        XmlNode myNode = connNode.SelectSingleNode("//add[@name='" + ConnStringName + "']");
        oldConnString = myNode.Attributes["connectionString"].Value;
        string oldPassword = getValue(oldConnString, "Password");
        string newConnString = oldConnString.Replace(oldPassword, newPassword);
        myNode.Attributes["connectionString"].Value = newConnString;
        string userId = getValue(newConnString, "User Id");
        XmlTextWriter writer = new XmlTextWriter(webConfig, null);
        writer.Formatting = Formatting.Indented;
        // last possible second change it on the database.
        changePassword(oldConnString, userId, oldPassword, newPassword);
            changed = true;
        catch (Exception ex)
            // error saving web.config change, so change it back on the database.
            changePassword(newConnString, userId, newPassword, oldPassword);
        writer = null;
        cfgDoc = null;
        return changed;
    // This function is passed a connection string like:
    // "data source=.\SQLEXPRESS;Initial Catalog=MyDb;User Id=McUser;Password=c99c0472e;"
    // and a partName like "User Id" or "Password".
    // it returns the value for that partName.
    private static string getValue(string connString, string partName)
        int partStart = connString.ToLower().IndexOf(partName.ToLower());
        int partEndSemi; 
        int partEndQuote;
        if (partStart > -1)
            partStart += partName.Length + 1;
            throw new Exception(partName + " not found in connection string");
        partEndSemi = connString.Substring(partStart).IndexOf(";");
        partEndQuote = connString.Substring(partStart).IndexOf("\"");
        if (partEndQuote == -1)
            partEndQuote = connString.Length - partStart - 1;
        if (partEndSemi == -1)
            partEndSemi = connString.Length - partStart - 1;
        return connString.Substring(partStart, Math.Min(partEndQuote, partEndSemi));
    // Call the database to change the password.
    private static bool changePassword(string connString, string loginName, 
        string oldPassword, string newPassword)
        bool changed = false;
        using (SqlConnection connection = new SqlConnection(connString))
            SqlCommand command = new SqlCommand("dbo.sp_password", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@loginame", SqlDbType.NVarChar, 128).Value = loginName;
            command.Parameters.Add("@old", SqlDbType.NVarChar, 128).Value = oldPassword;
            command.Parameters.Add("@new", SqlDbType.NVarChar, 128).Value = newPassword;
            changed = true;
        return changed;


ASP.Net | Development | SQL

Add comment

  Country flag

  • Comment
  • Preview

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.