3

I have been using "Local Server Groups" registration for quite a while now with SQL Server 2008 Management Studio.

I have created a group with 5 registered servers. They are registered using SQL authentication with the same login. That login has the same default database set on each of those servers. I can successfully connect to that group and query.

Today I changed the last server in the list of the registered servers group and changed it's connection details from "default database" to a specific database name that is different (*1) than the default database used for the other registered servers. It is assured that the login / password is correct, the login has access to the default and explicitly configured databases and all the databases exist.

Edit 1: I meanwhile figured out that it does not matter at all which database is configured for the connection, as long as there is something else configured other than "default db" the error comes - even if the configured db is the same as the default database, just typed explicitly.)

Since that moment, a SQL Alert for error 18456(=invalid login), that is configured to raise on each of the servers, showed up when connecting to the group for the FIRST server in the list. However, this alert definitely is WRONG because the connection was successfully established for all registered servers and I can query the databases as expected.

After deleting this first server from the list of registered servers, the same wrong login error alert 18456 showed up again only for the (new) first registered server in the group.

When I changed all the registered servers in the group back to use the default database for connect, this behavior disappeared.

I looked for a similar bug description but found nothing. Does anyone have any experience or reasons for this?

Details about SSMS:

Microsoft SQL Server Management Studio                      10.50.6220.0
Microsoft Analysis Services Client Tools                        10.50.6000.34
Microsoft Data Access Components (MDAC)                     6.1.7601.17514
Microsoft MSXML                     3.0 4.0 6.0 
Microsoft Internet Explorer                     9.11.9600.18282
Microsoft .NET Framework                        2.0.50727.5485
Operating System                        6.1.7601

Error log details:

Date 09.05.2016 16:38:52 Log SQL Server (Current - 08.05.2016 23:59:00)

Source Logon

Message Error: 18456, Severity: 14, State: 38.

Date 09.05.2016 16:38:52 Log SQL Server (Current - 08.05.2016 23:59:00)

Source Logon

Message Login failed for user 'xxx'. Reason: Failed to open the explicitly specified database. [CLIENT: 1.2.3.4]

EDIT 2:

Even if all registered servers have "default database" selected but the login's configured default database on one (not the first) of the registered servers is different, then the same behavior shows up - the error is raised for the first server in the list.

  • And sorry it's unclear but does that error occur on the server that you changed the registration, or a different server (like the first one in the list), or all servers? (I was about to test this but need to swap VMs for some strange reason). – Aaron Bertrand May 9 '16 at 15:03
  • 1
    The error comes always for the first server in the list of registered. If I remove it, it comes for the next in the list that became the first one. It never comes for the server with the different database or where the login has ha different default db configured on. After playing around and putting everything BACK to how it used to be before, that error now ALWAYS comes for the first server in the list, although all servers are configured with DEFAULT DATABASE... AND although connection and query works fine. It's weird. – Magier May 9 '16 at 15:10
  • @AaronBertrand I guess you have not been able to reproduce this? It's still an issue for me and I still have to keep the invalid login Alerts disabled because of this behavior. – Magier Sep 12 '16 at 14:13
  • I have not, but I have no reason to use such an ancient version of SSMS, either... :-) – Aaron Bertrand Sep 12 '16 at 14:44
  • This definitely does not sound like normal behavior, 38 definitely means a database was inaccessible - do you have any databases offline, set to auto-close, snapshots, single user, restoring, or in recovery? Any databases that have been removed, renamed, or denied access? What does profiler tell you happens during connection (it might reveal a database name for example)? – Aaron Bertrand Sep 12 '16 at 14:49

Your Answer

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

Browse other questions tagged or ask your own question.