Creating semantic model sql connection error

[Renata_Franczak]

Hi all,

I am new here and new to Power BI and I'm having some errors. Hopefully someone will be able to help. I have uplaoded Power BI dashboard and created gateway on-premises. Now, I want the dashboard, which pulls out the data from sql objects, to refresh automatically. I wanted to create an sql connection but I'm having this error message:

Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.

The creadentials are correct. And I tried different options of authentication, nothing seems to be working.

I appreciate any help!

Regards 

Renata

[Renata_Franczak]

Hi johnbasha33,

I had to park this task for some time but I came back to it. Thank you for your response. I've checked everything based on your suggestions and all is correct and enabled. But I stil can't connect to SQL server. This is the error message I have: 

Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.



Do you have any more suggestions, please?

[Renata_Franczak · Accepted solution]

Hi johnbasha33,

Just wanted to add that this has been resolved. The server needed to be added to the SQLTrustedServers in the config file.

Regards

Renata

[johnbasha33]

 It sounds like your Power BI gateway is having trouble authenticating with SQL Server. Here are a few things you can check to resolve the issue:

1. Verify Authentication Mode in SQL Server

Since you're connecting to an on-premises SQL Server, make sure SQL Server authentication is enabled:

• Open SQL Server Management Studio (SSMS)

• Run the following query to check the authentication mode:

  SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS AuthMode;

  • If it returns 1, it means SQL Server is only allowing Windows Authentication.
  • If it returns 0, both Windows Authentication and SQL Authentication are enabled.

  • If needed, enable SQL Server Authentication:

    • In SSMS, right-click on the server → Properties → Security
    • Select SQL Server and Windows Authentication mode
    • Restart the SQL Server service.

      2. Check the SQL Server Login

      • If using Windows Authentication, ensure the account running the gateway service has access to SQL Server.
      • If using SQL Server Authentication, verify the username and password exist:

        SELECT name FROM sys.sql_logins WHERE name = 'your_username';

        • If the login doesn’t exist, create one:

          CREATE LOGIN your_username WITH PASSWORD = 'your_password';

        • Then grant access to the database:

          USE your_database;
          CREATE USER your_username FOR LOGIN your_username;
          ALTER ROLE db_datareader ADD MEMBER your_username;
          ALTER ROLE db_datawriter ADD MEMBER your_username;

          3. Ensure the Gateway Can Access SQL Server

          • Go to On-premises Data Gateway → Service Settings
          • Check that the gateway service account (NT SERVICE\PBIEgwService) has permissions to access SQL Server.
          • Try running telnet your_sql_server 1433 from the machine where the gateway is installed to see if it's reachable.

            4. Use the Correct Data Source Settings in Power BI Service

            • In Power BI Manage Gateways, ensure the data source credentials match those in SSMS.
            • If using Windows Authentication, try entering the credentials as DOMAIN\username.

              5. Check Firewall & Network Issues

              • If the SQL Server is on a different machine, ensure port 1433 is open.
              • If your SQL Server is using a named instance, make sure the SQL Browser service is running to resolve dynamic ports.

                6. Restart Gateway and Test Again

                • Restart the Power BI Gateway Service
                • Restart the SQL Server service

                  Let me know what errors you see after trying these steps! 🚀
                  
                  Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

@Renata_Franczak