SQL Server Express issue deploying MVC4 application to IIS

Question

I've spent the past two days going through google (and here) with different variations of a connection string on my deployment server... If I had hair left, I'd have pulled it out by now... ;)

I have an application that runs perfectly on my development Win7 box and am working on deploying it to a test server before I put it into beta.

The test server is Windows 2008 R2 with IIS 7.

I'm able to deploy the application to IIS and the site comes up.

But when I got to a page that connects to the SQL Server Express running on the local host, it dies. I should also mention that the database is already existing on the SQL Server Express instance and my Winforms and service application works great when connecting to that database.

My web.config contains the following connection string:

<add name="OIDEntities" 
     connectionString="metadata=res://*/Models.OID.csdl|res://*/Models.OID.ssdl|res://*/Models.OID.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=OID;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />

The error I get is:

The underlying provider failed on Open.

I've tried resetting the service in SQL Server to nt authority\network service (to match the app pool). I've also tried creating a SQL Server user and using the following connection string:

<add name="OIDEntities" 
     connectionString="metadata=res://*/Models.OID.csdl|res://*/Models.OID.ssdl|res://*/Models.OID.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=OID;User ID=OID_User;Password=MyPrivatePAss;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />

I've gone into the SQL Server configuration manager and configured / enabled named pipes then TCP/IP and used the host IP to connect through server= in the connection string.

I am able to log into SQL Server Management Studio with the username/password and also using the IP vs. the instance name of .\SQLExpress.

What am I missing?

have you tried using machinename\sqlexpress? – allen Feb 20 '13 at 13:40 — allen, Feb 20 '13 at 13:40
Yes, I tried that as well... – Neon Blue Feb 20 '13 at 13:54 — Neon Blue, Feb 20 '13 at 13:54

score 0 · Answer 1 · edited May 23 '17 at 12:18

ok, it only took 3 days to figure this out... I added the following code to my error.cshtml: `@Html.DisplayFor(model => model.ActionName)

@Html.DisplayFor(model => model.ControllerName)

@Html.DisplayFor(model => model.Exception.Source)

@Html.DisplayFor(model => model.Exception.InnerException)

@Html.DisplayFor(model => model.Exception)`

This showed me that the IIS APPPOOL\[PoolName] did not have access. I added this into my SQL config with the following instructions: Add IIS 7 AppPool Identities as SQL Server Logons

SQL Server Express issue deploying MVC4 application to IIS

1 Answers1