3

I am using Excel 2013 PowerQuery addin to connect to SQL Server database. I have Excel open with my user account (domain/Sherlock); which does not have read access to the database (myServer/myDB). I have the credentials for another Windows user account (domain/Sherlock_2), which does have read access.

Objective:

I would like to open excel as normal user (domain/Sherlock - db read access not granted) and have excel PQ connect to db as another user (domain/Sherlock_2 - db read access privilege).

Does anyone know how to authenticate as windows alternative user account?

Steps I have tried (Windows Alternative User)

1. In Excel change the PQ DB authentication 

PQ ribbon> Data source settings> '@myDB'> Edit Permissions>
Credentials: 'Windows' 
User alternate credentials: '@domain/@Sherlock_2'

2. In Excel try connecting to DB 

PQ ribbon> from database> SQL Server> 
Server:'@myServer', 
Database: '@myDB'> 
Advanced options> SQL statement: 'select * from table'> OK

Error message:

Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'"

3. Try again

I tried clearing the permissions in 'Data source settings' and start over from scratch. Still the same message: HRESULT: 0x80070542.

More steps I have tried (As Admin)

Based on this PowerBI Community post, the DB connection will work with the standard account after connecting initially for the first time "As Admin".

1. Open Excel (Excel As Admin)

I ran Excel 'As Admin' with '@domain/@Sherlock_2' and created the same data source connection with User alternate credentials '@domain/@Sherlock_2' ... PQ connection successfully made to DB.

2. Open Excel (Normal)

And then I tried running Excel 'Normal' with '@domain/@Sherlock'... PQ connection error message HRESULT: 0x80070542.

NOTE: I tried also runnning Excel 'As Admin' with '@domain/@Sherlock', I get the message "The requested operation requires elevation".

Authentication setup screenshot:

enter image description here

Connection and error screenshot:

enter image description here

SherlockSpreadsheets
  • 2,062
  • 3
  • 27
  • 47
  • First, fix your error "An error happened while...". See this post : https://community.powerbi.com/t5/Issues/SQL-connect-error/idi-p/219982 – DanB Oct 19 '18 at 17:22
  • @DanielBlais That post has two suggestions... 1) **'reinstall your .NET Framework'** which some responded did not work, and 2) **'As Admin'** which I have already tried documented the results in my post. – SherlockSpreadsheets Oct 19 '18 at 17:28
  • It seem there are many source can lead to this error, so there is many solution. I suggest you to try them. You can also see this post : https://social.msdn.microsoft.com/Forums/vstudio/en-US/e6cd11ce-210e-4ed1-ae4c-5e251246797a/systemiofilenotfoundexception-could-not-load-file-or-assembly-systementerpriseservices?forum=netfxsetup – DanB Oct 19 '18 at 17:32

1 Answers1

-1

I tried to reinstall NET, register and unregoster DLL, etc, etc.

Opening Excel with admin can open a query, but not with my usual user.

Finally, ther solution was:

Create a query that works opening excel with admin user, and save the excel file

Open excel with regular user, and open file saved in previous step

Go to Edit query (Power Query Editor) -> Configure data source button -> Edit permissions ->Uncheck cipher connection

Solved

Any new query you create will work as usual

Jordi Rovira
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 22 '23 at 05:00