Creating Azure Synapse Link for Dynamics: Step-by-Step Guide

ADF, Code, DataVerse, Dynamics, PowerShell, Synapse Analytics 5 Minutes

I had an interesting morning trying to connect Dynamics to a Data lake. As you might know, there was a feature where Dynamics would create a Data lake for you. This choice will cease to exist quite soon and to offer alternatives, you can create an Azure Synapse Link or Fabric Link.

This blog will cover the creation of the Azure Synapse Link.

Prerequisites

I had to scroll through multiple documents to get it all together, but you need quite some permissions to get it all working. Source: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-msi

  • Azure CLI is required on your local machine
  • Azure Az PowerShell module
  • Azure Az.Resources PowerShell module
  • Power Platform admin PowerShell module
  • Go to this compressed folder file on GitHub. Then select Download to download it. Extract the compressed folder file to a computer in a location where you can run PowerShell commands. All files and folders extracted from a compressed folder should be preserved in their original location.
  • Create a separate Storage Account in a resource group
  • Check on policies limiting regions where you’re allowed to deploy resources. The Enterprise Policy has a some different location options (for instance just europe instead of west europe).
  • Dynamics and Synapse need to be in the same tenant to work. Cross tenancy does not work.
  • Permissions:
    • Azure subscription Owner
    • Azure resource group Owner (which is inherited from the subscription ownership)
    • To access the enterprise policies, the global or Azure Key vault admin is required to grant the Reader role to the Dynamics 365 or Power Platform admin.
    • User Access Administrator  or Owner to grant permissions to identities
    • Power Platform administrator or Dynamics 365 administrator role
    • Azure Data Lake Storage Gen2 Owner
    • Azure Synapse administrator
    • Dataverse system administrator 
    • Azure Data Lake Storage Gen2 account and Owner and Storage Blob Data Contributor

First steps

At the end of this blog I’ll add the full code, this is the walkthrough part.

To get it all running, start by installing the necessary PowerShell modules. 

Install-Module -Name Az
Install-Module -Name Az.Resources
Install-Module -Name Microsoft.PowerApps.Administration.PowerShell

Next, login to the correct Azure Tenant

 az login --tenant YourIdHere

If you have access to more than one subscription, you can set the default subscription like this.

 Update-AzConfig -DefaultSubscriptionForLogin YourSubscriptionHere

The following script sets up some basics in your subscription and resource group.

 .\SetupSubscriptionForPowerPlatform.ps1 - subscriptionId YourSubscriptionHere

Next comes the interesting stuff. You need an Enterprise Policy for Dynamics to connect to Azure. This is why you need a load of extra permissions. I hope you’ve made sure there are no policies blocking the roll out of the policy based on location.

 cd .\Identity\
 
 .\CreateIdentityEnterprisePolicy.ps1 `
	-subscriptionId YourSubscriptionHere `
	-resourceGroup YourResourceGroupNameHere `
	-enterprisePolicyName YourPolicyNameHere `
	-enterprisePolicylocation europe

Next you need to create a role assignment. The identity that will have this role assignment needs to have Power Platform permissions as well. You can find the Object Id in Entra ID.

 New-AzRoleAssignment `
	-objectId ObjectIdOfYourUser `
	-RoleDefinitionName Reader `
	-Scope /subscriptions/YourSubscriptionHere/resourceGroups/YourResourceGroupNameHere /providers/Microsoft.PowerPlatform/enterprisePolicies/YourPolicyNameHere

Finally, you need to create a new identity that Dynamics can use. The environment ID can be found in the address bar of your browser when you’re logged into the PowerApps environment.

https://make.powerapps.com/environments/IdIsHere/home

The PolicyArmId the code requests (which isn’t in the documentation as per july 26th 2024) is the same link you’ve used for the scope. 

.\NewIdentity.ps1 '
	-environmentId YourPowerAppEnvironmentID here `
	-policyArmId /subscriptions/YourSubscriptionHere/resourceGroups/YourResourceGroupNameHere /providers/Microsoft.PowerPlatform/enterprisePolicies/YourPolicyNameHere

Back to PowerApps

If you’ve managed to let all these scripts run successfully, the next step is to create an Azure Synapse Link inside PowerApps and create the connection. This should work and then you have to select the tables you want to extract from Dynamics into your Data lake.

You can stop here and just keep all the CSV files as is or dig into further configuration.

Synapse intricacies

After publishing this post, I found some weird things when reading data from the Synapse Serverless environment. Time for an update of this post!

