Entity Framework "The underlying provider failed on Open" - Cassini vs IIS

Question

I have a service that uses EF to retrieve data from a SQL database.

The EF model is in a class library. In the class library the connection is configured as:

      <add name="APIC2CEntities"
     connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MYSERVER;initial catalog=MYDB;user id=MYUSER;password=THEPASSWORD;multipleactiveresultsets=True;App=EntityFramework&quot;"
     providerName="System.Data.EntityClient" />

This class library is referenced by a WCF service project. In webconfig I control the EF connection with:

      <add name="APIC2CEntities"
     connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MYSERVER;initial catalog=MYDB;user id=MYUSER;password=THEPASSWORD;multipleactiveresultsets=True;App=EntityFramework&quot;"
     providerName="System.Data.EntityClient" />

When my service is running in Cassini the data is retrieved correctly. When the service is running under IIS (Windows XP) the connection fails with the following exception:

The underlying provider failed on Open

with an inner exception telling me:

{"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

I understand that Cassini is running under my logged in account, and that IIS is running under the IUSR_MYMACHINE account, but my connection is not trusted, so this shouldn't matter...

How else might an EF connection be manipulated or controlled differently between IIS and Cassini?

Thanks,
Mark

Answer 1

This solution is for someone facing this issue while deploying windows application,

I know this is late, but this maybe useful for someone in future, In my scenario, purely this is a connection string issue I developed a windows application using entity framework (DB First approach) and I published it, the code was worked fine on my machine, but it's not worked on the client machine

Reason for this issue:-

I updated the client machine connection string in App.config file, but this is wrong, if that is a windows application, then it will not read the connection string from App.config (For deployed machines), it will read from .exe.config file,

So after deployment, we need to change the connection string in "AppicationName".exe.config file

Answer 2

I think the issue is still in connection string. If you are sure please do the following steps and test

1. Open blank notepad and save as connectionString.UDL
2. Open connectionString (just saved in step 1)
3. Under the connection tab: type server name (in my case: DESKTOP-IDSLV81) or IP as XXX.XXX.XXX.XXX,1433 where 1433 is default port the use respective username and password and the select Database(available DB is in Dropdown list)
4. Then press test connection if succeeded the press Ok button.
5. Open the connectionString.UDL file in notepad. The correct connection string is there and compare with your connection string in web.config

Answer 3

Our resolution on this occasion was to force the connection to use tcp (using data source=tcp:MyServer in the connection string) and enabling TCP on the SQL box (oops!)..

I still have no idea how the Cassini session was able to establish connection where the IIS session was not :-S

Mark