Error connecting Analysis Services Cube using Linked Server

0
votes

I am trying to create a Linked Server from SSMS.The linked server should connect to a Analysis database cube.I am using this connection below. enter image description here

and security

enter image description here

But I am getting the below error each time I test connection

enter image description here

Can you let me know what could be the reason behind this

TITLE: Microsoft SQL Server Management Studio

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 "XXXX". OLE DB provider "MSOLAP" for linked server "XXX" returned message "The physical TCP/IP connection failed: An existing connection was forcibly closed by the remote host. ". OLE DB provider "MSOLAP" for linked server "XXXX" returned message "The peer prematurely closed the connection.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3356&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

Please let me know what could be reason behind not able to connect to analysis service ,whereas with the same settings I am able to connect from a different box

1
sql-serverssisssmsssaslinked-server

1 Answers

1
votes

You probably have problems with differences in kerberos encryption protocol for accounts running SQL and SSAS. Check that by following command:

Get-ADuser SERVICEACCOUNT -Properties * |select KerberosEncryptionType

If you got empty for one service account and AES128/AES256 for another this means that ticket can not be decrypted because of difference in protocols.

In that case you should set kerberos encryption protocol (aka KerberosEncryptionType; aka msds-supportedencryptiontypes) for the account which does not have it specified:

Set-ADUser -Identity SERVICEACCOUNT -KerberosEncryptionType AES128,AES256

Why this happens and how to check other reasons you may read in my article - https://www.sqlpill.com/tips/kerberos-encryption-protocol/