Access SSAS through http

Problem: In some scenarios, data centers are divided into different server farms: Database farms and Applications

Problem: In some scenarios, data centers are divided into different server farms: Database farms and Applications

Problem:

In some scenarios, data centers are divided into different server farms: Database farms and application farms. There is strict firewall control between server farms, where the database farm can only be built from the application farm firewall, that is, no client is allowed to directly connect to the firewall.

This policy is no problem for the database engine, but it is completely restricted for the Analysis Service. Generally, we directly connect to the Analysis Service database through Excel, the role of the Analysis Service is used to control data access permissions. Is there any way to solve this problem?

Solution:

We know that SSAS can be accessed through http, so as long as we make this site accept windows authentication, we can introduce the implementation of the connection from the excel client to the analytics Service database.

Lab environment:

Windows 8.1 + IIS

SQL Server 2012

Adveuture Works is the official Sample Database of Microsoft. The Analysis Service database is used here.

Tutorial steps:

First, make sure that IIS has been filled in, and then the windows identity mode has been installed.

Create the test account aaa and bbb in the operating system and add them to the two groups in sequence.

Under the Analysis Service database, create two roles: Team1 and Team2.

Locate the Dimension Data settings and specify that the first role has the first two members in the Department dimension, and the other role has the last three members in the dimension.

After filling in and adding, make sure that both groups are added to the analytic Service database.

Next, test the role permission control result. Log On As an administrator and you can see all dimension members under the Department.

Log on to Management Studio with an aaa account (Prompt: press Shift and right-click) to view only the first two members of the Team 1 role.

Open IIS, configure the site, and enable Windows Authentication mode.

View the data to see the returned xml result page.

Use this URL to connect to the Analysis Service and connect to the aaa account. You can see that the account of the current client is identified through http.

The data in the database is subject to the permission control of the analytics Service database.

Next, click "excel.exe" and select "run with the specified account.

The aaa information of the data account, which enables Excel to run in this identity.

Create a pivot table in Excel.

Select connect to the analytic Service database.

Enter a URL under the server name.

Select to connect to the Adventure Works multi-dimensional dataset. In Microsoft's example database, this is the most comprehensive multi-dimensional dataset. Other datasets that can be seen are actually from a perspective of this dataset.

In the Excel Pivot table, drag the Department dimension and you can see that you have accepted the role permission control of the Analysis Service.

Save the Excel file, open the file with the Administrator account, and refresh the pivot table. You can see that all Dimension member data under Department is returned Based on the permission settings of the analytics Service database.

Conclusion:

By connecting to the analytics Service database through http, you can see that the windows Authentication Mode of the site can be controlled by the role permissions of the analytics Service database. In this way, the application server can indirectly access the Analysis Service database on the server farm, and the effect is the same as that of directly connecting to the Analysis Service database.