Unable to create linked server to Power BI XMLA endpoint in SSMS

Question

Unable to create linked server to Power BI XMLA endpoint in SSMS and getting below error:

The test connection to the linked server failed. Additional information: An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo) 'Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "POWERBIAPI". OLE DB provider "MSOLAP" for linked server "POWERBIAPI" returned message "Errors in the OLE DB provider.

Failed to load managed ADAL component". (Microsoft SQL Server, Error: 7303)

Answer 1

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' 
go 
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP', N'AllowInProcess', 0 
go 

EXEC sp_addlinkedserver 
     @server='pbi', 
     @srvproduct='', 
     @provider='MSOLAP', 
     @provstr='Persist Security Info=True; Impersonation Level=Impersonate;', 
     @datasrc='powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace', 
     @catalog='MyDataset' 

EXEC master.dbo.sp_serveroption @server=N'pbi', @optname=N'rpc out', @optvalue=N'true' 


EXEC master.dbo.sp_addlinkedsrvlogin  
   @rmtsrvname = N'pbi',  
  @locallogin = NULL ,  
   @useself = N'False', 
   @rmtuser = N'app:523b389b-33...42791a6f@72f9...1ab-2d7cd011db47',  
   @rmtpassword = N'dpz-en...bMFD' 
go

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 NT SERVICE\MSSQL$<instanceName>.

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 provider:

Verify below settings in DCOMCNFG: Start --> Run –> Dcomcnfg

1. 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.

2. 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

3. Go to dcomcnfg > My computer > Properties > COM Security > Edit Defaults for Access Permissions & Launch and Activation Permissions, and add the SQL Proxy account.