How to connect to SSMS Analysis Services using a Personal Access Token from Azure Devops ?

[omkarsawant307]

I need to run partitions in SSMS for my huge Power bi dataset of 755m rows.

We received a suggestion that instead of using Microsoft Entra ID , we should use Personal access token to login in SSMS so that we dont face below problem -

If Microsoft Entra ID credentials are used to connect to Power BI, please note that these tokens are short-lived, typically expiring within one hour. After this time, you must manually generate a replacement Microsoft Entra ID token. This refresh cycle might interrupt the long-running queries or cause the connection to fail during large data fetch operations. Kindly explore Personal acces token method.

If i choose Analysis services, only 3 options are there to connect - Windows authentication, Microsoft Entra MFA , Microsoft Entra password. In First 2 options i cannot use a Token, so i used it as a password in 3rd one (Microsoft Entra password) but it gives authentication error

[Poojara_D12]

Hi @omkarsawant307 

Unfortunately, SSMS (SQL Server Management Studio) doesn't support using Azure DevOps Personal Access Tokens (PATs) to connect to Analysis Services. The supported authentication methods for SSMS Analysis Services are Windows Authentication, Microsoft Entra ID (password-based), or Microsoft Entra MFA, and PATs don’t fit into these methods.

For your use case of running partitions on large Power BI datasets, here’s a practical approach to avoid token expiry issues:

1. Use a Service Principal:
   Set up a Service Principal in Microsoft Entra ID, grant it the necessary permissions to the Power BI workspace or dataset, and use its credentials to authenticate. Service Principals provide long-lived credentials, avoiding the 1-hour expiration issue with Microsoft Entra ID user tokens.

2. Automate with XMLA or Tabular Editor:
   Instead of relying on SSMS, consider using Tabular Editor or PowerShell scripts with the XMLA endpoint. You can authenticate via the Service Principal in these tools and automate partition processing without worrying about token expiration.

3. Refresh Tokens in Scripts:
   If you must use Microsoft Entra ID, implement a script (e.g., with PowerShell) that automatically fetches a new token when the current one expires.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS