Creating semantic model sql connection error

 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

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: