General Availability of the Azure AD SQL Server connector

We are pleased to announce the general availability of the Azure AD support for the the SQL Server connector.   The Azure AD authentication for Azure SQL Database provides significant security benefits for Power Apps and Power Automate authors and users.   This feature enables each user to connect to a SQL Azure database with their own credentials. The SQL Server connection using Azure AD authentication is not implicitly shared when a Power 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. You can now select “Azure AD Integrated” authentication. Once selected, you can sign in with your Azure AD account to create a connection.

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.

Using the feature in Microsoft Power Apps

In Power Apps, this feature is now enabled by default.  All new applications have this capability.  If you have an existing application that you need to convert to use this new authentication, you must enable it the the File > App settings > Advanced settings section.  To enable this feature, open your app in studio and enable the “Enable enhanced Microsoft SQL Server connector” feature.

To the use the Azure AD Integrated authentication select the “Azure AD Integrated” option from the drop-down. Click on “Create” to create a connection. Note you may need to sign in to Azure AD.  Once the connection is created, you can specify the SQL server and database name and connect to it.

Once you are connected to a database, you will be able to select the tables and views you want to add in your app from a list. This experience and the rest remain the same as before.

Once you build the app, you can share the app. When you use Azure AD authentication with SQL Server, the connection will not be shared with the shared users.

The user to who you have shared the app with, will need to login with their own credentials.

Other changes in the SQL Server connector

Along with enabling Azure AD Integrated authentication,  we also updated the SQL server connector as well.  These are GA with this announcement as well.  In particular, we have have better support for SQL datetime, datetime2, and smalldatetime data types.

Specifically, while ODATA 2.0 has support for these types with Edm.DateTime, it was deprecated in ODATA 4.0. A datetime requires a timezone specifier as well – that way, every system knows the exact moment.  This is useful for building an API.  For our connector however we do not control the data source.  And there are too many SQL databases using the SQL datetime types. the way we have previously implemented them.  We used to map these SQL types to ODATA Edm.DateTimeOffset and using a JSON string as defined in RFC 3339 and in ISO 8601. This led to user confusion and errors because of timezone differences between the client, the data source and the normalization done by the platform in between. To solve this, we introduced a custom data type identifier in our platform which allows us to normalize the data as strings in appropriate format. We consider this a ‘breaking’ change – something that necessitate us to introduce a major API version. In future, we will be adding delegation support for these types as well.

We hope these updates address a long standing ask from our customers. Please feel free to reach out in our product forums for any feedback.