Assume that you use Microsoft SQL Server Management Studio (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 returned, 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 in which the Windows account that is used to start SSMS resides.
To work around this issue, use Windows authentication instead of SQL authentication, log in to Windows by using a local account instead of a domain account, or follow the steps in the “Resolution” section at 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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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 “eaten.” Essentially, it's not properly handled or made visible to the user.
- This causes the password that is 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.