asp classic - Unable to Connect to Azure SQL DB from Azure App Service

Question

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?

Answer 1

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.