以编程方式更改密码Changing Passwords Programmatically

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL Server 2005 (9.x)SQL Server 2005 (9.x) 之前,如果用户的密码过期,则只有管理员能对其进行重置。Before SQL Server 2005 (9.x)SQL Server 2005 (9.x), when a user's password expired, only an administrator could reset it. 从开始SQL Server 2005 (9.x)SQL Server 2005 (9.x)SQL ServerSQL Server native client 支持通过SQL ServerSQL Server native client OLE DB 访问接口和SQL ServerSQL Server native client ODBC 驱动程序以编程方式处理密码过期,并通过对SQL Server 登录对话框进行更改。Beginning with SQL Server 2005 (9.x)SQL Server 2005 (9.x), SQL ServerSQL Server Native Client supports handling password expiration programmatically through both the SQL ServerSQL Server Native Client OLE DB provider and the SQL ServerSQL Server Native Client ODBC driver, and through changes to the SQL Server Login dialog boxes.

备注

如果可能,请在运行时提示用户输入他们的凭据,并避免用持久化格式存储他们的凭据。When possible, prompt users to enter their credentials at run time and avoid storing their credentials in a persisted format. 如果必须保留其凭据,应使用 Win32 加密 API 来加密这些凭据。If you must persist their credentials, you should encrypt them using the Win32 crypto API. 有关密码使用的详细信息,请参阅强密码For more information about the use of passwords, see Strong Passwords.

SQL Server 登录错误代码SQL Server Login Error Codes

当由于身份验证问题而导致无法连接时,以下 SQL Server 错误代码之一可供应用程序使用,以帮助诊断和恢复。When a connection cannot be made because of authentication problems, one of the following SQL Server error codes will be available to the application to assist diagnosis and recovery.

SQL Server 错误代码SQL Server Error Code 错误消息Error Message
1511315113 用户 '%.*ls' 登录失败。原因: 密码验证失败。Login failed for user '%.*ls' Reason: Password validation failed. 帐户已锁定。The account is locked out.
1846318463 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 此时无法使用密码。The password cannot be used at this time.
1846418464 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 该密码太短,不符合策略要求。The password does not meet policy requirements because it is too short.
1846518465 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 密码太长,不符合策略要求。The password does not meet policy requirements because it is too long.
1846618466 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 该密码不够复杂,不符合策略要求。The password does not meet policy requirements because it is not complex enough.
1846718467 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 该密码不符合密码筛选器 DLL 的要求。The password does not meet the requirements of the password filter DLL.
1846818468 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 密码更改失败。Reason: Password change failed. 在密码验证过程中出错。An unexpected error occurred during password validation.
1848718487 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 该帐户的密码已过期。Reason: The password of the account has expired.
1848818488 用户 "%.*ls" 登录失败。Login failed for user '%.*ls'. 原因: 该帐户的密码必须更改。Reason: The password of the account must be changed.

SQL Server Native Client OLE DB 访问接口SQL Server Native Client OLE DB Provider

SQL ServerSQL Server Native Client OLE DB 提供程序通过用户界面和以编程方式支持密码过期。The SQL ServerSQL Server Native Client OLE DB provider supports password expiration though a user interface and programmatically.

OLE DB 用户界面密码过期OLE DB User Interface Password Expiration

SQL ServerSQL Server Native Client OLE DB 提供程序通过对SQL Server 登录对话框进行更改来支持密码过期。The SQL ServerSQL Server Native Client OLE DB provider supports password expiration through changes made to the SQL Server Login dialog boxes. 如果将 DBPROP_INIT_PROMPT 的值设置为 DBPROMPT_NOPROMPT,则在密码已过期的情况下初始连接尝试将失败。If the value of DBPROP_INIT_PROMPT is set to DBPROMPT_NOPROMPT, the initial connection attempt will fail if the password has expired.

如果 DBPROP_INIT_PROMPT 已设置为任何其他值,则无论密码是否已过期,用户都会看到“SQL Server 登录”对话框****。If DBPROP_INIT_PROMPT has been set to any other value, the user sees the SQL Server Login dialog, regardless of whether or not the password has expired. 用户可单击“选项”按钮,再选中“更改密码”进行更改********。The user can click on the Options button and check Change Password to change the password.

如果用户单击“确定”且密码已过期,则 SQL ServerSQL Server 会提示用户使用“更改 SQL Server 密码”对话框输入并确认新密码****。If the user clicks OK and the password has expired, SQL ServerSQL Server prompts the user to enter and confirm a new password using the Change SQL Server Password dialog.

OLE DB 提示行为和锁定的帐户OLE DB Prompt Behavior and Locked Accounts

