On premise gateway standard issue

[montecostacr]

Hello everyone,

I need help with a big issue I’ve been dealing with for days, and I can’t find a solution. I installed the standard gateway to connect my local SQL Server, Power BI, and the Power BI service. In Gateway and Cloud Connection, I go to Action to configure the gateway. However, it asks for a username and password. Honestly, I’m new to this, and I don’t know which username and password it’s referring to.

I’ve tried everything: using basic authentication, Windows authentication, and even Kerberos, but nothing works. Additionally, in the On-premises Data Gateway section, I see the gateway I created, and when I check its status, it says it’s online. The issue is when I go to connections. My SQL Server is listed, along with the connection type, but its status is offline. As I mentioned, I don’t know what the username and password are.

There’s also an option called “Windows without impersonation,” which gives me the possibility of not using a username or password, but when I try to save, it doesn’t save. I’m using SQL Server with Windows authentication, where I see a username but no password. I checked in the Security folder of SQL Server Management Studio, and I can see the On-premises Gateway user listed there.

Please help me figure out how to configure this; this is the only thing left for me to solve. Additionally, the account is a corporate one with Fabric’s free plan.

[Poojara_D12]

Hi @montecostacr 

The issue you’re facing with Power BI Gateway requiring a username and password when connecting to your local SQL Server is likely due to an authentication mismatch. Since you are using Windows Authentication, the gateway expects a domain account (e.g., DOMAIN\username) with access to SQL Server, rather than just a local machine account. If your SQL Server is configured for Windows Authentication only, you must enter a valid domain account that has permissions to access the database. To verify, open SQL Server Management Studio (SSMS) and check your authentication mode using SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'); if it returns 1, SQL Authentication is disabled, and you need a Windows domain account. Additionally, ensure that the On-Premises Data Gateway service is running under an account with SQL Server access, rather than the default Local System account. If SQL Authentication is enabled, you can alternatively create a SQL login and use that instead. In Power BI Service, go to Manage Gateways, edit the SQL Server data source, and enter the correct credentials. If using Windows Authentication, provide the domain username and password; if using SQL Authentication, enter the SQL login credentials. If the connection still fails, check SQL Server permissions, ensure the gateway has db_datareader and db_datawriter roles, and review gateway logs in Event Viewer for errors. Once properly configured, the connection status should change to Online, allowing Power BI Service to access your SQL Server data successfully.

[v-jianpeng-msft]

Thank you for your support Greg_Deckler 

Hi, @montecostacr 

You can refer to my article below, where I described in detail how to connect to a local SQL Server in Power BI Service:

Cannot connect Power BI with SQL server - Microsoft Fabric Community

For the gateway not online, you should make sure that your local data gateway is turned on and not blocked by any of your firewalls/anti-virus software, etc. Then sign in to Power BI Service with the same account that you used to install the on-premises data gateway. Finally, follow my article to configure the gateway connection.

Best Regards

Jianpeng Li

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

[v-jianpeng-msft]

Thank you for your support Greg_Deckler 

Hi, @montecostacr 

You can refer to my article below, where I described in detail how to connect to a local SQL Server in Power BI Service:

Cannot connect Power BI with SQL server - Microsoft Fabric Community

For the gateway not online, you should make sure that your local data gateway is turned on and not blocked by any of your firewalls/anti-virus software, etc. Then sign in to Power BI Service with the same account that you used to install the on-premises data gateway. Finally, follow my article to configure the gateway connection.

Best Regards

Jianpeng Li

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

[Greg_Deckler]

@montecostacr If you are trying to configure the gateway, then you logon using the same credentials that you logon to the Power BI Service with. 

[montecostacr]

I tried. I used basic autentication and use my same email and same password but I got an error.

Unable to update connection credentials. The gateway is either offline or could not be reached. Please ensure your gateway is online and try again. 

Status code:400

[Greg_Deckler]

@montecostacr Can you post some screen shots of where you are in the interface and the errors you are getting? I'm not quite sure where you are exactly. You will first need to ensure that your gateway is actually running. On whatever computer you installed the gateway on, launch the On-Premises Data Gateway app and logon with your Power BI Service credentials.

[montecostacr]

Hello, @Greg_Deckler.

Sure, I can send some secreenshots.

The first photo, is the on-premise data gateway and if you see the status is online. The second one it has been my issue because I can't connect it.

If I use basic authentication I can't connect it because I'm using Window authentication in SQL Server. So, I have the user name (I think is the user name that is in my SQL Server) but not my password and I need a password to configurate it. Therefore, I chose "Windows without impersonation". But, when I saved the connection I got this error.

Unable to update connection credentials.
The gateway is either offline or could not be reached. Please ensure your gateway is online and try again.

Details:

: Gateway on endpoint 'sb://wabi-west-us-e-primary-relay43.servicebus.windows.net/0662aefb-ea9d-4192-a22d-73ceaa2ae223/' is unreachable.

Please have this information handy if you choose to create a support ticket.

Session Id:
RequestId:
Cluster URI: https://api.powerbi.com
Status code: 400
Time: Mon Jan 27 2025 07:17:04 GMT-0600 (Central Standard Time)

Something I did before had tried to connect were: Checked in SQL properties connections that remote server connections was allow, I checked in SQL security my domain to know if I have the permission. I did and I am the db owner so I think it ok. Then I opened the port 1433 and opened in the firewall using a inbound rule. Finally I tried to connect but, I got the previouse error I showed you.

Regards 

[v-jianpeng-msft]

Hi, @montecostacr 

You're selecting Local Gateway Personal Mode. You'll need to select your on-premises data gateway standard mode (the one you're online). After that, follow the previous steps to configure it again.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

Jianpeng Li