If you have an excel created by LTG Business Analyst team and you are having trouble to refresh data.
Please follow this steps to ensure you have a proper setup.
First have Azure VPN connected. This is important to properly connect and authenticate to SQL server
We need to get to Data source settings. First click on Query tab, and click on Reference.
This will open a new window, where you will find Data source settings
Click on Global permissions, select SQL server (in this case it is our reporting SQL with IP address: 10.4.10.19) and click edit
Credential: Windows - this will grab your credentials from your user profile.
Encryption: un-tick Encryption.
Privacy Level: none
Now select the top level of our SharePoint and click edit
Credentials: Windows will work if you are connected to company's network. Howver If you are outside the company we must change authentication type.
Privacy level: none
Click edit to change credentials/authentication type.
Select Organizational account and click Sign in. You will get a prompt for modern authentication.
you can click on your recognised account, however it might ask you for password and trigger MFA.
Once done, you will be able to connect to other excel files to refresh data.
you can close Query editor, and refresh the connections