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"

What is the state number of that error? It should tell something like that, state number is 1 here for example ::> Msg 18456, Level 14, State 1, Server , Line 1 etc etc etc... — Eray Balkanli, Aug 13 '19 at 20:08
HI, we don't have the state number, in fact, we don't get any error from the application side, the error is in the server sid; it seems our code is not executed, but still, everything points to our application as the IP is the IP of our server, the user is only configured in our connectionstring.config and the application name is entityframewrork. I'll check with the DBA in case they can get the actual error. — Vicent Galiana, Aug 16 '19 at 07:37
Have you tried this [solution](https://stackoverflow.com/questions/19211082/testing-an-entity-framework-database-connection/19211240)? — Steve, Aug 20 '19 at 13:41
I see you have your connection string declared in xml (probably web.config). What does your code for dbcontext initialization look like? Where is the very first point in your application that you reference the dbContext (owin?)? — DiscipleMichael, Aug 20 '19 at 13:54
We rely completely on Entity framework. Our first attempt to intereact with the database is with the using of the new context, first line of code of my example: using (mycontext context = new mycontext()) — Vicent Galiana, Aug 22 '19 at 09:41

Eray Balkanli · Answer 1 · 2019-08-13T20:43:46.780

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

Hi, our application runs properly when our code is executed. If we use the same credentials via the SQL manager we can connect to the database when we explicitly configure it, if we don't we get the same error. Our problem is that we don't know, how come something within our app doamin is opening these connections, without using the database. — Vicent Galiana, Aug 16 '19 at 07:40
@VicentGaliana Are you employing "using" structure in your code? Should be like: using(SqlConnection conn = new SqlConnection("connection info")) { // your code } — Eray Balkanli, Aug 19 '19 at 14:12

score 0 · Answer 2 · answered Aug 13 '19 at 19:54

0

I think this is just an access issue for myuser in the UAT environment. Just make sure myuser has access in the UAT environment for UAT database and you should be good.

answered Aug 13 '19 at 19:54

DEV

949
1
9
29

It would make sense if we'd have a permanent error, but our functionality is working when it should, the problem is only with these "ghost connections" we don't know what/why is opening. – Vicent Galiana Aug 16 '19 at 07:41

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

2 Answers2