SqlConnection.ChangePassword Method
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Changes the SQL Server password.
ChangePassword(String, SqlCredential, SecureString) |
Changes the SQL Server password for the user indicated in the SqlCredential object. |
ChangePassword(String, String) |
Changes the SQL Server password for the user indicated in the connection string to the supplied new password. |
Changes the SQL Server password for the user indicated in the SqlCredential object.
public:
static void ChangePassword(System::String ^ connectionString, System::Data::SqlClient::SqlCredential ^ credential, System::Security::SecureString ^ newPassword);
public:
static void ChangePassword(System::String ^ connectionString, System::Data::SqlClient::SqlCredential ^ credential, System::Security::SecureString ^ newSecurePassword);
public static void ChangePassword (string connectionString, System.Data.SqlClient.SqlCredential credential, System.Security.SecureString newPassword);
public static void ChangePassword (string connectionString, System.Data.SqlClient.SqlCredential credential, System.Security.SecureString newSecurePassword);
static member ChangePassword : string * System.Data.SqlClient.SqlCredential * System.Security.SecureString -> unit
static member ChangePassword : string * System.Data.SqlClient.SqlCredential * System.Security.SecureString -> unit
Public Shared Sub ChangePassword (connectionString As String, credential As SqlCredential, newPassword As SecureString)
Public Shared Sub ChangePassword (connectionString As String, credential As SqlCredential, newSecurePassword As SecureString)
Parameters
- connectionString
- String
The connection string that contains enough information to connect to a server. The connection string should not use any of the following connection string keywords: Integrated Security = true
, UserId
, or Password
; or ContextConnection = true
.
- credential
- SqlCredential
A SqlCredential object.
- newPasswordnewSecurePassword
- SecureString
The new password.newPassword
must be read only. The password must also comply with any password security policy set on the server (for example, minimum length and requirements for specific characters).
Exceptions
The connection string contains any combination of UserId
, Password
, or Integrated Security=true
.
-or-
The connection string contains Context Connection=true
.
-or-
newSecurePassword
(or newPassword
) is greater than 128 characters.
-or-
newSecurePassword
(or newPassword
) is not read only.
-or-
newSecurePassword
(or newPassword
) is an empty string.
One of the parameters (connectionString
, credential
, or newSecurePassword
) is null.
See also
Applies to
.NET 8 (package-provided) and other versions
Product | Versions |
---|---|
.NET | 6 (package-provided), 8 (package-provided) |
.NET Framework | 4.5, 4.5.1, 4.5.2, 4.6, 4.6.1, 4.6.2, 4.7 (package-provided), 4.7, 4.7.1 (package-provided), 4.7.1, 4.7.2 (package-provided), 4.7.2, 4.8 (package-provided), 4.8, 4.8.1 |
.NET Standard | 2.0 (package-provided) |
Changes the SQL Server password for the user indicated in the connection string to the supplied new password.
public:
static void ChangePassword(System::String ^ connectionString, System::String ^ newPassword);
public static void ChangePassword (string connectionString, string newPassword);
static member ChangePassword : string * string -> unit
Public Shared Sub ChangePassword (connectionString As String, newPassword As String)
Parameters
- connectionString
- String
The connection string that contains enough information to connect to the server that you want. The connection string must contain the user ID and the current password.
- newPassword
- String
The new password to set. This password must comply with any password security policy set on the server, including minimum length, requirements for specific characters, and so on.
Exceptions
The connection string includes the option to use integrated security.
Or
The newPassword
exceeds 128 characters.
Either the connectionString
or the newPassword
parameter is null.
Examples
Warning
Microsoft does not recommend providing your user name and password directly, because it's an insecure pattern. Where possible, use more secure authentication flows, such as Managed Identities for Azure resources, or Windows authentication for SQL Server.
The following is a simple example of changing a password:
class Program {
static void Main(string[] args) {
System.Data.SqlClient.SqlConnection.ChangePassword(
"Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password",
"new_password");
}
}
Module Module1
Sub Main()
System.Data.SqlClient.SqlConnection.ChangePassword(
"Data Source=a_server;Initial Catalog=a_database;UID=user;PWD=old_password",
"new_password")
End Sub
End Module
The following console application demonstrates the issues involved in changing a user's password because the current password has expired.
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
try
{
DemonstrateChangePassword();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
}
private static void DemonstrateChangePassword()
{
// Retrieve the connection string. In a production application,
// this string should not be contained within the source code.
string connectionString = GetConnectionString();
using (SqlConnection cnn = new SqlConnection())
{
for (int i = 0; i <= 1; i++)
{
// Run this loop at most two times. If the first attempt fails,
// the code checks the Number property of the SqlException object.
// If that contains the special values 18487 or 18488, the code
// attempts to set the user's password to a new value.
// Assuming this succeeds, the second pass through
// successfully opens the connection.
// If not, the exception handler catches the exception.
try
{
cnn.ConnectionString = connectionString;
cnn.Open();
// Once this succeeds, just get out of the loop.
// No need to try again if the connection is already open.
break;
}
catch (SqlException ex)
{
if (i == 0 && ((ex.Number == 18487) || (ex.Number == 18488)))
{
// You must reset the password.
connectionString =
ModifyConnectionString(connectionString,
GetNewPassword());
}
else
{
// Bubble all other SqlException occurrences
// back up to the caller.
throw;
}
}
}
SqlCommand cmd = new SqlCommand(
"SELECT ProductID, Name FROM Product", cnn);
// Use the connection and command here...
}
}
private static string ModifyConnectionString(
string connectionString, string NewPassword)
{
// Use the SqlConnectionStringBuilder class to modify the
// password portion of the connection string.
SqlConnectionStringBuilder builder =
new SqlConnectionStringBuilder(connectionString);
builder.Password = NewPassword;
return builder.ConnectionString;
}
private static string GetNewPassword()
{
// In a real application, you might display a modal
// dialog box to retrieve the new password. The concepts
// are the same as for this simple console application, however.
Console.Write("Your password must be reset. Enter a new password: ");
return Console.ReadLine();
}
private static string GetConnectionString()
{
// For this demonstration, the connection string must
// contain both user and password information. In your own
// application, you might want to retrieve this setting
// from a config file, or from some other source.
// In a production application, you would want to
// display a modal form that could gather user and password
// information.
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(
"Data Source=(local);Initial Catalog=AdventureWorks");
Console.Write("Enter your user id: ");
builder.UserID = Console.ReadLine();
Console.Write("Enter your password: ");
builder.Password = Console.ReadLine();
return builder.ConnectionString;
}
}
Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main()
Try
DemonstrateChangePassword()
Catch ex As Exception
Console.WriteLine("Error: " & ex.Message)
End Try
Console.WriteLine("Press ENTER to continue...")
Console.ReadLine()
End Sub
Private Sub DemonstrateChangePassword()
Dim connectionString As String = GetConnectionString()
Using cnn As New SqlConnection()
For i As Integer = 0 To 1
' Run this loop at most two times. If the first attempt fails,
' the code checks the Number property of the SqlException object.
' If that contains the special values 18487 or 18488, the code
' attempts to set the user's password to a new value.
' Assuming this succeeds, the second pass through
' successfully opens the connection.
' If not, the exception handler catches the exception.
Try
cnn.ConnectionString = connectionString
cnn.Open()
' Once this succeeds, just get out of the loop.
' No need to try again if the connection is already open.
Exit For
Catch ex As SqlException _
When (i = 0 And (ex.Number = 18487 Or ex.Number = 18488))
' You must reset the password.
connectionString = ModifyConnectionString( _
connectionString, GetNewPassword())
Catch ex As SqlException
' Bubble all other SqlException occurrences
' back up to the caller.
Throw
End Try
Next
Dim cmd As New SqlCommand("SELECT ProductID, Name FROM Product", cnn)
' Use the connection and command here...
End Using
End Sub
Private Function ModifyConnectionString( _
ByVal connectionString As String, ByVal NewPassword As String) As String
' Use the SqlConnectionStringBuilder class to modify the
' password portion of the connection string.
Dim builder As New SqlConnectionStringBuilder(connectionString)
builder.Password = NewPassword
Return builder.ConnectionString
End Function
Private Function GetNewPassword() As String
' In a real application, you might display a modal
' dialog box to retrieve the new password. The concepts
' are the same as for this simple console application, however.
Console.Write("Your password must be reset. Enter a new password: ")
Return Console.ReadLine()
End Function
Private Function GetConnectionString() As String
' For this demonstration, the connection string must
' contain both user and password information. In your own
' application, you might want to retrieve this setting
' from a config file, or from some other source.
' In a production application, you would want to
' display a modal form that could gather user and password
' information.
Dim builder As New SqlConnectionStringBuilder( _
"Data Source=(local);Initial Catalog=AdventureWorks")
Console.Write("Enter your user id: ")
builder.UserID = Console.ReadLine()
Console.Write("Enter your password: ")
builder.Password = Console.ReadLine()
Return builder.ConnectionString
End Function
End Module
Remarks
When you are using SQL Server on Windows Server, you can take advantage of functionality that lets the client application supply both the current and a new password in order to change the existing password. Applications can implement functionality such as prompting the user for a new password during initial login if the old one has expired, and this operation can be completed without administrator intervention.
Warning
Microsoft does not recommend providing your user name and password directly, because it's an insecure pattern. Where possible, use more secure authentication flows, such as Managed Identities for Azure resources, or Windows authentication for SQL Server.
The ChangePassword method changes the SQL Server password for the user indicated in the supplied connectionString
parameter to the value supplied in the newPassword
parameter. If the connection string includes the option for integrated security (that is, "Integrated Security=True" or the equivalent), an exception is thrown.
To determine that the password has expired, calling the Open method raises a SqlException. In order to indicate that the password that is contained within the connection string must be reset, the Number property for the exception contains the status value 18487 or 18488. The first value (18487) indicates that the password has expired and the second (18488) indicates that the password must be reset before logging in.
This method opens its own connection to the server, requests the password change, and closes the connection as soon as it has completed. This connection is not retrieved from or returned to the SQL Server connection pool.
See also
- Connection Strings (ADO.NET)
- Connecting to a Data Source (ADO.NET)
- Using the .NET Framework Data Provider for SQL Server
- ADO.NET Overview
Applies to
.NET 8 (package-provided) and other versions
Product | Versions |
---|---|
.NET | 6 (package-provided), 8 (package-provided) |
.NET Framework | 2.0, 3.0, 3.5, 4.0, 4.5, 4.5.1, 4.5.2, 4.6, 4.6.1, 4.6.2, 4.7 (package-provided), 4.7, 4.7.1 (package-provided), 4.7.1, 4.7.2 (package-provided), 4.7.2, 4.8 (package-provided), 4.8, 4.8.1 |
.NET Standard | 2.0 (package-provided) |
.NET feedback
.NET is an open source project. Select a link to provide feedback: