Using System Assigned Managed Identity for API imports into Azure SQL databases

For Azure SQL Databases there is a very cool new preview feature: “sp_invoke_external_rest_endpoint “. This function allows you to call certain Microsoft API endpoints directly from within your Azure database and write that data back into a table for example.

With that, you can for example create a stored procedure that can be triggered from Power Automate. This is ideal for larger datasets that would require long and slow “apply-to-each” rounds or cumbersome bulk-upload-workarounds.

I was struggling with the authentication when using a system assigned managed identity (“service principal”). Thanks to Davide Mauri for telling me how to fill in the parameters for the DATABASE SCOPED CREDENTIALS to make this work for Power BI:

Database Scoped Credentials

To connect to the Power BI Rest API, the scoped credential has to look like so:

CREATE DATABASE SCOPED CREDENTIAL [https://api.powerbi.com]
WITH IDENTITY = 'Managed Identity',
SECRET = '{"resourceid": "https://analysis.windows.net/powerbi/api"}'

If you want to connect to the Graph API instead, you must create one like so:

CREATE DATABASE SCOPED CREDENTIAL [https://graph.microsoft.com]
WITH IDENTITY = 'Managed Identity',
SECRET = '{"resourceid": "https://graph.microsoft.com"}'

To my understanding, this initializes the general connectivity between the SQL DB and the APIs. However, to make it work, you must also add the service principal as a user to the database and assign rights. I used the following script for that:

CREATE USER [<SP Name>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];                                                          
ALTER ROLE [db_owner] ADD MEMBER [<SP Name>];

With  <SP Name> being the name of the enterprise app that has being created automatically, when the system assigned managed identity has been created. This is identical with the (logical) Azure SQL server name.

Also, you must allow the service principal to access external endpoints from within the server:

GRANT EXECUTE ANY EXTERNAL ENDPOINT TO [<SP Name>]

and lastly, you must allow the service principal to use the scoped credential:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[<Scoped Credential Name>] TO [<SPName>];

This last command is a bit different than in the official documentation. Big thanks to Ilya Fedorkov for pointing out, that the reference to the DATABASE SCOPED CREDENTIAL had to be made.

Further potential pitfalls for API imports into Azure SQL databases

I strongly recommend to read through the whole documentation for this feature. Also the requirements on the specific endpoints site are important to study also.

But with regards to Power BI connectivity, here are some points to look out for (a complet list of limitations can be found here):

• Make sure to enable “Allow service principals to use Power BI APIs” and “Dataset Execute REST API” in the Power BI admin settings
• Add the service principal as admin to the workspace
• A specific limitation when connecting via a service principal is that row level security is not supported. This is a big limitation in many cases, unfortunately. So I hope that this limitation will be lifted in the future. So currently, you can only use it on datasets that don’t have any RLS rules.

Scripts

The script I finally used for calls to the Power BI Rest API looks like so:

declare @url nvarchar(4000) = N'https://api.powerbi.com/v1.0/myorg/groups/<WorkspaceID>/datasets/<DatasetID>/executeQueries';
declare @payload nvarchar(max) = N'{
"queries": [
{
"query": "EVALUATE VALUES(''Product'')"
}
]
}'

declare @ret int, @response nvarchar(max);

exec @ret = sys.sp_invoke_external_rest_endpoint
@method = 'POST',
@url = @url,
@payload = @payload,
@credential = [https://api.powerbi.com],
@response = @response output;

select * from openjson(@response, '$.result.results[0].tables[0].rows') with
(
"Product[ProductID]" int,
"Product[Category]" nvarchar(50),
"Product[Segment]" nvarchar(150),
"Product[ManufacturerID]" int,
"Product[Product]" nvarchar(150)
)

Have been following this instruction for it.

The script I used to fetch tasks for a planner plan:

declare @url nvarchar(4000) = N'https://graph.microsoft.com/v1.0/planner/plans/<PlanID>/tasks';
declare @ret int, @response nvarchar(max);

exec @ret = sys.sp_invoke_external_rest_endpoint
@method = 'GET',
@url = @url,

@credential = [https://graph.microsoft.com],
@response = @response output;

select * from openjson(@response, '$.result.value')
with
("title" nvarchar(200),
"dueDateTime" nvarchar(200),
"completedDateTime" nvarchar(200)
)

If parsing JSON in SQL Server is also new to you, check out this cheat sheet.

Bonus

To enable a system assigned managed identity for an existing logical server in the Azure portal, visit the server page, go to “Identity” and switch the status:

Enjoy and stay queryious 😉