连接尝试可能会由于帐户被锁定而失败。Connection attempts may fail due to the account being locked. 如果在显示“SQL Server 登录”对话框后发生此情况,则向用户显示服务器错误消息,且连接尝试中止****。If this occurs following the display of the SQL Server Login dialog, the server error message is displayed to the user and the connection attempt is aborted. 如果在显示“更改 SQL Server 密码”对话框后用户输入错误的旧密码值,也会出现这种情况****。It may also occur following the display of the Change SQL Server Password dialog if the user enters a bad value for the old password. 在这种情况下,将显示相同的错误消息,并且连接尝试将中止。In this case the same error message is displayed, and the connection attempt is aborted.

OLE DB 连接池、密码过期和锁定的帐户OLE DB Connection Pooling, Password Expiration, and Locked Accounts

当连接在连接池中仍处于活动状态时,帐户可能被锁定或其密码可能已过期。An account may be locked or its password may expire while the connection is still active in a connection pool. 服务器会在以下两种情况下检查密码是否已过期以及帐户是否已锁定。The server checks for expired passwords and locked accounts on two occasions. 第一种情况是首次创建连接时。The first is when a connection is first created. 第二种情况是从相应池中获取相应连接以重置连接时。The second occasion is upon connection reset, when the connection is taken from the pool.

如果重置尝试失败,则将从相应池中删除相应连接且返回一个错误。When the reset attempt fails, the connection is removed from the pool and an error is returned.

OLE DB 编程密码过期OLE DB Programmatic Password Expiration

SQL ServerSQL Server Native Client OLE DB 提供程序通过添加已添加到 DBPROPSET_SQLSERVERDBINIT 属性集的 SSPROP_AUTH_OLD_PASSWORD (类型 VT_BSTR)属性,支持密码过期。The SQL ServerSQL Server Native Client OLE DB provider supports password expiration through the addition of the SSPROP_AUTH_OLD_PASSWORD (type VT_BSTR) property that has been added to the DBPROPSET_SQLSERVERDBINIT property set.

现有“密码”属性是指 DBPROP_AUTH_PASSWORD,用于存储新密码。The existing "Password" property refers to DBPROP_AUTH_PASSWORD and is used to store the new password.

备注

在连接字符串中,“旧密码”属性会设置 SSPROP_AUTH_OLD_PASSWORD,它是当前(有可能是过期的)密码,通过提供程序字符串属性无法得到该密码。In the connection string, the "Old Password" property sets SSPROP_AUTH_OLD_PASSWORD, which is the current (possibly expired) password that is not available via a provider string property.

访问接口不保留此属性的值。The provider does not persist the value of this property. 如果设置此属性,则访问接口不使用连接池进行首次连接,原因是将进行新连接。When this property is set, the provider does not use the connection pool for the first connection because a new connection will occur. 如果密码更改成功,则不能重新使用当前连接,原因是当前连接仍包含旧密码,这些密码在密码更改后失效。If the password change is successful, the current connection cannot be reused since it still contains the old password, which will be invalid after the password change. 此外,如果登录成功,则访问接口将清除此属性。Also, if the login succeeds, the provider clears this property. 如果随后尝试检索旧密码,则将返回 VT_EMPTY。Subsequent attempts to retrieve the old password return VT_EMPTY.

备注

不得保留 SSPROP_AUTH_OLD_PASSWORD,因为只有在密码过期时才使用它。SSPROP_AUTH_OLD_PASSWORD should never be persisted since it is only used when a password has expired.

请注意,只要设置“旧密码”属性,访问接口就会假定正在尝试更改密码,除非还指定了 Windows 身份验证,这种情况下 Windows 身份验证始终优先。Note that whenever the "Old Password" property is set, the provider assumes that an attempt to change the password is being made, unless Windows Authentication is also specified, in which case it always takes precedence.

如果使用 Windows 身份验证,则指定旧密码会产生 DB_E_ERRORSOCCURRED 或 DB_S_ERRORSOCCURRED(具体取决于是将旧密码指定为 REQUIRED 还是 OPTIONAL),并且在 dwStatus 中返回 DBPROPSTATUS_CONFLICTINGBADVALUE 的状态值**。If Windows Authentication is used, specifying the old password results in either DB_E_ERRORSOCCURRED or DB_S_ERRORSOCCURRED depending on whether the old password was specified as REQUIRED or OPTIONAL respectively, and the status value of DBPROPSTATUS_CONFLICTINGBADVALUE is returned in dwStatus. 在调用 IDBInitialize::Initialize 时进行检测****。This is detected when IDBInitialize::Initialize is called.

如果更改密码的尝试意外失败,则服务器将返回错误代码 18468。If an attempt to change the password fails unexpectedly, the server returns error code 18468. 将从连接尝试返回标准的 OLEDB 错误。A standard OLEDB error is returned from the connection attempt.

有关 DBPROPSET_SQLSERVERDBINIT 属性集的详细信息,请参阅初始化和授权属性For more information about the DBPROPSET_SQLSERVERDBINIT property set, see Initialization and Authorization Properties.

SQL Server Native Client ODBC 驱动程序SQL Server Native Client ODBC Driver

