Writeback to Azure SQL Database using service principal
App Registration
Create an app registration Service Principal using Microsoft's tutorial (specification of Redirect URI is not required):
https://learn.microsoft.com/en-us/entra/identity-platform/howto-create-service-principal-portal
NB! Make suree to copy the Secret value right after it has been created - it cannot be retrieved later.
Make a note of the following information, for use in accoPLANNING configuration:
ClientID
Shown in Overview page of app registration
ClientSecret
Manually copied right after it is created (see note above)
TenantID
Shown in Overview page of app registration
Permissions
The Service Principal can be granted permissions in either of two levels:
Simple
Granular
Simple
Grant the Service Principal the Contributor role for the Azure SQL Server hosting the relevant database.
Granular
For database "master", run the following T-SQL:
CREATE LOGIN [My Service Principal]FROM EXTERNAL PROVIDER
For the database where the writeback tabel is located, run the following T-SQL:
CREATE USER [My Service Principal] FROM LOGIN [My Service Principal];ALTER ROLE [accoDW Readers] ADD MEMBER [My Service Principal];GO
Configure accoPLANNING
In accoPLANNING select Service principal authentication in the connection tab, and provide authentication information using Service Principal values:
ClientID
ClientSecret
TenantID
You are now ready to perform writeback using Service Principal authentication.