“The underlying provider failed on Open.”

I’m deploying my ASP.NET app to a brand new server with a fresh install of SQL Server 2008 Express. The db connection step failed with this error:

Message: The underlying provider failed on Open.
StackTrace:  at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

I checked the connectionString, which uses Windows authentication, and it looked fine.

Somebody suggested to Allow Remote Clients in the Network DTC Access settings. That option is already enabled on my environment.

I looked at the Event Viewer, and noticed this entry:

Event Type:    Failure Audit
Event Source:    MSSQL$MSSQL
Event Category:    Logon
Event ID:    18456
User:        NT AUTHORITY\NETWORK SERVICE
Description:
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

So IIS is trying to access the database under the credential   'NT AUTHORITY\NETWORK SERVICE' .  I looked that the db settings via SQL Server Management Studio and, sure enough, that account is not listed as one of the users allowed to connect. So I added him. And the web app was able to connect successfully.

11 comments:

Lgeorge said...

You are a Life saver. I am new to SQL Server and the Web Application couldn't login to the SQL server. I tried so many other things but at the end, I saw your post and was able to get it on my first try.

Thanks

Liby

Thanh Hai Tran said...

Thanks, Liby, for your comment.

winnietigrou said...

thanks a lot. This was helpful, and time saving

tendras said...

i wasted a complete day before i got ur post.Thanks a lot

DeadRat said...

Thank you

Anonymous said...

Thank you !

bill.jeffries said...

Just saved me a ton of time. Thanks!

Kwex said...

I solved this problem by starting this service manually - "Distributed Transaction Coordinator". This turned out to be the MSDTC service it was looking for.

Hope this helps someone.

Kwex.

Chintan Udeshi said...

Thanks a lot Dude it really helped me out to solve the Error.......

Anonymous said...

Thanks a lot!

Anonymous said...

it works, thank you!