7

I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log.

Error: 18456, Severity: 14, State: 38. Login failed for user
'domain\account'. Reason: Failed to open the explicitly specified
database. [CLIENT: xx.xx.xx.xx]

This error is only appearing on this one particular SQL Server in this group. Running a Profiler Trace on this server and reproducing the login failure brings up the following User Error Message:

Cannot open database "<multiple>" requested by the login. The login failed.

My account belongs to an AD group which is apart of the sysadmin server level role. The AD group's default database is the master database. The SQL Server's Connection Properties, Connect to database: is set to <default>. Which is no different from any of the other SQL Servers in this group.

Any reason why this would occur?

  • The error appears in the trace once I click New Query and the window opens. Before any querying actually occurs.
  • Removed and re-added the server registration, error still appears.
  • I can query the server from the window successfully. There's no real issue or error experienced other than the fact that this error appears in the error log.
  • Server collations are not all the same.
  • The collation of the problematic server is Latin1_General_CI_AS.
  • Default user options are the same.
  • All databases are in the online state.
  • This question is tagged for SQL 2008 R2 - however I suggest the problem may be more related to the version of Management Studio. We are seeing this on all our CMS queries against a diverse range of SQL servers (2008 R2 through to 2017). We are using SSMS 18.4, and the problem has been present for us for at least the last few releases of SSMS. – Mike Feb 10 at 22:16
1

I have seen this issue when the default database for your login is set to the wrong database.

| improve this answer | |
0

In our case, I was able to solve the issue by going through every single registered server entry and changing the database from "default" to "master". Once all registered servers were set this way, the problem went away. This is using SSMS 18.4.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.