ASP.NET losing connection to SQL Server Express after being published to IIS

Question

I have an ASP.NET application that gets data from a SQL Server Express on the same computer. When I run the ASP.NET project from Visual Studio, the project can reach the database just fine. However, when published to IIS, the project loses the connection. Here is my connection string located in Web.config:

<connectionStrings>
    <add name="conString" 
         providerName="System.Data.SqlClient" 
         connectionString="Server = DESKTOP-EVAN\SERVER;Database=master;User ID=sa;Password=12345;" />
</connectionStrings>

The project can be reached on the published IIS, however no data is there and no error messages show.

I've added the application pool and NT Authority to the SQL login but still no luck connection to the database.

Does anyone have any experience with this? How can I get my published ASP.NET project to connect to my SQL Server Express?

Is the IIS server on the same machine as the machine you run Visual Studio on? — Tony Abrams
– Tony Abrams, Commented May 28, 2019 at 16:48
Yes, the IIS server and Visual Studio are on the same machine — GreenSaber
– GreenSaber, Commented May 28, 2019 at 16:48

Jake Steffen · Accepted Answer · 2019-05-28 16:41:15Z

3

Have you tried removing the spaces for the server like this:

 Server=DESKTOP-EVAN\SERVER

answered May 28, 2019 at 16:41

Jake Steffen

4153 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GreenSaber Over a year ago

Yes, same problem

Jake Steffen Over a year ago

Your connection string is specifying the "master" database, are you sure that is correct? You typically would create a new database rather than using that one.

GreenSaber Over a year ago

master is where the tables are, so that is correct. The connection works when running in visual studio, so I think the connection string is fine, but I'm not sure why it's not working when published

Jake Steffen Over a year ago

When you are running the site under VS the user is running as your local windows user. When you deploy to IIS it is running as the application pool user. Check what user this is running under by going to IIS > Sites > Right Click The Site > Manage WebSite > Advanced Settings. That is the user it is running as and that user may need to have SQL access. If you need to add a new application pool look here:learn.microsoft.com/en-us/iis/configuration/…

GreenSaber Over a year ago

Oh man... it was using the wrong application pool... It works now, thank you so much. I've been trying to solve this for way too long. I love you

marc_s · Accepted Answer · 2019-05-28 17:11:13Z

3

Did you install SQL Server Express on that machine where IIS is running, or on a machine that can be reached from the IIS machine?

Also: did you explicitly define a separate instance name for your SQL Server Express when installing?

By default, the instance name would be SQLEXPRESS - but you seem to refer to DESKTOP-EVAN\SERVER - so SERVER would be your instance name - is that the case??

If not: can you try DESKTOP-EVAN\SQLEXPRESS as the server/instance name instead?

<connectionStrings>
    <add name="conString" 
         connectionString="Server=DESKTOP-EVAN\SQLEXPRESS;Database=master;User ID=sa;Password=12345;" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

answered May 28, 2019 at 17:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

GreenSaber Over a year ago

The SQL Express Server and IIS are on the same machine. Yes, I just doubled checked, the instance name is SERVER. It works if I run from Visual Studio, the database updates the web pages, but when published to IIS the connection is lost.

amin89 Over a year ago

@GreenSaber same problem. have you got a solution?

GreenSaber Over a year ago

I have this working now, but it's been a while since I've posted this. Here is my new connection string in web.config, hope it helps:

<add name="conString" providerName="System.Data.SqlClient" connectionString="Server=DESKTOP-EXAMPLE\DATABASE;Database=master;User ID=exampleuser;Password=password;MultipleActiveResultSets=True;" />

Jakub Dvorak · Accepted Answer · 2019-05-28 17:28:09Z

2

You muss allow user for iis in security. Here is oné video: https://youtu.be/rmVtxLKmkDU

I hope thats help you.

answered May 28, 2019 at 17:28

Jakub Dvorak

771 silver badge7 bronze badges

1 Comment

GreenSaber Over a year ago

I have added IIS in security, but it's still not connecting

Collectives™ on Stack Overflow

ASP.NET losing connection to SQL Server Express after being published to IIS

3 Answers 3

5 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related