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?

I will share your other post about DB connect error. You can refer my answer. I think it will help you. stackoverflow.com/questions/61644033/… — Jason Pan
Make sure you can access your Azure SQL Server by SSMS. And you can use my way to solve the issue. — Jason Pan
Everything you described in your article works for SQL User Authentication. It does not with for Active Directory User Authentication. My issue is specific to Active Directory User. The AD User authentication works with SSMS and from a non-Azure IIS server. It does not work from an Azure Web Service. — Steve Perrone
HI Jason, the solution does not for for us. I think it has to do with ASP Classic. I see that you used C# code to connect. We will attempt a connection with C# to see if the issue is related to ASL Classic. Thanks, — Steve Perrone

Jason Pan Jason Pan · Accepted Answer · 2020-05-30T02:01:10

UPDATE

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

Create SQL managed instances (maybe cost long time)
Configure Active Directory admin
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.

Unable to Connect to Azure SQL DB from Azure App Service

1 Answers