2

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?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Steve Perrone
  • 21
  • 3
  • I will share your other post about `DB connect error`. You can refer my answer. I think it will help you. https://stackoverflow.com/questions/61644033/net-core-on-azure-cant-connect-to-sql-server-database/61650563#61650563 – Jason Pan May 30 '20 at 02:07
  • Make sure you can access your Azure SQL Server by SSMS. And you can use my way to solve the issue. – Jason Pan May 30 '20 at 02:11
  • 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 Jun 01 '20 at 12:49
  • Is my solutions works for u ? – Jason Pan Jun 02 '20 at 13:22
  • 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 Jun 03 '20 at 12:26

1 Answers1

0

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

enter image description here

enter image description here

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.

enter image description here

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 .

enter image description here

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.

Jason Pan
  • 15,263
  • 1
  • 14
  • 29
  • 1
    Hi Jason,Thanks for the explanation. Everything you described 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. Any idea? – Steve Perrone Jun 01 '20 at 12:47
  • @StevePerrone I have updated my answer. I also create a demo to test it and it works for me. You can try it and hope it will help or inspire you. – Jason Pan Jun 02 '20 at 07:43