System.Data.SqlClient.SqlException: Cannot open database "database name" requested by the login. The login failed.

A short one today, this is about one of the slightly confusing error messages when it comes to connecting to SQL Server, namely:

System.Data.SqlClient.SqlException: Cannot open database "database name" requested by the login. The login failed.

Login failed for user 'the login'.

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

…

Let us take it from the beginning, fire up SQL Server Management Studio and create a database:

create database DatabaseDemo

go

and then create a simple Login, like so:

create login DemoLogin with password = 'DemoLogin', default_database=DatabaseDemo, check_expiration=off, check_policy=off

go

Finally, create a really simple .Net console application like so:

        static void Main(string[] args)

        {

            String cs = @"Data Source=.\sqlexpress;User=DemoLogin;Password=DemoLogin;Initial Catalog=DatabaseDemo";

            try

            {

                using (SqlConnection con = new SqlConnection(cs))

                {

                    con.Open();

                    Console.WriteLine("We're in...!");

                    con.Close();

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

and run it, this should create the error above.

The solution is simple. The DemoLogin does not have sufficient role membership or rights to this database.

So just add the login to an appropriate server role (I use sysadmin here, you should of course use a more appropriate one. Sysadmin may be a bit too generousJ)

exec sys.sp_addsrvrolemember @loginame = 'DemoLogin', @rolename = 'sysadmin'

go

And rerun the app, you should now be able to login.

Now, change the connection string to be:

String cs = @"Data Source=.\sqlexpress;User=DemoLogin;Password=DemoLogin;Initial Catalog=DatabasseDemo";

Rerun and you will get the same exception. This time because there is no database with that name.

This is then why I think this confusing, the same error can have (at least) two totally different reasons.

One being that the database simply doesn’t exist (since the name is incorrect etc.), yet it still tells you that the login failed, indicating that it does.

The other one being that the database does exist, however, the login failed, indicating that the database can’t be opened.

There are a few reasons for this scenario to appear.

One is moving the database from one server to another and not moving/assigning the logins the proper rights. That is when you can connect to one server but not another even though “everything is identical”.

One is having database names of the type “Srv01bDb02ezone” giving users plenty of chances to type the database name incorrectly.

Well, as mentioned, a short one J

Don’t forget to drop the database and login.

drop login DemoLogin

go

drop database DatabaseDemo

go