SqlConnection.ChangePassword 方法

定義

命名空間:

System.Data.SqlClient

組件:

System.Data.SqlClient.dll

套件:

System.Data.SqlClient v4.9.0

變更 SQL Server 密碼。

多載

ChangePassword(String, SqlCredential, SecureString)	變更 SqlCredential 物件中所指出使用者的 SQL Server 密碼。
ChangePassword(String, String)	將連接字串中指出之使用者的 SQL Server 密碼變更為提供的新密碼。

ChangePassword(String, SqlCredential, SecureString)

來源:

System.Data.SqlClient.notsupported.cs

變更 SqlCredential 物件中所指出使用者的 SQL Server 密碼。

public:
 static void ChangePassword(System::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 newPassword);

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)

參數

connectionString

String

包含足夠資訊的連接字串，可連線到伺服器。 連接字串不應使用下列任何連接字串關鍵詞：Integrated Security = true、UserId或 Password;或 ContextConnection = true。

credential

SqlCredential

SqlCredential 物件。

newPassword

SecureString

新密碼。 newPassword 必須是唯讀的。 密碼也必須符合伺服器上設定的任何密碼安全策略（例如，特定字元的長度下限和需求）。

例外狀況

ArgumentException

連接字串包含 UserId、Password或 Integrated Security=true的任何組合。

-或-

連接字串包含 Context Connection=true。

-或-

newSecurePassword （或 newPassword） 大於 128 個字元。

-或-

newSecurePassword（或 newPassword）不是唯讀的。

-或-

newSecurePassword （或 newPassword） 是空字串。

ArgumentNullException

其中一個參數 （connectionString、 credential或 newSecurePassword） 為 null。

另請參閱

• ADO.NET 概觀

ChangePassword(String, String)

來源:

System.Data.SqlClient.notsupported.cs

將連接字串中指出之使用者的 SQL Server 密碼變更為提供的新密碼。

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)

參數

connectionString

String

包含足夠資訊的連接字串，可連線到您想要的伺服器。 連接字串必須包含使用者識別碼和目前的密碼。

newPassword

String

要設定的新密碼。 此密碼必須符合伺服器上設定的任何密碼安全策略，包括長度下限、特定字元的需求等等。

例外狀況

ArgumentException

連接字串包含使用整合式安全性的選項。

或

newPassword 超過 128 個字元。

ArgumentNullException

connectionString 或 newPassword 參數為 null。

範例

警告

Microsoft不建議直接提供您的使用者名稱和密碼，因為這是不安全的模式。 可能的話，請使用更安全的驗證流程，例如 azure 資源的 受控識別，或 SQL Server Windows 驗證。

以下是變更密碼的簡單範例：

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

下列主控台應用程式示範變更使用者密碼時所涉及的問題，因為目前的密碼已過期。

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

備註

當您在 Windows Server 上使用 SQL Server 時，您可以利用可讓用戶端應用程式同時提供目前和新密碼的功能，以變更現有的密碼。 如果舊密碼已過期，應用程式可以實作功能，例如在初始登入期間提示使用者輸入新密碼，而且不需要系統管理員介入即可完成這項作業。

警告

Microsoft不建議直接提供您的使用者名稱和密碼，因為這是不安全的模式。 可能的話，請使用更安全的驗證流程，例如 azure 資源的 受控識別，或 SQL Server Windows 驗證。

ChangePassword 方法會將所提供之 connectionString 參數中所指示之使用者的 SQL Server 密碼變更為 newPassword 參數中提供的值。 如果連接字串包含整合式安全性的選項（也就是「整合式安全性=True」或對等專案），則會擲回例外狀況。

若要判斷密碼已過期，呼叫 Open 方法會引發 SqlException。 若要指出連接字串中包含的密碼必須重設，例外狀況的 Number 屬性會包含狀態值 18487 或 18488。 第一個值 （18487） 表示密碼已過期，而第二個值 （18488） 表示在登入之前必須重設密碼。

這個方法會開啟自己與伺服器的連線、要求密碼變更，並在連線完成後立即關閉。 此連線不會從 SQL Server 連接集區擷取或傳回。

另請參閱

• 連接字串 （ADO.NET）
• 連接到數據來源 （ADO.NET）
• 使用 .NET Framework Data Provider for SQL Server
• ADO.NET 概觀