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

问题描述

我们有一个在 Azure 应用服务中成功运行的 ASP 经典 Web 应用程序，该应用服务当前使用 SQL Server 身份验证来访问 Azure SQL 数据库.我们需要将身份验证更改为 Active Directory 用户.我们设法让这个应用程序在开发人员的工作站上访问 Azure SQL 数据库运行，看看哪些有效，哪些无效，如下所示.

* 不适用于桌面，也不适用于 Azure Conporttail.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 Conporttail.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 Conporttail.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 应用服务中进行这项工作，我们还缺少什么?

解决方案

更新

您可以关注

私人

您的 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; .

你可以在门户中找到它.

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

<connectionStrings><add name="DefaultConnection" connectionString="你本地数据库连接字符串或其他"/><add name="DefaultConnection11" connectionString="Data Source =**;Initial Catalog = {your db in server not azure};User Id = {userid};Password ={password};"/></connectionStrings>

您可以像我给出的代码一样配置您的连接字符串.当你想解散你的应用程序时.您可以切换到生产数据库，无需更改代码中的任何内容.有关详细信息，您可以查看

它的优先级高于web.config中的配置，并且会覆盖代码中的地址，所以这里设置好之后，你就不需要修改你的web.config文件了部署.

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.

* 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;"

* 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;"

* 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;"

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

解决方案

UPDATE

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

1. Create SQL managed instances (maybe cost long time)
2. Configure Active Directory admin
3. Configure your db

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.

Connection strings like below

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';

PRIVIOUS

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.

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>

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 .

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 应用服务连接到 Azure SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！