Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Direct Query On SQL Server on Windows Authentication

Hi ,   I had a power bi report which had connected to a SQL server using direct query.I used a database user to connect to the server. Now they have upgarded the database and our infrastructure tea...
Gateways
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Dale,

     

    I raised a Microsoft ticket regarding this issue. Their solution was this is not possible unless my windows user(AD user) has the access to the database. Since my AD user doesn't have access to the database i had to switch all my reports in to import mode. Direct query was not possible for my case.

     

    Regards,

    Maddy

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Maddy,

 

The error message is clear. This isn't supported by now. There is a workaround. Actually this post is quite clear and very helpful. Please go to there for details. I tested it successfully. I will post some snapshot of my test here.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

Reference: http://www.pseale.com/pretend-youre-on-the-domain-with-runas-netonly

https://www.mssqltips.com/sqlservertip/3250/connect-to-sql-servers-in-another-domain-using-windows-authentication/

 

 

Best Regards!

Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Dale,

 

Thanks for the reply. I have also tried this method. But once i logged in to the power bi as another user i couldnt login to my powerbi user and publish the report. 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    One problem is we have to open Desktop this way every time we open the file. I tested it. I can switch user freely. A proper gateway is needed. 

    Are there any error messages?

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-jiascu-msft

       

      Loggin to power bi with a different user working fine. when i login i get following.

       

       

      Since i already have a power bi account i click on "Already have a Power BI account? Sign In" at the bottom. Then it asks me to enter the email address

       

       

      Then i give the email address of my pbi account and click sign in . After that nothing happens. It didnt ask me a password or any thing. My pbi account doesnt change.  :smileysad: 

       

       

      My report connects to the datasource without any issue (Direct Query). Only problem i have now is i can't publish the report.

       

      Regards,

      Maddy

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Maddy,

         

        You can login in the Desktop first. Then do as my last post. I tested it successfully. You need use Windows authentication when you configure a data gateway.

         

        Best Regards!

        Dale