Azure 上的 .NET Core 无法连接到 SQL Server 数据库 [英] .NET Core on Azure can't connect to SQL Server Database

问题描述

我有一个在 Azure 上发布的 .NET Core API 后端.我还有一个在 Azure 上运行的 SQL Server 数据库，如果我在本地运行我的后端应用程序，它会成功连接到在线数据库，读/写等工作正常.但是，在运行在线后端时，每个 API 调用都会导致 500 错误.查看日志时，有以下错误可能导致 500:

Microsoft.Data.SqlClient.SqlException (0x80131904):建立与 SQL Server 的连接时发生与网络相关或特定于实例的错误.服务器未找到或无法访问.验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接.(提供程序:命名管道提供程序，错误:40 - 无法打开与 SQL Server 的连接)

因此，出于某种原因，Azure 上托管的后端无法访问此数据库.在我的在线数据库的 Azure 门户中，我已将后端的 IP 添加到防火墙例外中，并选中了允许远程 Azure 连接到服务器".

TLDR;本地一切正常，Azure 上发布的版本无法连接到/查找数据库.

已解决，感谢所有评论者.(特别感谢杰森！)解决方案是使用以下格式的连接字符串:

Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=db;Persist Security Info=False;User ID=user;Password=mypassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;连接超时=30;

解决方案

UPDATE

我按照教程操作，详细步骤见截图.

第一步.修改代码中的连接字符串，并添加一个接口进行测试.

第 2 步.为 peoject 创建一个 Todo 表.[确保您已完成防火墙设置]

第 3 步.在本地测试然后部署.可以看到我的项目运行成功，界面可以使用了

隐私

你可以去你的 sqldb ，找到 Connection strings 并复制那里提供的那个.

该字符串的格式为:

Data Source=tcp:servername.database.windows.net,1433;初始目录=db;持久安全信息=假；用户 ID=用户；密码=我的密码；MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;连接超时=30；

还有 有相同问题的帖子.更多详情，你可以阅读.

I have a .NET Core API backend which is published on Azure. I also have a SQL Server database running on Azure, if I run my backend app locally it succesfully connects to the online database, reading/writing etc works fine. When running the online backend however, every API call results in a 500 error. When looking in the logs there is the following error which probably causes the 500:

Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

So for some reason the backend hosted on Azure does not have access to this database. In the Azure portal of my online db I have added the IP of my backend to firewall exception and "Allow remote Azure connections to server" is checked.

TLDR; everything works fine locally, published version on Azure can't connect to/find database.

Edit: this is resolved, thanks everyone who commented. (special thanks to Jason!) Solution was to use the following format of connection string:

Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=db;Persist Security Info=False;User ID=user;Password=mypassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

解决方案

UPDATE

I operate according to the tutorial, see the screenshot for detailed steps. The official documentation contains sample code, which you can download and see. I put my conn string in my code.

If you configure your connnection strings in web.config or other files, u can see this document. And u can configure it in your portal.

Step 1. Modify connection strings in code, and add a interface for test.

Step 2. Create a Todo Table for peoject. [Make sure you have finished Firewall setting]

Step 3. Test in local then deploy. You can see my project run successfully and interface can be used.

PRIVIOUS

You can go to your sqldb ,find Connection strings and copied the one provided there.

This string was in the format:

Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=db;
Persist Security Info=False;
User ID=user;Password=mypassword;
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;
Connection Timeout=30;

There also have a post which has the same issue. For more details,u can read it.

这篇关于Azure 上的 .NET Core 无法连接到 SQL Server 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！