3

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.

Dionis Beqiraj
  • 737
  • 1
  • 8
  • 31

4 Answers4

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.

Stan Shaw
  • 3,014
  • 1
  • 12
  • 27
  • I created a non-windows user but still the same problem. I can access the db in VS but not when I publish it in IIS. Let's suppose that my IP is 192.168.1.10 and the SQL Server Port is 1433. How should the Data Source be in this case? – Dionis Beqiraj Dec 16 '15 at 22:09
1

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

Mohan
  • 71
  • 4
1

There are two problems in you connection string:

"Integrated Security" means you are using the native windows system for authentication. Similar to opening SSMS on the database using your Windows password. IIS is now trying to connect to the database, and connection string is telling to use the process that IIS is running under.

You can create a non-windows user in SQL Server and put the credentials into the connection string. Or you can grant the IIS user DB privileges. Or you can a lot different things here, but theses are the easiest to get you moving.

THe second problem in the connection string is the data source. Is there SQL Server on you local machine? If so that's why it's not working. Try to run your app in VS but against the remote SQL Server. That should be your next step.

Robert-Ryan.
  • 61
  • 5
  • I created a non-windows user but still the same problem. I can access the db in VS but not when I publish it in IIS. Is there anything else that I ca do? – Dionis Beqiraj Dec 16 '15 at 22:07
0

The problem was that I thought that SQL Server was installed automatically with VS or at least with SQL Server Management Studio. BUT NO. So, as far as I understand, till know I have not worked with a real SQL Server. When I checked SQL Server Configuration Manager there were nothing running at SQL Server Services and so I realized that I was missing something.

Then, I installed SQL Server Express and build my db there. Now it is working fine even when I publish it. The connection string is

Data Source=.\\SQLEXPRESS;Initial Catalog=my_db;User ID=username;Password=pass

It can also be:

Data Source=localhost\\SQLEXPRESS;Initial Catalog=my_db;User ID=username;Password=pass

Dionis Beqiraj
  • 737
  • 1
  • 8
  • 31
  • Glad you got it worked out! You should basically never use the "built-in" SQL Server that comes with visual studio. Express is free (and the code will be portable), so use that as a last resort if you don't have a full-blown install. Also, please remember to accept the answer that helped you the most so that future users will be able to easily find it. Welcome to the forum and best of luck! – Stan Shaw Dec 17 '15 at 20:07