Creating Linked Server to connect to Azure Analysis Services

SQLDev2021 1

I have connection info to AZURE Analysis services. I can connect to this server & cube in EXCEL pivot , using my AD credentials (no problem there).

However I try to create an Linked server to try to connect to the same server from SSMS , It doesn't work.

I get this error - "Integrated Security must not be set or set to ClaimsToken or SSPI in order to access this server"

I cannot get my head around this , I tried many combinations (mentioned here ) .

Please help if you have encountered this issue , Thanks.

5 answers

PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-17T09:12:31.487+00:00
Hello @SQLDev2021,

Welcome to the Microsoft Q&A platform.

Additional to @AmeliaGu-MSFT response.

SQL Server can connect to an Azure Analysis Services resource as a Linked server by specifying MSOLAP as the data source provider. Before configuring a linked server connection, be sure to install the latest MSOLAP client library (provider).

To create a linked server to another instance of SQL Server Using SQL Server Management Studio:

Step1: In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.

Step2: On the General page, in the Linked server box, type the name of the instance of SQL Server that you area linking to.

Other data source:

Provider: Microsoft OLE DB Provider for Analysis Services 14.0 Product Name: MSOLAP Datasource: Analysis services - Server name Provider string: Integrated Security= SSPI

Hope this helps. Do let us know if you any further queries.

------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Nandan Hegde 32,421 Reputation points MVP

2021-02-17T04:54:05.6+00:00

Hey @SQLDev2021,
Can you please clarify your ask ? Do you want to browse the tabular model from SSMS and do analysis?
If yes, then you need to use Analysis server type :

No need to create a linked server specifically for that .
Please sign in to rate this answer.
SQLDev2021 1 Reputation point

2021-02-17T04:56:42.68+00:00

I want to create a Linked server in my local SQL server (SSMS) , and query from Azure SSAS Cube.

Yes I can connect to this AZURE SSAS from SSMS as you show... But cannot connect using a Linked Server.

Nandan Hegde 32,421 Reputation points MVP

2021-02-17T05:34:55.183+00:00

Hey @SQLDev2021 ,
Can you please provide the screenshot of the linked server properties which you are trying to configure?

SQLDev2021 1 Reputation point

2021-02-17T06:54:01.177+00:00

Screen shot of Linked Server configuration.

Nandan Hegde 32,421 Reputation points MVP

2021-02-17T08:50:23.727+00:00

And @SQLDev2021 ,
Can you provide the screenshot of the sceurity tab? what configuration are you using to connect to AAS?
Because the error seems more the security side as compared to the config one(which I see is correct)

PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-17T09:13:49.847+00:00

Hello @SQLDev2021 ,

Please checkout the answer provided below.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor

2021-02-17T07:42:04.93+00:00

Hi SQLDev2021-5312,

Could you please try to specify “MSOLAP” in Product name and add connection string with “Integrated Security= SSPI” in Provider String in the linked server?
Please refer to Connection string properties which might help.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
SQLDev2021 1 Reputation point

2021-02-17T10:10:18.743+00:00

I tried the above step , it gave me an error when I used the Linked server to Query the Cube (see below) -

I am able to connect to the same cube, and create Pivot in Excel (same credentials). I have tried various other combinations in "provider string" , and under "security" Impersonation - they all failed with same kind of errors.

I work in Microsoft Corp. I haven't created a Linked server for Azure SSAS before.

Thanks
Please sign in to rate this answer.
PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-18T09:33:25.713+00:00

Hello @SQLDev2021,

Could you try these options and do let us know:

Start SQL Server Management Studio as Administrator

Check the Allow Inprocess box for the MSOLAP provider.

Could you please confirm, "If Allow inprocess is selected and the provider is instantiated in the SQL Server process"?

Double click MSOLAP -> Check the “Allow Inprocess” check box,

Hope this helps. Do let us know if you any further queries.

SQLDev2021 1 Reputation point

2021-02-18T17:24:56.47+00:00

“Allow Inprocess” in MSOLAP - is not checked (This is recommended by Microsoft)

PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-19T04:47:50.273+00:00

Hello @SQLDev2021,

Have you tried the below options and do let us know the status:

Start SQL Server Management Studio as Administrator

Check the Allow Inprocess box for the MSOLAP provider.

SQLDev2021 1 Reputation point

2021-02-19T06:25:16.113+00:00

Thanks for the reply.

I tried the steps (enabled "Allow Inprocess") , and now I get this error -

"Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "AZURE_10" "

When I try some option in "Security" tab , I get this error -

" **Object reference not set to an instance of an object ** "

PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-19T08:53:15.287+00:00

Hello @SQLDev2021,

Thanks for the details. Could you try the below options and do let us know the status.

While configuring the Linked server, please do specify the Security as "Be made using this Security Context."

If you need MFA, then just give the UPN in the username tab and leave the password as blank........... If you need fixed address, then you give have to give SPN credentials to avoid MFA.

SQLDev2021 1 Reputation point

2021-02-19T10:01:31.013+00:00

Do u work at Microsoft ?

I have already tried this , it gives error "Cannot initialize data source object of OLEDB provider"

I don't think Kerberos/SPN is set up on my SQL instance. NTLM is what is being used.

PRADEEPCHEEKATLA-MSFT 89,571 Reputation points Microsoft Employee

2021-02-22T05:47:42.943+00:00

Hello @SQLDev2021,

Yes, I do work at Microsoft.

For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket. In case if you don't have a support plan, I will enable a one-time free support request for your subscription. Please do let us know.

SQLDev2021 1 Reputation point

2021-02-22T10:06:31.083+00:00

I work at Microsoft too, on a "v- "

can I ping you on Teams ?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Neeraj Dubey 1 Reputation point

2021-11-12T21:05:21.17+00:00

Similar to this , Creating linked server in Azure sql managed instance with AAS will work ?
Please sign in to rate this answer.
Richard Han 1 Reputation point

2022-06-10T17:02:42.45+00:00

No work for AAS, Azure SQL MI only support limited target, please see
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server?view=azuresql#linked-servers
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.