System Data Entity. The underlying provider failed on Open

Question

I am creating 2 projects that have the same database (it's an MDF database). The first one is the map editor, and I use XNA 4 and Web Services to connect to it. The second one is the game itself and uses XNA 3.1 and Entity Data Model to connect database.

When I run the map editor and access the database, it runs properly. Bbut when I run the game and access the database, it shows an error "The underlying provider failed on Open"

I think the connection from the web service is not closed yet. But I don't know where I should close the connection.

Here is my code from the web service:

public Map AddNewMap(string username, string mapName, int sizeX, int sizeY)
    {
        using (BaseModelDataContext context = new BaseModelDataContext())
        {
            Map newMap = new Map()
            {
                Username = username,
                Name = mapName,
                SizeX = sizeX,
                SizeY = sizeY,
                Upload_Date = DateTime.Now,
                Status = 0
            };

            context.Maps.InsertOnSubmit(newMap);
            context.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);
            context.Dispose();
            return newMap;
        }
    }

EDIT:

Here is the entity data model code :

using (MazeEntities ent = new MazeEntities())
        {
            ent.Connection.Open();
            return (from map in ent.Map
                    select map).ToList<Map>();
        }

This code runs properly if I did not use the web service before. If I use the web service first, it shows an error at ent.Connection.Open();

Here is the inner exception:

Cannot open user default database. Login failed.\r\nLogin failed for user 'erkape-PC\erkape'.

Connection string for web service :

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\3DMapDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Connection string for the game:

"metadata=res:///MazeDataModel.csdl|res:///MazeDataModel.ssdl|res://*/MazeDataModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\eRKaPe\DropBox\TA\Program\3D_Map_Editor\3DMapEditorServices\App_Data\3DMapDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />

The using-block automatically disposes the context after use. You don't need to explicitly call context.Dispose() here. This doesn't solve your problem. Just an info in general. Through the disposal of the context (no matter if implicitly or explicitly) the underlying connection should be closed as well. The problem must be somewhere else. Sorry I couldn't help more. — Dennis Traub, May 12 '12 at 06:20
I think the inner exception would tell you what the error is. Check your connection string and what is the user that attempts to connect to the database and its permissions. — dan radu, May 12 '12 at 06:43
Post the inner exception(s) text here. Also, consider that disposing of the context disposes underlying connection, but dispose of the connection **does not** mean that connection will be physically **closed**. It just returns to the connection pool and can be reused then. — Dennis, May 12 '12 at 06:52
@dradu, actually the error is not in the web service, but in the entity data model. I'll update the code using the entity data model.. and post the inner exception.. — erkape, May 12 '12 at 14:01
@erkape, can you post connection strings used in web service and another application (game)? — Dennis, May 12 '12 at 17:57
Do you use Windows authentication to connect to your DB? When you use the map editor is the same user (erkape-PC\erkape)? If yes, is the DB server on a different machine than the web server? — dan radu, May 12 '12 at 21:15
@dradu i use .mdf file to store my database. the map editor, the game, and the .mdf file are on the same machine. — erkape, May 13 '12 at 04:10
I have a feeling that is an issue with the user instance on SQL Express. Sorry for not being able to help, but [here](http://msdn.microsoft.com/en-us/library/bb264564(v=sql.90).aspx) is an article that might explain / help. Or [this](http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx) one. — dan radu, May 13 '12 at 06:44
Thanks for your reply, i will read the articles later. I'm still confused why I got an error on ADO .NET after I use the web service. When I stopped the IIS, the ADO .NET can run properly. — erkape, May 13 '12 at 07:27

score 0 · Answer 1 · answered May 12 '12 at 07:45

For a quick check, can you try adding the following line after the using:

    using (BaseModelDataContext context = new BaseModelDataContext())
    {
        context.Connection.Open();

        OR

        context.Database.Connection.Open(); 

        // your code here

score 0 · Accepted Answer · answered May 19 '12 at 11:22

Finally I found a way to solve my problem after reading some articles. The connection from the web service doesn't close automatically after I close the map editor. That is why I can't access my database from the game. I have to change the connection string from both application, I set the User Instance to False. The game can access the database this way.

score 0 · Answer 3 · answered Mar 05 '14 at 12:07

0

Please check the following post http://th2tran.blogspot.ae/2009/06/underlying-provider-failed-on-open.html

Also please Enable for 32 Bit application in the APplication Pool of that application.

This may resolve.

answered Mar 05 '14 at 12:07

Sheryar Nizar

285
4
5

score -1 · Answer 4 · answered May 12 '12 at 06:39

-1

You are trying to return an object (Map) which is associated with the Context. This object has the context information which can't be returned to the client.

You will need to create your own DataContract (a type having necessary properties) that you want to expose to the client.

Or you can use the POCO implementation As described here

answered May 12 '12 at 06:39

Hps

1,177
8
9

What is relationship between "The underlying provider failed on Open" and "returning object which is associated with the Context"? – Dennis May 12 '12 at 06:54
@Dennis I am sorry, I answered it based on my previous experience with the exact situation. The actual error that I found was "Object graph for type contains cycles and cannot be serialized if reference tracking is disabled" but on the client it just displayed the generic "underlying...." error. – Hps May 12 '12 at 08:06
@Dennis , I don't mind about the down vote!! Just found a link which might help in relating. http://www.codeexperiment.com/post/Returning-LINQ-to-SQL-Entities-From-WCF.aspx – Hps May 12 '12 at 08:14
"The underlying provider failed on Open" means that ADO .NET provider having some trouble (which described in the inner exception) when context trying to open database connection. "Underlying provider" and "underlying connection" (from the link you provided) are very different things, like "green" and "warm". So, your answer makes no sense. – Dennis May 12 '12 at 08:32
thanks for your reply, but i can get the object (Map) correctly. i got an error when I open the database with ADO .NET after I use the web service. Just like @Dennis said. – erkape May 12 '12 at 14:09

System Data Entity. The underlying provider failed on Open

4 Answers4