516 Login Failed Error

When using SQL Management Studio to connect to your MSSQL database, you may encounter specific errors even though you are using the correct login credentials.

Below, we will explain each issue and it's resolution:


516 Login Failed Error

When logging into SQL Management Studio, you may receive the '516 Login Failed Error' which is an error stating that you are unable to log in to the MSSQL database even though you have set the correct username and password.

The common cause of this is due to collation being enabled in SQL Management Studio 2008. This also explains why myLittleAdmin is functioning properly and displaying the correct database while SQL Management Studio generates an error.

What is Collation?

Collation is a set of rules that determine how data is stored and compared in SQL server. These rules define characteristics such as how data is sorted with the correct character sequence, case sensitivity specifications, how accent marks are treated, and character widths.

Collation issues in SQL Server Management Studio in a Limited Security Environment?

By default, when you click the 'Databases' node within the SQL Server Management Studio under the Object Explorer after connecting to a server, a list of databases on that server is enumerated and shown. In a shared environment, such as a hosted SQL server account solution, each log-in only has access to its own database. This means that collation information is only available for the database that the log-in has access to.

SQL Server Management Studio, by default, tries to grab collation information for every database that is enumerated on the server. The trouble occurs when a database that has a collation other than the default is enumerated and the log-in used to enumerate the database does not have access to the collation information. This usually results in a complete error when opening the ‘Database’ node and prevents any databases from being shown.

The error usually states:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
-> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

If you are getting the error "Issue: Failed to retrieve data for this request" or "Login Failed Error 516," please disable collation by following the steps below.

How to Disable Collation

To disable collation:

1. Open SQL Management Studio.
2. Connect to the server with the Server Name & Login Credentials (these are located within your web.config in the connection-string area as uid: and pwd:).
3. On the Left, click > Databases.
4. Press F7 on the keyboard, and Object Explorer Details should open on the right side.
5. Right click on Name in the right hand column.
6. Uncheck the Collation option.
7. Refresh the Databases (A white box with green arrows, or press F5).

The 516 Login Failed Error should now be resolved.


Related Error: Microsoft SQL Server, Error: 916

-> The server principal "login" is not able to access the database "db with non default collation" under the current security context. (Microsoft SQL Server, Error:916).

This error can occur for several reasons, the most common reasons someone would see this error would be:

  • If a database is offline and/or it's collation is NULL.
  • If a login does not have permission to view data of a column selected in the header.
  • If multiple databases with different collations are on the instance and SSMS is unable to retrieve the collation because a database is configured to auto-close.

To Reproduce the Error

This error can be replicated doing the following steps:

  1. Connect the SQL Server using SQL Management Studio.
  2. From the top navigation menu, click View.
  3. Select Object Explorer Details (F7).
  4. From the left hand navigation menu, double click the Databases folder.
  5. You will notice multiple columns such as Name, Policy Health State, Collation, etc. in the column header, but no user defined databases are being displayed.
Note: Before attempting to correct the error and to find the root cause, connect to SQL Server again with SSMS. This time however, use the SA account. It will probably now reveal that the collation is NULL for specific user defined databases.

To Correct the 916 Error

  1. To resolve the 'collation is NULL' issue, right click on the column header (in the Object Explorer Details window) and un-check all of the optional columns.
  2. Click the refresh icon and the databases should now appear.

The following image should best illustrate how to resolve the Server Error: 916

Display 313