question

chamilakariyawasam-4227 avatar image
0 Votes"
chamilakariyawasam-4227 asked CarrinWu-MSFT commented

Access SQL server analysis 2019 via Excel

Hi,

I am trying to access Access SQL server analysis 2019 via Excel > Data > From Other Sources > From Analysis Services.
but once enter the SQL server IP address and click on next the Excel gets frozen and nothing happens.
SQL version is 2019 and installed on a Windows Server 2019.

If I add the particular user to Windows 2019 server's, local admin group, it works without any issue, but due to security reasons, we do not want to give admin rights to normal users.
How to achieve this with minimum rights?

Regards,
Chamila.

sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @chamilakariyawasam-4227, we have not get a reply from you. Could below answers help you? If yes, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @chamilakariyawasam-4227,

In order for a specific role to have permissions over a cube data you have to specifically grant cube permissions to that role. That can be done using the Cube tab in the role properties page: in SSAS

-> Databases -> Your db -> Roles -> create new role ->read definition->add the users to membership

144931-untitled1.png


You may take a reference of below links.

grant-read-definition-permissions-on-object-metadata-analysis-services

security-roles-analysis-services-multidimensional-data

configuring-permissions-for-sql-server-analysis-services

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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 October



untitled1.png (36.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

chamilakariyawasam-4227 avatar image
0 Votes"
chamilakariyawasam-4227 answered

Hi Zoe,

SQL permissions have been granted as you mentioned.
If I add the particular user to Windows 2019 server's, local admin group, it works without any issue if not excel get frozen and after about 20-30 minutes it opens.

Regards,
Chamila.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @chamilakariyawasam-4227,

Thank you for your replying.

SQL permissions have been granted as you mentioned.

This permissions should be granted in SSAS as following:

1.created an test user in Computer Management and then added to Groups:
145403-1.png
145335-2.png

2.added a New Role into your SSAS and granted permissions to the user:
145383-3.png
145384-4.png
145385-5.png
145386-6.png

3.connect to SSAS from excel successfully.

it works without any issue if not excel get frozen and after about 20-30 minutes it opens.

If you could connect to the cube, it means there is no error for your connection. But the connection takes a long time, I suspect that your cube maybe have some script need to calculate so that you will take a long time to connect if you connect to SSAS from user side. And you could try to use SQL Server Profiler to get some details. For more information, please refer to this thread.


Best regards,
Carrin

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.



1.png (32.2 KiB)
2.png (88.6 KiB)
3.png (97.2 KiB)
4.png (96.2 KiB)
5.png (26.7 KiB)
6.png (27.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.