SQL Server Management Studio - Login failed for user (.Net SqlClient Data Provider)

Question

I have very strange problem.

When I try to connect my database (located on shared SQL Server) with SQL Server Management Studio 2008/2012. This database which is hosted by a company for web hosting, I receive this very strange error:

Cannot connect to tango.rdb.superhosting.bg.

Login failed for user 'database_administrator'. (.Net SqlClient Data Provider)

Server Name: tango.rdb.superhosting.bg
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Near some weeks ago everything worked perfect and I did not have any problems with connecting the SQL Server with SQL Server Management Studio 2008.

When I faced this problem, I installed SQL Server Management Studio 2012 and result is the same.

But with the absolute the same credentials I can connect to my database on the same server using VS2010 or Toad for SQL.

Do you have any suggestions?

Thanks in advance!

Answer 1

This is probably an issue of an orphaned user.

The login 'database_administrator' can either be:

• On the Database itself. Just navigate to Logins under the database in SSMS.
• Or, under the Server -> Security Logins.

What I usually do is:

1. Remove the Login that exists under the database
2. Add the Login under the server level Security
3. Give rights to that user to the particular database it needs

There are a bunch of ways to solve orphaned users, but that is usually what I do.