As an external employee or consultant, you sometimes have to connect to on-premises data from a remote location. We recently ran into troubles while connecting to a MS SQL Server from a machine which was not in the same domain as the SQL Server. The user credentials and the VPN connection was in place but using Windows authentication, the following error occurred:
Activating database credentials on the SQL Server for this account was not an option so we had to find a different solution.
The Solution
1. Add hosts file entry
If you want to use the resolved name of your SQL Server instead of the IP address, make sure you have added the respective entry in your hosts file located at: “C:\Windows\System32\drivers\etc\hosts”
The entry should contain the private IP address of the SQL Server and the corresponding name. In our case, the entry was added like this:
2. Install Power BI Desktop
This step requires the Power BI Desktop installation directly from the Microsoft Download Center. Download Power BI Desktop
After installing Power BI Desktop, locate the PBIDesktop.exe file. Without changing the default installation path, it is located at:
“C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe”
Copy the path and write it down for later.
The Power BI Desktop installation from the Microsoft Store does not work in this case.
3. Connect your VPN Software
Make sure you are connected with your VPN software to the network from which the SQL Server is accessible.
4. Run Power BI as a different user
We now want to run Power BI Desktop with the credentials for the Windows authentication on the SQL Server. This is possible using the runas command-line tool with the parameter “/netonly” which indicates that the user information specified is for remote access only. Open a new command prompt window and type in:
runas /netonly /user:[domain]\[user] [path\to\PBIDesktop.exe]Where
[domain] is the domain in which the user to authenticate with resides,
[user] is the username which you want to use for authentication and
[path\to\PBIDesktop.exe] is the copied path from step 2.
In our case the command looks like this:
You will be prompted for the password. You can copy paste or just type it in. CMD will not show what you have typed in so just press enter after providing your password. After this step, Power BI Desktop will start up.
5. Connect to SQL Server
In Power BI Desktop, click on “Get Data” and select “SQL Server”. Put in the IP address or the name of the server from step 1.
For the authentication choose Windows and select “Use my current credentials”
If everything went right, you are now connected to the SQL Server
There are of course many other reasons why the connection is not possible, regardless of the authentication method. MS SQL works with TCP port 1433 so make sure it is not blocked.