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

问题描述

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

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

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

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

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

 数据源= tcp:servername.database.windows.net，1433;初始目录= db;持久安全信息= False;用户ID =用户;密码= mypassword; MultipleActiveResultSets = False;加密= True; TrustServerCertificate= False;连接超时= 30; 

解决方案

更新

我根据教程进行操作，有关详细步骤，请参见屏幕截图.

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

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

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

重要

您可以转到sqldb，找到连接字符串并复制那里提供的内容.

此字符串的格式为:

 数据源= tcp:servername.database.windows.net，1433;初始目录= db;持续安全信息= False；用户ID =用户;密码=我的密码;MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False;连接超时= 30； 

还有

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屋！