CODEWITHSUNDEEP
ssas-tabularazure-analysis-servicesmicrosoft-fabrictabular-editor
Rogero Wisehombre
Rogero Wisehombre

Reputation: 133

Connect Azure Analysis Services to Microsoft Fabric through SQL endpoint

I am trying to connect an Azure Analysis Services model to Microsoft Fabric through a SQL endpoint. When using my own account [email protected] I am able to get data. To schedule the processing of the model every night so I need some kind of authentication to the sql endpoint data source.

Analysis Services support these authentication options (through the gui):

I need to process the data every night and none of these options work. So I am trying to use a service principal and maybe put it in the connections string but no luck so far. The service principal has access I confirmed this with management studio.

I also tried if the are any options in Tabular Editor 2.19 but no luck so far.

Any solution that makes the model process without a filling in a password every day or doing a deployment every day would be nice.

Added: The connection from Analysis Services to the Fabric SQL endpoint after deployment to the server will only succeed while going through a gateway. Before that, in visual studio on the laptop, the connection works without a gateway. Without a gateway the server response when processes is: 'On-Premise Gateway is required to access the data source. Please install a unified gateway for the server [servername]or disable AlwaysUseGateway setting if it's enabled.'

Upvotes: 0

Views: 664

Answers (2)

JohnD
JohnD

Reputation: 1

I've spent too much time trying to write a PowerShell script to update data source settings before processing, and it's been a nightmare. From what I understand, there is currently no way to have unattended access for Azure Analysis to pull data from a SQL endpoint. You must use a Microsoft Account, which has a token expiry of one day, making it impossible to automate daily processing. If I'm mistaken, I'd appreciate clarification, as we've been looking into this for weeks. I understand the reasoning behind it, but not being able to use a Service Principal or Managed Identity in the .bim is very limiting.

Upvotes: -1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89341

Just follow the instructions for OAuth as if for Azure SQL Database here:

For tabular models at the 1400 and higher compatibility level using in-memory mode, Azure SQL Database, Azure Synapse, Dynamics 365, and SharePoint List support OAuth credentials. To generate valid tokens, set credentials by using Power Query. Azure Analysis Services manages token refresh for OAuth data sources to avoid timeouts for long-running refresh operations.

Data sources supported in Azure Analysis Services - OAuth Credentials

Upvotes: 0

Related Questions