How to avoid connections against empty/null database by Entity Framework

Question

We use entity framework to read from an existing database. This is a simplified version of our code.

using (my context context = new mycontext())
{
    if(context.Database.Connection.State == System.Data.ConnectionState.Closed)
    {
        _logger.Info(" Opening the connection to the database");
        context.Database.Connection.Open();
    }

    context.Configuration.LazyLoadingEnabled = false;          
    IQueryable<mymodel> people;
    people = context.People.OrderBy(x => x.Firstname);
    _lstContacts = people.ToList();

    if (context.Database.Connection.State != System.Data.ConnectionState.Closed)
    {
        context.Database.Connection.Close();
        context.Database.Connection.Dispose();
        _logger.Info(" Connection to the database Closed");
    }
}

It works 100% of the time, but... On our UAT environment we can see failed connections to the Microsoft SQL server with the error:

Login failed for user "my user". Reason: Failed to open the explicitly specified database "null". Client my IP.

For us, these are ghost connections because at the time when we see the errors in the SQL server, our code is not executed. Initially we didn't close and open the connection explicitly, we just added it trying to control when EF open and closes the connection, but it didn't fix the issue.

Our connection string is using the following format:

<add name="MYCN" connectionString="metadata=res://*/CVs.Cvs.csdl|res://*/CVs.Cvs.ssdl|res://*/CVs.Cvs.msl;provider=System.Data.SqlClient;provider connection string="data source=myserver\;initial catalog=mydatabase;Integrated Security=;User ID=myuser;Password=XXXXXXX;MultipleActiveResultSets=True;App=EntityFramework"/>

As you can see, we are specifying the database in the connection string and our user only have access to our database, so we understand the error when EF doesn't include the database in the connection string, but we don't understand why it's trying to perform these connections. We know the connections are coming from our application, because we are the only one using that specific user, the IP is the IP of our server, and because the logs in SQL server tell us that the application is "EntityFramewrok"

Answer 1

I didn't personally see the error before, but researched for you and seen that many people suffered from the same problem discussed here: https://blogs.msdn.microsoft.com/sql_protocols/2006/02/21/understanding-login-failed-error-18456-error-messages-in-sql-server-2005/

I read all the messages in the website specified, and here are the solutions offered and at least one other user confirmed that it worked. You might not use 2005 as you didn't specify your version in your question, some solutions I believe will still work for you. Try the list below.

Solution list:

1) Please check the state number of this error and search solution by the state number in addition to the message, might give your more accurate solution proposals. Most common states are listed:

All state-error descriptions you can find here: https://sqlblog.org/2011/01/14/troubleshooting-error-18456

2) Make sure the username and password are correct.

3)

• Logon to SQL Server using windows authentication.
• Right click in the query window that appears and select "Open Server in Object Explorer"
• Go to object explorer and open the "Security" folder and then the "Logins" folder.
• Double-click the "sa" user and change the password. Also, like another user mentioned above, untick the "Enforce Password Policy" in addition to resetting the password.

4) Try to change the password and turn off the policy, and try with new password.

exec sp_password @new = ‘sqlpassword’, @loginame = ‘sa’
alter login sa
with password = ‘sqlpassword’ unlock,
check_policy = off,
check_expiration = off

5) Run your application/browser and SSMS (if you work on it) in administration mode.

6)

• Open Windows Services
• Configure Microsoft Single Sign-on Service to use the proper account
• Open Central Administration >> Operations >> Manage settings for single sign-on
• Configure properties to use the same account used for Microsoft ‘Single Sign-on Service

7) Go to Sql server configuration manager and Enable TCP/IP and named pipes

8)

• go to sql server
• right click on server, choose properties
• click on security
• on server authentication, enable SQL Server authentication

These might help:

https://www.wikitechy.com/errors-and-fixes/sql/login-failed-error-18456-severity-14-state-38-reason-failed-to-open-the-explicitly-specified-database

https://dba.stackexchange.com/questions/90445/login-failed-for-user-error-18456-severity-14-state-38