ASP。在IIS Web服务器上发布时的网络SqlException。

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:

我刚开始研究ASP。NET Web应用在Visual Studio 2015和SQL Server 2014中。在某处，我需要连接到我的本地数据库，我使用这个:

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

ConnStr = "数据源=(localdb)\ MSSQLLocalDB;初始目录=my_db;集成安全性=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:

当我在浏览器中通过VS运行应用程序时，它运行良好。我可以访问db并做我想做的任何事情。但是，当我在IIS Web服务器中发布应用程序，然后在浏览器中打开它时，它仍然可以工作，直到我必须访问db。这时它抛出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. .

System.Data.SqlClient。SqlException:在建立到SQL Server的连接时发生与网络相关或特定于实例的错误。没有找到或无法访问服务器。

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.

可能是服务器名问题，我应该使用IP和端口，而不是这个名称，但是我不确定，因为我没有这个经验。

4 个解决方案

#1

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

您是正确的:您应该在连接字符串中使用服务器名和/或IP。

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:

使用“local”或“localhost”之类的东西意味着您的代码是不可移植的。另一个选项是将连接字符串存储在两个独立的配置文件中——一个用于本地副本(用于开发和故障排除)，另一个用于服务器(用于可移植性)。我的解决方案中有两个配置文件:

Web.config
. config
WebServer.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!

然后，当我部署到服务器时，我只是删除Web。配置和重命名网络服务器。配置web . config。它是完全可移植的，你再也不会有连接字符串的麻烦了!

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.

还需要注意的是:在连接字符串中不包含凭据，这意味着在连接SQL server时使用windows身份验证。当通过visual studio进行调试时，应用程序将以您的身份运行——如果您有所需的权限，那么它将工作。然而，当在IIS中运行时，它不会像您一样运行(至少，它不应该是)——因此您也可能在那里遇到问题。

EDIT

编辑

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

这个链接可能对您有用:https://msdn.microsoft.com/en- us/library/system.data.sqlclient.sqlconnection.connection.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.

同样，关于windows问题——您需要确保windows帐户IIS正在使用的权限能够遍历您的网络并到达SQL Server实例。也就是说，我建议您使用SQL帐户而不是windows身份验证。

#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

由于您正在连接字符串中使用集成安全性，因此您必须修改应用程序运行时所在的应用程序池的标识。身份将是您的windows用户名和密码。要更改用户名和密码，您需要进入应用程序池和流程模型的预设置，您可以在其中看到可以添加windows凭据的标识

Hope this helps

希望这有助于

#3

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.

“集成安全”意味着您正在使用本机windows系统进行身份验证。类似于使用Windows密码在数据库上打开SSMS。IIS现在正在尝试连接到数据库，连接字符串告诉您使用IIS正在运行的进程。

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.

您可以在SQL Server中创建非windows用户，并将凭据放入连接字符串。或者您可以授予IIS用户DB特权。或者你可以做很多不同的事情，但是这些是最容易让你行动的。

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.

连接字符串中的第二个问题是数据源。本地机器上有SQL服务器吗?如果是这样，这就是它不能工作的原因。试着在VS中运行你的应用程序，但要在远程SQL服务器上运行。这应该是你的下一步。

#4

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.

问题是，我认为SQL Server是用VS自动安装的，或者至少是用SQL Server Management Studio安装的。但是没有。所以，据我所知，直到我知道我没有使用真正的SQL Server。当我检查SQL Server Configuration Manager时，没有任何SQL Server服务运行，所以我意识到我遗漏了一些东西。

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

然后，我安装了SQL Server Express并在那里构建我的db。现在，即使我发表了它，它也运行得很好。连接字符串

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

数据源= \ \ SQLEXPRESS;初始目录= my_db;用户名,密码的用户ID = =

It can also be:

它也可以是:

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

数据源= localhost \ \ SQLEXPRESS;初始目录= my_db;用户名,密码的用户ID = =

#1