Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
harryws
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
v-yilong-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-yilong-msft
Community Support
Community Support

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
Impactful Individual
Impactful Individual

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

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...

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.