无法从Azure App Service连接到Azure SQL DB [英] Unable to Connect to Azure SQL DB from Azure App Service

问题描述

我们有一个ASP Classic Web应用程序在Azure应用服务中成功运行，该服务当前使用SQL Server身份验证访问Azure SQL数据库.我们需要将身份验证更改为Active Directory用户.我们设法通过开发人员的工作站访问Azure SQL数据库使该应用程序正常工作，在下面查看有效的方法和无效的方法.

We have an ASP Classic web application running successfully in an Azure App Service that currently uses SQL Server Authentication to access the Azure SQL Database. We need to change the authentication to Active Directory user. We managed to make this application work from a developer’s workstation accessing the Azure SQL Database, see what worked and did not, below.

*不适用于桌面，不适用于Azure Conportail.Open"Provider = SQLOLEDB; Server = tcp:.database.windows.net，1433; Initial Catalog =; Persist Security Info = False;用户ID = @leg * .com ; Password =; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False;连接超时= 30; Authentication = ActiveDirectoryPassword;"

* Does NOT work on desktop and Does NOT works in Azure Conportail.Open "Provider=SQLOLEDB; Server=tcp:.database.windows.net,1433; Initial Catalog=; Persist Security Info=False; User ID=@leg*.com; Password=; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30; Authentication=ActiveDirectoryPassword;"

*不适用于桌面，不适用于Azure Conportail.Open"Provider = SQLOLEDB.1; Server = tcp:.database.windows.net，1433; Initial Catalog =; Persist Security Info = False;用户ID = @leg * .com; Password =; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30; Authentication = ActiveDirectoryPassword;"

* Does NOT work on desktop and Does NOT works in Azure Conportail.Open "Provider=SQLOLEDB.1; Server=tcp:.database.windows.net,1433; Initial Catalog=; Persist Security Info=False; User ID=@leg*.com; Password=; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30; Authentication=ActiveDirectoryPassword;"

*在桌面上可用，在Azure 中不可用 打开"Provider = MSOLEDBSQL; Server = tcp:.database.windows.net，1433;初始目录=;持续安全信息= False;用户ID = @leg * .com ; Password =; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False;连接超时= 30; Authentication = ActiveDirectoryPassword;"

* Works on desktop and Does NOT works in Azure Conportail.Open "Provider=MSOLEDBSQL; Server=tcp:.database.windows.net,1433; Initial Catalog=; Persist Security Info=False; User ID=@leg*.com; Password=; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30; Authentication=ActiveDirectoryPassword;"

要在Azure App Service中实现此功能，我们缺少什么?

What are we missing to make this work in Azure App Service?

推荐答案

更新

您可以遵循

You can follow the offical document to set in portal. I have try it and sucessed.

1. 创建SQL托管实例(可能会花费很长时间)
2. 配置Active Directory管理员
3. 配置数据库

完成后，您可以找到类似pic的连接字符串.您只需将其复制并粘贴到您的代码中即可.它对我有用.

When u have finished it, you can find connection string like pic. You just copy and paste it in your code. It works for me.

如下所示的连接字符串

Server=tcp:panshubeidb.database.windows.net,1433;Initial Catalog=dbname;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';

重要

您的SQL连接字符串应类似于Server=tcp:testdb.database.windows.net,1433;Initial Catalog=test;Persist Security Info=False;User ID=sasasa;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;.

Your SQL Connectionstrings should be like Server=tcp:testdb.database.windows.net,1433;Initial Catalog=test;Persist Security Info=False;User ID=sasasa;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30; .

您可以在门户网站(如图片)中找到它.

You can find it in portal like pic.

您还可以在web.config中配置连接字符串的名称.

You also can configure your connectionstring's name in web.config.

<connectionStrings>
    <add name="DefaultConnection" connectionString="You local db connnection strings or others" />
    <add name="DefaultConnection11" connectionString="Data Source =**;Initial Catalog = {your db in server not azure};User Id = {userid};Password ={password};" />
</connectionStrings>

您可以像我给定的代码一样配置Connectionstrings.而且，当您想取消应用程序的所有权时.您可以切换到生产数据库，而无需在代码中进行任何更改.有关更多详细信息，请参见本文.

You can configure your Connectionstrings like the code I given. And when you want to depoly your apps. You can switch to your production database, and don't need change anything in your code. Fore more details, you can see this article .

它的优先级高于web.config中的配置，并且它将覆盖代码中的地址，因此在此处进行设置后，部署时无需修改web.config文件.

Its priority is higher than the configuration in web.config, and it will cover the address in the code, so after setting it here, you do not need to modify your web.config file when deploying.

这篇关于无法从Azure App Service连接到Azure SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！