Create Service Principle Connection from Crystal Reports to Azure Synapse Analytics

user_16898108 0
Viewed 84

I have data held in an Azure Data Lake Gen 2 storage container. I would like to provision this data for an existing report authored in Crystal Reports using SQL on demand.

During development I used my own Azure AD login via an ODBC connection on my local machine. I have access to the Synapse environment and also the data lake. This worked successfully and although slow, pulled all information required.

To deploy this solution correctly I need to remove my AAD creds and use a provisioned service principle. I have given the service principle to read from the data lake and also added the principle to the SQL database. Now I am stuck on how to use the principle to connect to Crystal Reports.

I have tried the same authentication type as with my AAD but now I am using a clientID not a email. So when the system prompts for connection details it wants you to sign in and does not accept the clientID.

Does anyone have any suggestions on how to connect to Crystal Reports using this way or any other way?

Also: My org does not want this user or app reg to have restricted permissions so therefore adding them to the RBAC "synapse admin" wont work.

Thanks Tom

azure-synapse crystal-reports
1 Answers

Found a way around this.

Create a service account user on Azure Portal. Head to Synapse Analytics and open blank SQL script to give the user minimal permissions.

*USE [master]*

CREATE LOGIN [serviceaccountsynapseuser@company.onmicrosoft.com] FROM EXTERNAL PROVIDER

GRANT CONNECT ANY DATABASE TO [serviceaccountsynapseuser@company.onmicrosoft.com]

GRANT SELECT ALL USER SECURABLES TO [serviceaccountsynapseuser@company.onmicrosoft.com]

*USE [Reporting] (Serverless SQL DB)*

CREATE USER [serviceaccountsynapseuser@company.onmicrosoft.com] FROM EXTERNAL PROVIDER

ALTER ROLE db_datareader ADD MEMBER [serviceaccountsynapseuser@company.onmicrosoft.com]

Finally head to the storage account and give the user storage blob reader role.

user_168981080
Related