MS Query from SQL to Excel Security

(0) Share

Report

Posted on by Community Member

Hello,

I recently ran a script to create a view, gave access to this view. Linked the MS query to this view. The report runs fine for SA but not for other users. What security does the users need to be able to access this excel report pulling data from sql?

Thanks for you help!

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

Hello again ShellyAnn

Are you talking about a normal Excel file being linked to SQL view ? Which prompts asking for authentication as shown below:

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Yes exactly!

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (1)

Report
Copy link

Link copied!

In this case, you need to use an SQL user with proper access on the view and its associated tables. Dynamics GP user will not work of course since it is a different thing

You can consider saving the "sa" password on the Excel file for the users so that opening the file will not ask for the user name and password anymore, follow the steps below to save the password:

1- On the Excel file, open the "Data" tab, click on connections

2- One the connection string window is opening, click on the specific connection string and then "Properties"

3- On the properties tab, open the "definition" tab and check the "Save Password" option

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

Another option is to create an SQL user on the database level, which has appropriate access for the specific tables and view you created. Of course, this is definitely a better option to ensure that your "sa" password is not used and saved on separate Excel sheets across different computers.

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report
Copy link

Link copied!

Why don't you try to connect the SQL server database with using window authentication?

Better you can provide SQL access to your windows users to SQL server database and the connect the database from EXCEL with using windows authentication mode instead of SQL server authentication.

Hope this helps!!!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

If you can implement Windows Authentication to your SQL Server, you can try using stored procedure to implement the same view, and grant execute for your other windows credential to the stored procedure.

Just make sure you create the stored procedure using your dbowner credential, then ownership chaining will take care of the permission

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

This works perfectly! Thank you! =)

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

You are most welcome Shelly

Was this reply helpful? Yes No