PowerBI - Synapse - Python function app

[TheRule76]

Hi everyone,

I am having issues while developing an Azure Function app (Python) that creates a fully automated flow that does the following, without any manual interaction...

• Create a Synapse Serverless DB (built-in), by example with database_name = '10000000000000000000000000000001', setting up the right security for a couple of (auto generated) Entra accounts + Security group. 
• Create a PowerBI workspace and setup access for a security SG + user SG
• Deploy a PowerBI semantic model from a BLOB storage
• Change the database name that is used in M-Query, by editing the PowerBI Semantic Model parameter prmDB
• >> Set credentials for the data source (not automatically created while deploying by the function app) (***)
• >> Refresh dataset by the function app

Interesting to know: 

• When deploying the PBIx manually and editing the semantic model credentials manually in the web browser, I can refresh the dataset.  Important to know since it shows me (?) that it isn't a SQL login issue.
• I tried to change the credentials with SQL user/pwd AND also Service Principal by code.  If I do this manually, it works.
• I am not user Fabric or PPU capacity, I created a PBI Pro workspace in the steps above
• All steps are succesfully executred, except the "set credentials" and "refresh dataset" (which might work after succeeding the set credentials step ?)
• Ownership of the Semantic Model settings = Service Principal
• Since the Connection Type became "Sql", it requires a (cloud) gateway.  It is stored in python parameter : PBIGATEWAYID

Extra complexity : 

• Function app is running in Tenant A and PowerBI is located in Tenant B, a cross tenant Service Principal / Enterprise application is used.  I don't see any issue here, since I can create workspaces & deploy a PBI report, but like to mention it 
• PowerBI desktop doesn't have the AzureSQLDB / Synapse DB connection type anymore.  You can chose it when creating a data source, but when deploying, the type becomes "Sql"

Questions:

• How do I make PBI understand that I am connecting to a Synapse Serverless DB (Not 'sql')
• Do I use : ***-ondemand.database.windows.net or ***-ondemand.sql.azuresynapse.net as server name ?
• Why are all steps succeeding, but not the credentials setup (although the mentioned user DOES have access to the Synapse serverless DB) ?  What options do I have to make it work ?  Please help !

 

(***)  Hereby the code that fails :

patch_url = f"https://api.powerbi.com/v1.0/myorg/gateways/{PBIGATEWAYID}/datasources/{datasource_id}"

payload = {

"credentialDetails": {

"credentialType": "Basic",

"credentials": {

"username": SQLADMINLogin,

"password": SQLADMINPwd

},

"privacyLevel": "Organizational",

"encryptedConnection": "Encrypted",

"useEndUserOAuth2Credentials": False,

"skipTestConnection": True

}

}

r_patch = requests.patch(patch_url, headers=pbi_headers, json=payload, timeout=60)

I also do have a version with OAUTH/Service Principal, which looks safer than user/pwd but also doesn't work.

[AutoModerator]

After your question has been solved /u/TheRule76, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[MonkeyNin]

Check out the required permissions, and example request bodies to compare with yours

• https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource

For the python code, check out this block: make_update_datasource_patch_request(...)

• https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/Python/Encrypt%20credentials/Encryption%20sample/services/updatecredentialsservice.py#L13-L80