Azure Active Directory authentication in the SQL Server connector

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.

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 PowerApps

In PowerApps, this feature is not enabled by default, but is made available as a preview feature. To enable this feature, open (or create) your app in the studio. Then, navigate to File > App settings > Advanced settings > Preview features. There, make sure you turn on the “Enable enhanced Microsoft SQL Server connector” feature. You need to do this for every app.

Once you enable this feature, add a new SQL Server data source in your app. You may need to create a new connection (if you haven’t already). As part of the connection creation, you will now be prompted to select the authentication type. Select “Azure AD Integrated (Preview)” option from the drop-down. Click on “Create” to create a connection – you may need to sign in to Azure AD when you do that. Once the connection is created, you can specify the SQL server and database names and connect to it.

Once connected, 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.

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.

A little background and changes to the platform

For those of you who want a little more detail, I can share some of the design changes we introduced to support this feature. We started working on this feature several months back – and it has taken us quite some time to roll out this feature.

In PowerApps and Flow, you create a connection in order to use any connector from an app or a flow. A connection typically contains credentials and is defined by the connector. For the SQL Server connector, the connection parameters included the server name, the database, username and password (amongst others). It nicely abstracts all the details you would typically need to connect to a SQL database.  We could implement ‘test connection’ functionality so that we can tell you immediately if you didn’t provide the right connection parameters. When an app is shared with other users, the associated connection is also shared. That way, the end users do not have to know the connection details.

When we started looking into supporting Azure AD for SQL connector, we needed to ensure a few things –

• Existing scenarios that uses SQL Authentication should continue to work
• Every user of an app should be able to connect to the underlying database with their own credentials

This presented a couple of challenges for us.  We need to decide based on the “authentication” type, how the connection creation behavior should behave. For example, for an Azure AD authentication, we need to kick off an OAUTH authentication flow where the user will sign-in to Azure AD.  Also, based on the “authentication” type of the connection, we now need to change the connection sharing behavior. So far, the behavior is dependent on the connector – each connector can tell whether a connection can be shared or now.  Third, we want a design that scales. We have hundreds of connectors, and therefore, we need to come up with constructs that work across connectors. Particularly, we know that we want to support different authentication models in a single connector. For example, we want to add support for SQL connection strings, authentication using a service principal, etc. And finally, we need a mechanism to ensure that the server and database details come the app itself – since an app will be used by different user’s connection. We cannot expect the end users to know the underlying server/database details used by an app.

To accommodate these challenges, we introduced a few things onto the platform—

• Concept of a connection parameter set – which is used to model authentication types, and
• Protocol changes to handle server and database from an app

Each connector can now advertise multiple connection parameter sets – for instance, for different authentication types. This solves the main challenge of providing independent authentication type that may have different behavioral requirements – whether in connection creation, or in app sharing scenarios. This also allows us to add more authentication types in future without having to worry about impacting the connection creation experience for other authentication types. And because this is such a fundamental change, we need to update multiple components in our architectural stack – from our connector resource model, to the connection management resource providers, to the various end user experiences we have today in the portal and mobile.

All tabular data sources in PowerApps, like the SQL Server connector, implements a modification of the ODATA protocol. When a tabular data source is added to an app, the app simply asks for a list of available tables (or entities). This used to work because the connection contained everything that we needed to connect to the underlying data source – the server, database and user credentials. Now that each user brings their own credential, the server and database information need to come from the app. This necessitate the need to introduce additional operational parameters for the server and database. And we had to do this in a way so that existing apps and connections continue to work.

Other changes in the SQL Server connector

Along with the above platform changes, we also updated the SQL server connector as well. We introduced a major version of our underlying API, while still ensuring that the old API endpoints continue to work. The new updated API for the SQL Server connector also provides one additional major change – better support for SQL datetime, datetime2 and smalldatetime types. Unfortunately for us, while ODATA 2.0 have support for these types with Edm.DateTime, it has been deprecated in ODATA 4.0. Basically, a datetime requires a timezone specifier as well – that way, every system knows the exact moment. This makes a lot of sense for anyone building an API surface. For our connector though, we do not control the data sources. There are too many SQL databases that uses these SQL datetime types. 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.

 

Thanks!
Sameer