ASP.NET SqlException when publishing in IIS Web Server

Question

I've just start working on an ASP.NET Web Application in Visual Studio 2015 and SQL Server 2014. Somewhere, I need to connect to my local database and I use this:

ConnStr = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=my_db;Integrated Security=True";

It works fine when I run the application through VS in my browser. I can access the db and do whatever I want to do. But, when I publish my application in IIS Web Server, and then I open it in browser, it still works OK until I have to access the db. At that moment it throws SqlException:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. .

Maybe it is a server name problem and I should use an IP and a Port instead of that name, but I'm not sure because I don't have experience on this.

Answer 1

You are correct: You should use server name and/or IP in your connection string.

Using something like "local" or "localhost" means that your code is not portable. Another option would be to store your connection strings in two separate config files - one for your local copy (for development and troubleshooting) and one for your server (for portability). I have two config files in my solutions:

• Web.config
• WebServer.config

Then, when I deploy to the server, I just delete Web.config and rename WebServer.config to Web.config. It's totally portable and you'll never have connection string troubles again!

Also noteworthy: you're not including credentials in your connection string, which means that you're using windows authentication when connecting to SQL server. When debugging through visual studio, the application will run as you - and if you have the needed permissions, it will work. However, when running in IIS, it won't be running as you (at least, it SHOULDN'T be) - so you could run into issues there, as well.

EDIT

This link might be useful for you: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

Your connection string should look like this:

Data Source=192.168.1.10,1433;Initial Catalog=YourDatabaseName;User ID=SqlUserName;Password=SqlPassword;Connection Timeout=60; Max Pool Size=3000; 

(you can set max pool size and connection timeout to whatever you want - or omit them entirely.

Also, regarding your windows issues - you need to make sure that the windows account IIS is using has permissions to traverse your network and reach your SQL Server instance. That said, I suggest that you use a SQL account instead of windows authentication.

Answer 2

Since you are using integrated security in connection string you will have to modify the Identity of App pool under which your application is running. The Identity will be your windows username and password. To change the username and password you need to go the advance settings of the app pool and process model you can see identity where you can add your windows credentials

Hope this helps