SQL Server Native Client OLE DB Provider
The SQL Server Native Client OLE DB provider supports password expiration though a user interface and programmatically.
OLE DB User Interface Password Expiration
The SQL Server Native Client OLE DB provider supports password expiration through changes made to the SQL Server Login dialog boxes. If the value of DBPROP_INIT_PROMPT is set to DBPROMPT_NOPROMPT, the initial connection attempt will fail if the password has expired.
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.
If the user clicks OK and the password has expired, SQL Server prompts the user to enter and confirm a new password using the Change SQL Server Password dialog.
OLE DB Prompt Behavior and Locked Accounts
Connection attempts may fail due to the account being locked. 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. 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 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 Programmatic Password Expiration
The SQL 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.
The existing "Password" property refers to DBPROP_AUTH_PASSWORD and is used to store the new password.
.gif) Note: |
|---|
|
In the connection string, the "Old Password" property sets SSPROP_AUTH_OLD_PASSWORD which is the expired password that is not available via a connection string property, as this would interfere with connection pooling. |
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. Subsequent attempts to retrieve the old password return VT_EMPTY.
| Note: |
|---|
|
SSPROP_AUTH_OLD_PASSWORD should never be persisted since it is only used when a password has expired. |
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.
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. This is detected when IDBInitialize::Initialize is called.
If an attempt to change the password fails unexpectedly, the server returns error code 18468. A standard OLEDB error is returned from the connection attempt.
For more information about the DBPROPSET_SQLSERVERDBINIT property set, see Initialization and Authorization Properties.