SQL Server on Azure VM not connecting via on-premises gateway

[harryws]

Hi all,

We've recently migrated our physical server onto an Azure VM which now hosts our server. I have managed to recreate my report in Power BI desktop by connecting to SQL Server.

We have then went and created an on-premises gateway on our VM, which is constantly running to allow for refreshes.

We are now having a problem connecting the SQL server to the gateway on the Power BI Service. The error messages usually arise when trying to authenticate or login.

When connecting to the SQL Server in desktop I connect using the IP address and database, and then authenticate using "Database" credentials. However, on the service there are no options for Database (only Basic, Windows, and oAuth2). My question is therefore this: when connecting the SQL server to the gateway should I use the IP address or ___.database.windows.net format, and what credentials should I use to authenticate?

Best wishes

Harry

[v-yilong-msft · Accepted solution]

Hi @harryws ,

The IP address of the VM should be used when connecting to SQL Server on an Azure VM, the ___.database.windows.net format is usually used for Azure SQL Database, it's a different service, here SQL Server is on an Azure VM, so I think you should go ahead and use the IP address of the VM.
The second is that you mentioned that you use the Database credentials in Power BI Desktop, so you should choose Basic as the authentication method in the Power BI service.

Finally, I think you can check out this official document for a more in-depth study: Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticPulse]

use ___.database.windows.net format  
go twith the basic authentication method in powerbi enter credentials realted to sql server account (same creds that you used for connecting in powerbi desktop),.

If this helped, Follow this blog for more insightful information about data analytics

https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/
Please Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
Please subscribe CogniJourney On Youtube For Daily fun facts:
https://www.youtube.com/@CogniJourney

[harryws]

Okay, thanks for clearing that part up. I'm guessing that on Power BI Desktop when importing data I should also log in to the SQL database using .database.windows.net format.

I gave this a go but it keeps saying invalid credentials. I can only import the data successfully when using the IP address as the server name...

[AnalyticPulse]

But ip address is dynamic in most of the cases.  Server name and username is different for sql, its not same as windows account