Problema gateway power services

[Syndicate_Admin]

Hello everyone

I need help with a big problem I'm having and I've been thinking about it for days and I can't find a solution. I've installed the standard gateway so I can connect my on-premises SQL Server, power bi, and the power bi service. In gateway and cloud connection I'm going to action to configure the gateway. However, it asks me for a username and password. Honestly I am new to this and I do not know what username and password he is referring to. I've tried everything. I've used basic authentication, window authentication and even kerber and nothing works. Additionally, in the on-premise data gateway option I see the one you think and if I give it the status it says it is online. The problem is when I go to connections. You see my SQL server, my connection type but it's offline when I give it status. And as I said, I don't know what the username and password are. Also when you want to get one called "window without impersonation", in it gives me the possibility of not using either username or password but when I give it save, it does not save. I'm using SQL server with window authentication, which I see a user but there is no password. I looked at the SQL management security folder and if I see the on-premise gateway user. Please help me how to set up, I'm just missing this. In addition, the account is business with Fabric's free plan.

[Anonymous]

Hi,rohit1991 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@Syndicate_Admin .I am glad to help you.
The username and password used when configuring the gateway should be Windows credentials that enable access to SQL Server. Typically, these credentials are in the format Domain\Username. if you don't have a password, try creating a new user with the appropriate permissions and setting a password for it.
Adding Domain Users to the Local Administrators Group in Windows | Windows OS

If you can't use the windows (domain user authentication) method, you may consider using basic to authenticate the data source credentials.
basic requires you to fill in the username and password of the SQL account created in your SQL Server database.
All database instances will have an SA account by default after creation, you can manually change the password and give it access rights to the corresponding database.

Make sure that the gateway and SQL Server are in the same domain and that the gateway computer is authorized to access SQL Server If you still cannot save the settings, you may need to check the network connection between the gateway and SQL Server.
Update the gateway version: Make sure you are using the latest version of the Local Data Gateway.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Additional. I was able to check the domain in the cmd with this code: systeminfo | findstr /B /C:"Domain", tells me that it's in a workgroup and not in a domain. When I try to make the change, this part is disabled to add a domain.

[Syndicate_Admin]

Hello

What a shame with my insistence but I can't solve the problem. Honestly, you mention certain things that I don't understand very well since I'm new to permissions and gateways and I barely understand. What I can tell you is that I performed the steps mentioned above. As for creating a new account, SQL does not allow me, it generates an error. I don't know if the error is because I have to add domain user to the group. But if that were the case, it would be to create a new user as an administrator in lusrmgr.msc?. Additionally, the sa comes out with an x in that user and it doesn't let me change password. When you mention "Make sure that the gateway and SQL Server are in the same domain and that the gateway computer is authorized to access SQL Server," what do you mean?

I tell you that the gateway does work perfectly. What I can't do is connect to SQL.

I attach the photo where on-premise data gateway works.

And a second photo where it generates the error for not being able to perform the authentication.

[rohit1991]

Hi @Syndicate_Admin 

Here’s what’s happening and how you can solve it:

1. What credentials do you need? If your SQL Server is part of a domain, use the format: DOMAIN\Username (plus the Windows password for that user). If you’re not on a domain (just a workgroup), or domain login isn’t possible, you’ll need a SQL Server login (something like “sa” or another SQL user with a password).

2. Enabling SQL Server Authentication (if needed): Open SQL Server Management Studio (SSMS). Right-click the server (top node) > Properties > Security. Make sure “SQL Server and Windows Authentication mode” is checked. Restart the SQL Server service if you had to change this

3. Create a SQL Login (if you don’t have one): In SSMS, expand Security > Logins. Right-click > New Login. Choose a username (like “powerbi_user”), pick “SQL Server authentication,” set a password, and map it to your database with at least “db_datareader” permissions

4. Set up the gateway connection in Power BI: Go to Power BI Service > Manage Gateways. For authentication, use the SQL login and password you created (not your Windows credentials).  If you can’t create a SQL login: You’ll need someone with SQL admin rights to do it for you and give you the details.

The gateway machine can reach the SQL Server (network/firewall) The account isn’t locked or missing permissions. You’re using the right authentication mode for your setup.

This stuff can be confusing if you’re not used to Windows domains and SQL permissions, but once you have the right login, it just works.

[Syndicate_Admin]

The process you describe has allowed me to get to the save option when I use windows without impersonation. However. It generates the following 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 Time: Sun Jan 26 2025 13:58:16 GMT-0600 (Central Standard Time)

The use of this authentication is correct for using the local SQL Server authenticated with window. Because I tell you that I don't have a password, only the username that would be of the form Domain\Name.

By the way, thank you very much for all the help and information.