cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
maddy Regular Visitor
Regular Visitor

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 ?

 

 

error3.PNG

 

Regards,

Maddy

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
maddy Regular Visitor
Regular Visitor

Re: Direct Query On SQL Server on Windows Authentication

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 8
Community Support Team
Community Support Team

Re: Direct Query On SQL Server on Windows Authentication

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.

 

Direct Query On SQL Server on Windows Authentication1.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

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-a...

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
maddy Regular Visitor
Regular Visitor

Re: Direct Query On SQL Server on Windows Authentication

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. 

 

Community Support Team
Community Support Team

Re: Direct Query On SQL Server on Windows Authentication

Hi @maddy,

 

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?

 

Direct Query On SQL Server on Windows Authentication2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
maddy Regular Visitor
Regular Visitor

Re: Direct Query On SQL Server on Windows Authentication

@v-jiascu-msft

 

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

 

error1.PNG

 

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

 

error2pbi.PNG

 

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.  Smiley Sad 

 

error3.PNG

 

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

Community Support Team
Community Support Team

Re: Direct Query On SQL Server on Windows Authentication

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Direct Query On SQL Server on Windows Authentication

Hi @maddy,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
maddy Regular Visitor
Regular Visitor

Re: Direct Query On SQL Server on Windows Authentication

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

Community Support Team
Community Support Team

Re: Direct Query On SQL Server on Windows Authentication

Hi Maddy,

 

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.