Sql-server – Login failure error log when querying Central Management Server group

central-management-serversql serversql-server-2008-r2ssms

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.

Best Answer

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

Related Question