"Invalid password" error and slow response to typed password in SSMS
This article describes an issue that occurs at password input in SQL Server Management Studio (SSMS) with SQL Server authentication.
Original product version: SQL Server
Original KB number: 4038457
Assume that you use SSMS 17.2 (or an older version of the program) to connect to any version of SQL Server by using SQL Server authentication. When you type the password, the visual response of the password box to your key presses on the keyboard is noticeably slow. Additionally, the login attempt fails, and an "invalid password" error message is shown, even if you entered the password correctly.
This issue occurs because the Windows Data Protection API can't back up a MasterKey to a domain controller for the domain, which contains the Windows account that's used to start SSMS.
To work around this issue:
- Use Windows authentication instead of SQL authentication.
- Log in to Windows using a local account instead of a domain account.
- Follow the steps in the "Resolution" section in DPAPI MasterKey backup failures when RWDC isn't available.
To fix this issue, identify and resolve the issue that's preventing backup of the Windows Data Protection API MasterKey.
The following steps describe what happens in this scenario:
When you type a character in the password box in SSMS, the CryptProtectData Windows Data Protection API (DPAPI) function is called to encrypt the password.
DPAPI initially generates a strong key called a MasterKey (because there's no valid MasterKey to be used), which is protected by the user's logon credentials, and the backup process is invoked.
When this process fails (because there's no accessible writable Domain Controller (DC) for the user domain), an error is generated and thrown to the SSMS application.
In SSMS code, this error is caught and not handled. Since the code doesn't handle the situation, this error isn't shown to the user.
This causes the password that's sent to the SQL Server to be an empty string. When you encounter this issue, there are two symptoms:
Typing in the password box is noticeably slow due to the failed attempts to reach a writable domain controller.
The SQL Server reports an invalid password in its error log even when the correct password is entered.
Essentially, you have encountered the issue documented at the article DPAPI MasterKey backup failures when RWDC isn't available. Microsoft has changed the SSMS code for the 17.3 and future releases. Therefore, if this issue is encountered, the system will report the exception that is thrown from DPAPI for much easier diagnosis.