When you try to create a linked server together with remote logins, you encounter the error:
Additionally, you may receive the following error message in SQL Server error log:
When you query the catalog view sys.symmetric_keys, one row for the service master key is returned. When you query the catalog view sys.key_encryptions, no row is returned. The expected result is two rows.
- If you have encountered this issue, this update won't work, and you have to use one of the workarounds listed in the "Workaround" section.
- In order to use the changes in this cumulative update, you have to install SQL Server by using the Product Update feature and perform a slipstreamed install with RTM + SP + CU.
The issue was first fixed in the following cumulative update of SQL Server:
- Regenerate the service master key by using the following Transact-SQL statement:
ALTER SERVICE MASTER KEY FORCE REGENERATE
- Reinstall the instance of SQL Server.
- Restart the operating system.
- Log on to the RDP session by using the SQL Server service account.
- Wait for several minutes and then log off after the user profile is created completely.
- Start SQL Server setup after a few minutes.
Article ID: 3091643 - Last Review: Oct 19, 2015 - Revision: 1