I was wondering when someone would ask that. First, why what you're doing doesn't work.
The MSOLAP OleDb provider uses a .NET component (ADAL) for Azure Active Directory authentication. Using .NET from a COM component causes the .NET Framework to be loaded. But SQL Server already is a .NET Framework host, and so bootstrapping .NET from COM is not allowed. This is the same as the scenario described here: Using extended stored procedures or SP_OA stored procedures to load CLR in SQL Server is not supported
Luckily, Linked Servers were designed to accommodate drivers that can’t be loaded in-process, and COM allows you to run components in separate processes, if you’re willing to deal with Component Services and DCOM configuration.
The setup starts with provisioning a Service Principal to connect to Power BI (or Azure Analysis Services), per the doc here: For Power BI ( or For AAS ).
Then the Linked Server config looks like this :
--EXEC master.dbo.sp_dropserver @server=N'pbi', @droplogins='droplogins'
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP', N'AllowInProcess', 0
@provstr='Persist Security Info=True; Impersonation Level=Impersonate;',
EXEC master.dbo.sp_serveroption @server=N'pbi', @optname=N'rpc out', @optvalue=N'true'
@rmtsrvname = N'pbi',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'app:523b389b-33...42791a6f@72f9...1ab-2d7cd011db47',
@rmtpassword = N'dpz-en...bMFD'
EXEC ('evaluate SomeTable') at pbi
But to get it to actually work, you have to go through the DCOM configuration steps listed here: Permissions needed to set up linked server with out-of-process provider
The account you should add permissions to is the SQL Server Service Virtual Account, which for a default instance is
NT SERVICE\MSSQLSERVER. Named instances have an account of the form
Also it’s a little unclear, but you can stop when you get to the
“Follow these instructions to be able to edit above DCOM settings on Windows Vista/2008 machine”
So quoting from that blog:
Permissions needed to set up linked server with out-of-process
Verify below settings in DCOMCNFG: Start --> Run –> Dcomcnfg
- Component services -->My Computer ---> Properties verify that below options are set:
In the 'Default Properties' tab:
'Enable Distributed COM on this computer' is checked.
Default Authentication = Connect.
Default Impersonation Level = Identify or Impersonate.
- Component services --> My computer --> DCOM Config --> MSDAINITIALIZE
-Right click on MSDAINITIALIZE --> Properties -->Security
-Add the SQL Server service account (if connected to SQL server using SQL login) or windows user account under "Launch and Activation
Permissions", "Access permissions" and "Configuration Permissions".
-Give full rights to these accounts.
-Restart the server
- Go to dcomcnfg > My computer > Properties > COM Security > Edit
Defaults for Access Permissions & Launch and Activation Permissions,
and add the SQL Proxy account.