Creating Linked Server to connect to Azure Analysis Services

SQLDev2021 1 Reputation point
2021-02-17T03:59:58.973+00:00

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.

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
438 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 77,751 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

    68946-image.png

    69047-image.png

    68989-aas-linkedserver.gif

    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.
    1 person found this answer helpful.
    0 comments
  2. Nandan Hegde 29,891 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 :

    68963-aas.png

    No need to create a linked server specifically for that .

  3. AmeliaGu-MSFT 13,961 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.

    0 comments
  4. 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

    69082-screen-2.png

    69066-screen-3.png

  5. 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 ?