none
SSRS Connection to - Azure Analysis Services Tabular Model RRS feed

  • Question

  • Hi,

    We have following scenario:

    On - Prem: 

    Server 1 - DB Server - SSRS ReportServer, ReportServerTempDB is placed here

    Server 2 - SSRS Report Server - Report engine is installed here

    Server 3 - SSAS Tabular Server - Installed here

    Clients are able to access the reports pointing to Server 3. (We have added SPNs correct for on prem domain account)

    Now, we have moved Server 3 to Azure Analysis Service

    Server 1, Server 2 - are still same

    Server 3- Azure Analysis Service

    We have registered an App - Provided Read, Write access to Azure Analysis Service and created a key, Added app:abcd@asdf as Analyis Server Admin.

    Using SQL Server Management Studio: 

    If we use Active Directory password - app:abcd@asdf and key as password we can connect.

    But when we use the same credentials on Report Server Data Source it fails -

    ERROR: Report server unique dump occured. Exception: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.ArgumentException: Authentication failed: User ID and Password are required when user interface is not available., Message: , Unhandled Exception: False

    Has anybody tried connecting SSRS to Azure AS?

    Thanks!


    VJ

    Friday, October 19, 2018 8:25 AM

Answers

  • I was able to work around the problem finally if not fully but for now partially.

    1. Downloaded latest drivers for ADO from microsoft website and installed on reporting server and reportserver db server

    https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers

    2. When connecting from data source in ssrs I used OLEDB connection

    3. Connection string - it is same as oledb connection connectionstring

    4. Use Windows Authentication in Data source and used my Domain Account that is admin of azure as server

    5. OLEDB connection does not support parameters in data set. So changed the ssrs dataset query to expression query so i generate dax query dynamically in expression and hence do not need any parameter.

    6. Once done now i can connect ssrs report to azure as server.

    However, i am not able to fix the impersonitation yet so need to check this how to fix this problem.


    VJ




    Thursday, November 8, 2018 8:16 AM

All replies

  • Hi,

    Can you share the connection string?

    I found related questions in MSDN with a workaround.  Have you tried what is in the threads below?

    https://social.msdn.microsoft.com/Forums/azure/en-US/be5e95d0-ac13-4c9d-ae23-170a05f09ad6/ssrs-connect-to-azure-analysis-services

    https://social.msdn.microsoft.com/Forums/azure/en-US/8794da73-2c52-4d54-89d0-e104d9cabbc0/azure-as-report-with-ssrs

    Thanks.

    James

    Tuesday, October 23, 2018 11:13 PM
    Moderator
  • Hi Vishal,

    As you said Server1 & 2 are still on prem and analysis service is moved to azure. It is hosted on VM or Paas mode (if it is analysis tabular), what network channel you are using (using public network  or Express Route) while connecting to azure from on prem.

    Few suggestions, you can check the ports (i.e. 80 & 443) and if it is VM then you have to whitelist the IP's/Range those making the data requests.


    Cheers,

    Wednesday, October 24, 2018 1:52 AM
  • Yes, i had tried looking at these earlier. The suggestions did not work.

    Connection Strings that i tried:

    1. Provider=MSOLAP;Data Source=asazure://northeurope.asazure.windows.net/azureserrvername;Initial Catalog=TabularDatabaseName;

    2. Also passed userid and password of domain account in the connection string. Domain account is the administrator of Azure Analysis Server and also admin on all servers.

    3. Also passed userid and password of app registered in connection string. App SPN is the administrator of Azure Analysis Server.

    None worked here.


    VJ

    Wednesday, October 24, 2018 11:51 AM
  • Thanks for your response!

    It is hosted on VM or Paas mode (if it is analysis tabular) - Paas

    what network channel you are using (using public network  or Express Route) while connecting to azure from on prem - I am not sure!

    80 & 443: Requested open Port 80 and port 443 on server 1 and server 2. Will get back to you if this solves the problem.


    VJ


    Wednesday, October 24, 2018 1:09 PM
  • I was able to work around the problem finally if not fully but for now partially.

    1. Downloaded latest drivers for ADO from microsoft website and installed on reporting server and reportserver db server

    https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers

    2. When connecting from data source in ssrs I used OLEDB connection

    3. Connection string - it is same as oledb connection connectionstring

    4. Use Windows Authentication in Data source and used my Domain Account that is admin of azure as server

    5. OLEDB connection does not support parameters in data set. So changed the ssrs dataset query to expression query so i generate dax query dynamically in expression and hence do not need any parameter.

    6. Once done now i can connect ssrs report to azure as server.

    However, i am not able to fix the impersonitation yet so need to check this how to fix this problem.


    VJ




    Thursday, November 8, 2018 8:16 AM
  • I was able to work around the problem finally if not fully but for now partially.

    1. Downloaded latest drivers for ADO from microsoft website and installed on reporting server and reportserver db server

    https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers

    2. When connecting from data source in ssrs I used OLEDB connection

    3. Connection string - it is same as oledb connection connection string

    4. Use Windows Authentication in Data source and used my Domain Account that is admin of azure as server

    5. OLEDB connection does not support parameters in data set. So changed the ssrs dataset query to expression query so i generate dax query dynamically in expression and hence do not need any parameter.

    6. Once done now i can connect ssrs report to azure as server.

    However, i am not able to fix the impersonitation yet so need to check this how to fix this problem.



    Thursday, November 8, 2018 8:18 AM