Connecting Azure Analysis service and SSIS using service principal

Question

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.

Answer 1

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.

Answer 2

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?