When you allow Dynamics to push data to your datalake and disclose them through Synapse Analytics, there are a few things to be aware of.

Tables and views

When you open the Object Explorer in Sql Server Management Studio, you’ll see the tables you selected from Dynamics. Open the views and you’ll see almost the same ones, except that these have a ‘partitioned’ extension. According to the docs, these partitioned tables are akin to snapshots that are made every hour. The tables are near real time. This means reading from the table can return an error if Dynamics is writing out data. Apparently (we’re still testing this) the snapshot shouldn’t suffer from this.

Accounts

When you log into SSMS with your Entra ID account, there’s a chance you can’t read the data. If you’re using a SQL Account, you will never be able to in this case. Why? For the simple reason that Dynamics creates the tables and views without an identity that has the permission to read the data in the data lake. You, as a login, need that permission. It’s not delegated. As you can’t grant permissions on a data lake to a local Sql account, you need to use an Entra ID account. This account needs access to either Synapse or the Serverless Sql Endpoint by being added to the Logins. It also needs Storage Blob Reader permissions to read the data in the storage account or you need to add the account in the Access Control List of the container. As we’re using a separate storage account for Dynamics, we went for the Storage Blob Reader.

What if a tool can only support Sql Accounts?

I ran into this, a legacy tool didn’t support Entra ID. But there’s a work around for that. Create an ODBC connection to the Synapse Serverless using a user assigned managed identity.

  • Create the User Assigned Managed Identity (UAMI); more info
  • Grant the UAMI permissions on the data lake and Synapse
  • Connect the UAMI to the VM this tooling is running on
  • Download the latest SQL ODBC Driver here
  • Install the driver
  • Create a new connection to the Serverless Endpoint
  • Find the Object ID of the UAMI and paste that in the user name textbox; more info
  • Validate the connection
  • Use the ODBC Connection in your tooling

This way you can still read all the data and enjoy the many millions of columns Dynamics offers you.

Full code

As promised, the full code including some variables to save you from copy-pasting the same things over and over again. Be mindful about having to re-authenticate for every code block. At least that happened to me, it might have been because of my browser running in privacy mode.

# Get the Modules

Install-Module -Name Az
Install-Module -Name Az.Resources
Install-Module -Name Microsoft.PowerApps.Administration.PowerShell

# Set some variables to Reuse

$TenantId = YourTenantIdHere
$SubscriptionId = YourSubscriptionIdHere
$EnvironmentId = YourPowerAppsEnvironementIdHere
$ResourceGroupName = YourResourceGroupNameHere
$EnterprisePolicyName = YourPolicyNameHere
$EnterprisePolicyLocation = YourLocationHere

# Login to Tenant
 az login --tenant $TenantId

#Set Default Subscription

 Update-AzConfig -DefaultSubscriptionForLogin $SubscriptionId
 
 # General Setup stuff
 
 .\SetupSubscriptionForPowerPlatform.ps1 - subscriptionId $SubscriptionId
 
 # Create the Enterprise Policy
 
 cd .\Identity\
 
 .\CreateIdentityEnterprisePolicy.ps1 `
	-subscriptionId $SubscriptionId `
	-resourceGroup $ResourceGroupName `
	-enterprisePolicyName $EnterprisePolicyName `
	-enterprisePolicylocation $EnterprisePolicyLocation

#objectId = ID of current user
#scope can also be retrieved from the output of the Create Identity EnterprisePolicy

 New-AzRoleAssignment `
	-objectId YourUserObjectIdHere `
	-RoleDefinitionName Reader `
	-Scope /subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.PowerPlatform/enterprisePolicies/$EnterprisePolicyName
	
# EnvironmentId is the Dynamics Id, this can also be retrieved from the address bar when logged into the correct environment.
# https://make.powerapps.com/environments/FindIdHere/home
# PolicyArmId is the same as the earlier used scope.

.\NewIdentity.ps1 '
	-environmentId $EnvironmentId `
	-policyArmId /subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.PowerPlatform/enterprisePolicies/$EnterprisePolicyName
Unknown's avatar

Published by reitsees

Reitse began his computer days with GW Basic and quickly followed with Windows 3.11. After that many computers followed and, interrupted by human resource and psychology studies, he got into the IT work field around 2007. There he met Oracle 9. With a command line from 1980. At his current job he met SQL Server. And loved it. Now he’s working with SQL 2008 to SQL 2019 on-premises and a number of Azure SQL Databases, supporting customers and tuning databases. New projects focus more on the Azure data platform as an architect, security advisor and data engineer.

Published

Leave a comment