Login failed in SQL Server due to SSMS Intellisense

  • Article

Hello all,

Recently, I had worked on an interesting Login failed error constantly getting reported in SQL Error log. In this blog, we are covering the steps taken to fix the issue.

 

Below error was reported in Error log:

2018-02-22 09:48:19.76 Logon Error: 18456, Severity: 14, State: 38.
2018-02-22 09:48:19.76 Logon Login failed for user 'test'. Reason: Failed to open the explicitly specified database 'MODEL'. [CLIENT: <local machine>]

Looking at the error message, the Login failed error message was reported from the Local machine itself. Connected to SQL instance using the account for which login failed error message was reported. Expanding the model database  gave the same error.

But the user was not trying to access Model database, when connected to SQL Server instance.

 

To isolate the issue further, we collected SQL Profiler trace when the used logged in to SQL Server: From the trace collected, we identified that the Login failed for model database was reported from "Microsoft SQL Server management studio-Transact-SQL Intellisense" application.

 

The login failed error was getting reported in Error log, as soon as the user opens a new query editor session in SSMS.

 

Tried refreshing the Intellisense Local cache as per below, but user was still getting the login failed error.

 

Logging onto the same SQL instance using a sysadmin account in SQL, no errors were getting reported.

 

Management studio version used : 13.0.15000 (SSMS June 2016 version).

Please note that the issue was reported only on instances with SQL AlwaysOn setup.

 

Resolution:

The issue was turned out to be a known issue with SSMS June 2016 version. We performed in-place upgrade of  SSMS to 16.5.3 version using the below link which fixed the login failed error due to Intellisense.

https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms\#previous-ssms-releases

 

 

Hope the above steps shared help you troubleshoot SQL service startup issues.

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.