Share via

Connecting Azure Analysis service and SSIS using service principal

Dheeraj 6 Reputation points
2020-12-16T08:48:35.223+00:00

Hello Team,

We are trying to connect azure analysis services and SSIS using service principal. But when we are passing this in SSIS as username & password, it is getting failed again and again and throwing the below error :
Test connection failed because of an error in initializing provider. No error message available.

However, when we are giving the individual email id and password, we are able to make the connection successfully.

So is it not possible to connect both of them using service principal?
And, if possible can you please share the resolution for it.

Also, is it mandatory to add/assign service principal to Access Control (IAM) in the analysis services and give it as contributor role.

Thank you.

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
444 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,521 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HarithaMaddi-MSFT 10,136 Reputation points
    2020-12-24T07:11:18.177+00:00

    Hi @Dheeraj ,

    Thanks for your patience. I reached out to Product team and they mentioned that service principal can be used as an authentication method from SSIS as per this document. Couple of things to check for the issue from our team:

    • Please check if latest Microsoft OLE DB Provider for Analysis Services (MSOLAP) is installed.
    • Check the connection outside SSIS and confirm that it works using MSOLAP and service principals(Ex: UDL file)
    • Ensure the TargetServerVersion of the project is set to the latest SQL Server version.

    However, as issue persists at your end, we would suggest to raise a support ticket if you have an active support plan. Otherwise let us know and we will help in enabling one-time free support ticket for you.

    --

    • 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.
  2. Monalv-MSFT 5,896 Reputation points
    2020-12-16T09:58:51.46+00:00

    Hi @Dheeraj ,

    • Please check if your user and password are correct.
    • Please check if your user has been authorized.
    • May I know if you want to connect to SSIS Catalog in Azure?
      If so, please see the following information:
      Find the connection information you need to connect to the SSIS Catalog (SSISDB) hosted on an Azure SQL Database server. You need the following items to connect:
      1. fully qualified server name
      2. database name
      3. login information

    Please refer to Connect to the SSIS Catalog (SSISDB) in Azure.

    Best Regards,
    Mona

    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.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?