共用方式為


SqlConnection.ChangePassword 方法

定義

變更 SQL Server 密碼。

多載

ChangePassword(String, String)

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

ChangePassword(String, SqlCredential, SecureString)

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

ChangePassword(String, String)

將連接字串中指示的使用者 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

連接字串,包含的資訊足以連接至您要的伺服器。 連接字串必須包含使用者 ID 和目前的密碼。

newPassword
String

要設定的新密碼。 這個密碼必須符合伺服器上設定的任何密碼安全性原則,包括最小長度、特定字元的需求等。

例外狀況

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

Or

newPassword 超過 128 個字元。

可能是 connectionStringnewPassword 參數為 null。

範例

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

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 時,開發人員可以利用可讓用戶端應用程式同時提供目前和新密碼的功能,以變更現有的密碼。 如果舊密碼已過期,應用程式可以實作功能,例如在初始登入期間提示使用者輸入新密碼,而且此作業可以完成,而不需要系統管理員介入。

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

若要判斷密碼已過期,呼叫 方法會OpenSqlException引發 。 若要指出必須重設 連接字串 中包含的密碼,Number例外狀況的 屬性會包含狀態值 18487 或 18488。 第一個值 (18487) 表示密碼已過期,而第二個 (18488) 表示必須先重設密碼,才能登入。

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

另請參閱

適用於

ChangePassword(String, SqlCredential, SecureString)

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

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 newSecurePassword);
static member ChangePassword : string * System.Data.SqlClient.SqlCredential * System.Security.SecureString -> unit
Public Shared Sub ChangePassword (connectionString As String, credential As SqlCredential, newSecurePassword As SecureString)

參數

connectionString
String

連接字串,包含的資訊足以連接至伺服器。 連接字串不應使用任何下列的連接字串關鍵字:Integrated Security = trueUserIdPassword;或者是 ContextConnection = true

credential
SqlCredential

SqlCredential 物件。

newPasswordnewSecurePassword
SecureString

新的密碼。 newPassword 必須是唯讀的。 這個密碼也必須符合伺服器上設定的任何密碼安全性原則 (例如,最小長度和特定字元的需求)。

例外狀況

連接字串包含 UserIdPasswordIntegrated Security=true 的任何組合。

-或-

連接字串包含 Context Connection=true

-或-

newSecurePassword (或 newPassword) 大於 128 個字元。

-或-

newSecurePassword (或 newPassword) 並非唯讀。

-或-

newSecurePassword (或 newPassword) 為空字串。

其中一個參數為 null(connectionStringcredentialnewSecurePassword)。

另請參閱

適用於