CODEWITHSUNDEEP
sql-serverssisssmsssaslinked-server
Vlogs Bengali
Vlogs Bengali

Reputation: 95

Error connecting Analysis Services Cube using Linked Server

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

Upvotes: 0

Views: 2046

Answers (2)

Mohammed Sharief
Mohammed Sharief

Reputation: 1

this happens due to encrypt method being changed, remove service account creds from linked server and then reapply same credentials.

Give required permissions to SPs if exits and then try again.

Upvotes: 0

sqlpill
sqlpill

Reputation: 11

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/

Upvotes: 1

Related Questions