Recently I was working on a SQL Server Analysis Services Tabular project in Visual Studio 2019. In attempting to connect to a SQL Server database to import data, I got the following error.
An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=22.214.171.124, 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)'
Let’s see the steps I went through to get to this point…
Reproducing the Error
Start by opening your SSAS Tabular project in Visual Studio 2019. In the Tabular Model Explorer, right click on Data Sources, then pick New Data Source.
In the Get Data window, pick Database, then “SQL Server database” and click Connect.
In the “SQL Server database” window, enter the name of the server, for example “localhost”. Click OK.
In the credential window, with the default of Windows credential, use Impersonate Account for the Impersonation Mode.
Enter your credentials and click OK.
You get a dialog titled “Unable to connect“.
You get this, despite knowing you’ve entered your credentials correctly. I actually found the solution in a PowerBI issue on Stack Overflow, they were having a similar problem.
The solution, as it turned out, worked for both PowerBI and Visual Studio 2019. Simply run Visual Studio 2019 in administrator mode.
In the pic above, I have VS2019 in my toolbar. I right clicked on the icon, then in the menu right clicked on Visual Studio 2019. I then picked the Run as administrator option.
Following the steps in the Reproducing… section above I entered my credentials and clicked OK.
After clicking on OK, instead of the error I got an Encryption Support error, that it was unable to connect using an encrypted connection. I believe that was because, in my case, Visual Studio and SQL Server are both on the same box, in a development VM. As such, I’d not bothered with the overhead of setting up encrypted connection support in SQL Server. In this case I was OK with that so just clicked OK.
Now the Navigator window appeared, and I was able pick a database to import from.
I hope this simple fix works for you. I know I spent forever looking for an answer, and was lucky that trying the same solution that worked for PowerBI, running in admin mode, also worked for Visual Studio 2019.