SQL ServerSQL Server Native Client OLE DB 提供程序通过用户界面和以编程方式支持密码过期。The SQL ServerSQL Server Native Client OLE DB provider supports password expiration though a user interface and programmatically.

ODBC 用户界面密码过期ODBC User Interface Password Expiration

SQL ServerSQL Server NATIVE Client ODBC 驱动程序通过对SQL Server 登录对话框进行更改来支持密码过期。The SQL ServerSQL Server Native Client ODBC driver supports password expiration through changes made to the SQL Server Login dialog boxes.

如果调用SQLDriverConnect并将DriverCompletion的值设置为 SQL_DRIVER_NOPROMPT,则如果密码已过期,则初始连接尝试将失败。If SQLDriverConnect is called and the value of DriverCompletion is set to SQL_DRIVER_NOPROMPT, the initial connection attempt fails if the password has expired. SQLSTATE 值28000和本机错误代码值18487由对SQLErrorSQLGetDiagRec的后续调用返回。The SQLSTATE value 28000 and the native error code value 18487 are returned by subsequent calls to SQLError or SQLGetDiagRec.

如果DriverCompletion已设置为任何其他值,则无论密码是否已过期,用户都会看到SQL Server 登录对话框。If DriverCompletion has been set to any other value, the user sees the SQL Server Login dialog, regardless of whether or not the password has expired. 用户可单击“选项”按钮,再选中“更改密码”进行更改********。The user can click on the Options button and check Change Password to change the password.

如果用户单击 "确定" 且密码已过期, SQL ServerSQL Server则会提示使用 "更改 SQL Server 密码" 对话框输入并确认新密码。If the user clicks OK and the password has expired, SQL ServerSQL Server prompts to enter and confirm a new password using the Change SQL Server Password dialog.

ODBC 提示行为和锁定的帐户ODBC Prompt Behavior and Locked Accounts

连接尝试可能会由于帐户被锁定而失败。Connection attempts may fail due to the account being locked. 如果在显示“SQL Server 登录”对话框后发生此情况,则向用户显示服务器错误消息,且连接尝试中止****。If this occurs following the display of the SQL Server Login dialog, the server error message is displayed to the user and the connection attempt is aborted. 如果在显示“更改 SQL Server 密码”对话框后用户输入错误的旧密码值,也会出现这种情况****。It may also occur following the display of the Change SQL Server Password dialog if the user enters a bad value for the old password. 在这种情况下,将显示相同的错误消息,并且连接尝试将中止。In this case the same error message is displayed, and the connection attempt is aborted.

ODBC 连接池、密码过期和锁定的帐户ODBC Connection Pooling, Password Expiry, and Locked Accounts

当连接在连接池中仍处于活动状态时,帐户可能被锁定或其密码可能已过期。An account may be locked or its password may expire while the connection is still active in a connection pool. 服务器会在以下两种情况下检查密码是否已过期以及帐户是否已锁定。The server checks for expired passwords and locked accounts on two occasions. 第一种情况是首次创建连接时。The first is when a connection is first created. 第二种情况是从相应池中获取相应连接以重置连接时。The second occasion is upon connection reset, when the connection is taken from the pool.

如果重置尝试失败,则将从相应池中删除相应连接且返回一个错误。When the reset attempt fails, the connection is removed from the pool and an error is returned.

ODBC 编程密码过期ODBC Programmatic Password Expiration

SQL ServerSQL Server NATIVE Client ODBC 驱动程序通过添加 SQL_COPT_SS_OLDPWD 属性(在使用SQLSetConnectAttr函数连接到服务器之前设置)来支持密码过期。The SQL ServerSQL Server Native Client ODBC driver supports password expiration through the addition of the SQL_COPT_SS_OLDPWD attribute which is set before connecting to the server using the SQLSetConnectAttr function.

连接句柄的 SQL_COPT_SS_OLDPWD 属性是指已过期的密码。The SQL_COPT_SS_OLDPWD attribute of the connection handle refers to the expired password. 对于此属性,没有连接字符串属性,原因是如果有的话会影响连接池。There is no connection string attribute for this attribute, as this would interfere with connection pooling. 如果登录成功,则驱动程序将清除此属性。If the login succeeds, the driver clears this attribute.

SQL ServerSQL Server功能的四种情况下,NATIVE Client ODBC 驱动程序将返回 SQL_ERROR:密码过期、密码策略冲突、帐户锁定以及使用 Windows 身份验证时设置旧密码属性的时间。The SQL ServerSQL Server Native Client ODBC driver returns SQL_ERROR in four cases for this feature: password expiration, password policy conflict, account lockout, and when the old password property is set while using Windows Authentication. 调用SQLGetDiagField时,驱动程序将向用户返回相应的错误消息。The driver returns the appropriate error messages to the user when SQLGetDiagField is invoked.

另请参阅See Also

SQL Server Native Client 功能SQL Server Native Client Features