Forum Discussion

hughv99's avatar
hughv99
New Member
1 year ago
Solved

Gateway Connection - basic authentication

Hello,

 

I tried to search this forum to see if anyone came across the same issue but didn't find any solution match. We created a secondary gateway to the cluster and point to a new 2017 SQL server using the On-Premise data gateway. It successfully created. On the PowerBI backend (MS), it sees the secondary connection. When attempted to add 'Connections' and point to the SQL, server, db, it asks for Authentication. We tried to use:

Authentication method: basic

Username:  jjone (domain user)

Password:  **** (domain password)

 

This user has access to the SQL and database as well. 

 

We are getting this error: 

 

 

What is the authentication looking for? 

 

Thx

Hugh

 

 

 

Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    Hi hughv99 
    In addition reply to GilbertQ please cross verify the blow steps:

    Check SQL Server User Permissions.

    Confirm that the user jjone has the necessary "db_owner" or at least "db_datareader" permissions on the SQL database.

    Run the following query on SQL Server to verify permissions:

    ***************************************************************
    SELECT dp.name AS UserName,
    dp.type_desc AS UserType,
    p.permission_name,
    p.state_desc
    FROM sys.database_principals dp
    JOIN sys.database_permissions p
    ON dp.principal_id = p.grantee_principal_id
    WHERE dp.name = 'jjone';

    ***************************************************************

    Check the On-Premises Data Gateway Configuration:

    • Make sure the gateway is properly installed and running.
    • Open the On-Premises Data Gateway app, go to Service Settings, and verify:
      • The gateway is running.You are using the correct region in Power BI Service.
      • The account used to run the gateway has the correct permissions to access SQL Server.

    If none of these steps resolve the issue, try using "SQL Profiler" to capture login attempts and analyze what credentials are being passed to the SQL Server.

    If the above information helps you, please give us a Kudos and marked the reply a Accept as Solution.

    Thanks,
    Cheri Srikanth

     

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    1 year ago

    Hi hughv99 

     

    Can you please confirm me that you're using windows authentication to authenticate your sql server? And if you could also please confirm that your BI Gateway that you created in the second instance also can access the SQL Server when logging  from the second server?

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    1 year ago

    Hi hughv99 
    In addition reply to GilbertQ please cross verify the blow steps:

    Check SQL Server User Permissions.

    Confirm that the user jjone has the necessary "db_owner" or at least "db_datareader" permissions on the SQL database.

    Run the following query on SQL Server to verify permissions:

    ***************************************************************
    SELECT dp.name AS UserName,
    dp.type_desc AS UserType,
    p.permission_name,
    p.state_desc
    FROM sys.database_principals dp
    JOIN sys.database_permissions p
    ON dp.principal_id = p.grantee_principal_id
    WHERE dp.name = 'jjone';

    ***************************************************************

    Check the On-Premises Data Gateway Configuration:

    • Make sure the gateway is properly installed and running.
    • Open the On-Premises Data Gateway app, go to Service Settings, and verify:
      • The gateway is running.You are using the correct region in Power BI Service.
      • The account used to run the gateway has the correct permissions to access SQL Server.

    If none of these steps resolve the issue, try using "SQL Profiler" to capture login attempts and analyze what credentials are being passed to the SQL Server.

    If the above information helps you, please give us a Kudos and marked the reply a Accept as Solution.

    Thanks,
    Cheri Srikanth

     

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    1 year ago

    Hi hughv99 

    We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please accept as solution to help others benefit. If not, feel free to reach out.
    Thank you.

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    1 year ago

    Hi hughv99 

    I wanted to check if you had a chance to try the suggested solution.
    Please let us know if you need any further assistance.

    Looking forward to your update!

    Thanks you.

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    1 year ago

    Hi hughv99 
    It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
    If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
    Looking forward to your reply!