I have an Azure Analysis Services model reading data out of SQL Azure with this connection string:
Data Source=MySQLAzureDB.database.windows.net;
Initial Catalog=MyDB;Persist Security Info=true;
User ID=MyUser;Password=MyPWD;
Encrypt=True;Authentication=Sql Password
This uses a SQL User to connect to SQL Azure. This means I need to define user/password in two seperate places: in SQL Azure as well as in this connection string.
I would like to use a service principal instead rather than a SQL user. Has anyone done this before?
I've tried using different connection types but this is the typical error from AAS when I use Authentication=ActiveDirectoryPassword in my connection string:
The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'Unable to load adalsql.dll >> (Authentication=ActiveDirectoryPassword)
So before I undertake any further investigation, has any one done this before? The error message appears to indicate that this critical library has not been installed.
I have some prior experience using service principals. I have to connected to AAS and processed a database with Azure Automation using the service principal. Some background on this here:
Use Automation RunAs service principal to connect to Azure Analysis Services and process
I think it is unsupported for AAS. We have plans to extend SQL driver allowing to support interactive mode where password is requested via an interactive dialog. Also you may try MSI to Azure SQL (from Windows VM): https://learn.microsoft.com/azure/active-directory/msi-tutorial-windows-vm-access-sql Although it's not quite what you are looking for.
Mirek Sztajno, senior PM SQL Data Platforms