Azure Active Directory authentication in the SQL Server connector

NOTE: You can read a longer version of this post in the PowerApps Blog here.  This is an abridged version.

We recently added support for Active Directory authentication in the SQL Server connector. This has been one of the most requested feedback from our customers. In fact, few customers already noticed this rollout and have started using it. In this post, I want to give an overview of how you can use this feature, and some of the underlying design changes we had to bring about in the platform.

Using Azure AD authentication for Azure SQL Database provides a lot of benefits when it comes to managing the security of your data. In the context of PowerApps and Flow, this feature will enable each user to connect to the underlying databases with their own credentials. The SQL Server connection using Azure AD authentication will not be shared when an app is shared.  This is similar to how authentication works for Office 365 Outlook, SharePoint and other Azure AD based services.

Using the feature in Microsoft Flow

In Microsoft Flow, this feature is available when you create a new SQL Server connection. When you create a new connection, you will be asked to choose an Authentication Type. Apart from SQL Server Authentication and Windows Authentication, you can now select “Azure AD Integrated (Preview)” authentication. Once you select that, you can sign in with you Azure AD account to create a connection. If you select any of the other authentication types, you will need to provide appropriate details.
Creating a SQL Server connection in Flow

After you select a connection, you need to specify the server and database as part of the action or trigger you are using. This allows you to use a single connection – associated with your Azure AD credentials – across multiple SQL Servers and databases. Note that we provide a dynamic drop-down for the Database parameter once you provide a valid SQL server. Once you provide the server and database, you can proceed to provide the rest of the parameters required for your operation.

TIP: If your credentials do not have access to list the database, you can select “Enter custom value” and type in the database name.

SQL connector in Flow - Database dropdown

Upcoming changes

This feature is in Preview now. As we see usage of this feature, we will make it production ready. Right now, there are a few areas we are still working on:

  • “App from data” experience in PowerApps
  • “Transform data using Power Query” experience in Microsoft Flow

If you use this feature, please do share your feedback. You can use our product forums or shoot us an email directly.