SSRS Connection to - Azure Analysis Services Tabular Model

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

Answer 1

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

Answer 2

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,

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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.

---