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 team gave me a windows user to logging to the new server. I changed the data source of my earlier report to  new server (using alternate windows authentication domain\userid) but it gives me following error. Does any one know the solution for this? Is direct query is not possible under windows authentication ?

 

 

 

Regards,

Maddy

 

 

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

8 Replies

  • 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

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

    Hi Anonymous,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      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,

         

        Thank you very much for sharing. This is really a big help to the Community.

         

        Best Regards,

        Dale