-2

Currently I have everything on premise - .NET Core 3.0 API and SQL Server database.

Azure AD/Service account are configured in IIS and database has given access to the service account.

All requests use that Azure AD service account.

Now we are planning for an Azure migration. API is published in App Services.

Now I want to use the same Azure AD account to connect to an Azure SQL Server database.

How should I do that?

Don't send me Microsoft links, those are having toooo much info.

Tell what and how to do configuration in Azure SQL and App Services.

Any help is appreciated.

Praveen
  • 25
  • 1
  • 8

1 Answers1

3

Now I want to use the same AD account to connect to an Azure SQL Server database.

AD accounts can't connect to Azure SQL Database. Only SQL and Azure Active Directory (AAD) Logins/Users.

There are some options, but there's a clear best-practice: Provision a Managed Service Identity (MSI) for your Azure App Service Application and use that to connect to SQL Server.

Here is a simple tutorial for how you should integrate App Service and Azure SQL Database:

Tutorial: Secure Azure SQL Database connection from App Service using a managed identity

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Well that links is MS link, toooo much info, well I'll give a try again. Does any other link which have straight or simple steps. Basically No SQL user, Azure AD or any other way to connect DB – Praveen May 05 '20 at 16:26
  • The only two ways to connect are using a SQL User or an AAD User. You have to create the user in SQL Database in both cases, but for a SQL User you also have to store the user name and password somewhere secure and retrieve them at runtime. – David Browne - Microsoft May 05 '20 at 17:16
  • Thanks, SQL user that's what we avoiding. AAD user we wants to do. Like we set user to the app pool and it works on premise. Similar way need to do for azure app service and azure sql server connection with AAD account – Praveen May 05 '20 at 18:47
  • Yep. That's what the tutorial does. An MSI is like a App Pool user, except you never have to manage the password. – David Browne - Microsoft May 05 '20 at 19:31
  • Thanks, I am following that,The service account in the list when I execute _az ad user list --query [].userPrincipalName_ **SVC-NET@Mycompany.onmicrosoft.com**, BUT in next steps it says [link](https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#grant-database-access-to-azure-ad-user) Add this Azure AD user as an **Active Directory admin** using az sql server ad-admin create command in the Cloud Shell. I should not do that. I need valid reason to add my service account as Admin in SQL, the account has read and execute permission in database, please guide – Praveen May 10 '20 at 12:43
  • Correct. You should choose a different AAD account to be the AAD admin of your SQL instance. Probably yourself or some AAD Security group you are a member of. Having an AAD admin for your SQL Server is a prerequisite to adding any AAD users to your databases. – David Browne - Microsoft May 10 '20 at 12:49
  • I have app service and azure SQL server created already, do I sill need to create new resource group? I don't have access to create new resource group, normal AD user has contribute access on SQL and App service. – Praveen May 10 '20 at 12:51
  • You can use your existing Azure SQL Server, Resource Group, and App Service. – David Browne - Microsoft May 10 '20 at 12:51
  • The SQL server has Admin see image [Link](https://drive.google.com/file/d/1meB27a_b0yxXhqpvpR4n06GRXOSXBpTg/view?usp=sharing) I don't have password for that Active Directory admin, but let me know what steps I need to do to add my Azure AD account to execute the SP and SQL functions? I think I am almost there, please guide – Praveen May 10 '20 at 13:05
  • You need to connect as the AAD admin to run `CREATE USER [] FROM EXTERNAL PROVIDER`. If you don't have access to that AAD admin account, you'll need to either change the AAD Admin from the Azure portal, or get that AAD user to do it for you. – David Browne - Microsoft May 10 '20 at 13:16
  • Thanks @David, it was really helpful. Able to connect using Managed Identity from app service to Azure SQL. Just last question - **can we connect to SQL (on premise) from Azure app service using AD account?**, Hybrid connection is there able to reach the server but not able to login using using AD account, SQL user working. – Praveen May 26 '20 at 14:24
  • Not with .NET, ODBC or OLEDB. All require a windows logon, or at least a `runas /netonly` for Windows Authentication. JDBC now supports NTLP with user-supplied password. – David Browne - Microsoft May 26 '20 at 14:28
  • You say this is a "simple" tutorial? Hardly, and it also doesn't work from a local dev machine perspective. Tried it all and at the end the "Microsoft.Data.SqlClient.SqlConnection" object on .Open() throws and exception "An error occurred creating the configuration section handler for SqlAuthenticationProviders: Could not load type 'System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'" for .NET Core 3.1 – user3683706 Jun 01 '20 at 20:45