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), 
        CONNSTRINGNAME))
    this.lblMessage.Text = "Password has been changed";
else
    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();
        cfgDoc.Load(webConfig);
 
        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);
        try
        {
            cfgDoc.WriteTo(writer);
            writer.Flush();
            writer.Close();
            changed = true;
        }
        catch (Exception ex)
        {
            // error saving web.config change, so change it back on the database.
            changePassword(newConnString, userId, newPassword, oldPassword);
            throw;
        }
 
        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;
        else
            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;
            command.Connection.Open();
            command.ExecuteNonQuery();
            changed = true;
        }
 
 
        return changed;
 
    }
}
 

Tags:

ASP.Net | Development | SQL