Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bfrancis
Advocate I
Advocate I

on-premises data gateway (personal mode) SQL server basic login not working

I installed the latest On-premises data gateway (personal mode) today and a SQL server database I had been successfully connecting to with the previous version (Power BI gateway - personal) is no longer working.  Specifically, I cannot connect to the server's database using the "Basic" login mode.  This has worked flawlessly since the first versions of the personal DMG, so something has clearly been broken.  Also, I am able to connect to other data sources (databases) in the same server using Windows authentication mode.  So, the problem is with "Basic" login mode.

 

This is a serious issue for us since many reports being refreshed use this data source.  Anyone else experiencing this problem?  Any suggestions on how to resolve?

 

Thanks,

Bob

17 REPLIES 17
dimazaid
Employee
Employee

hi @bfrancis,

Are you connecting using the SQL server connector? What's the error message are you seeing? 

Yes, see thread above in this post.  You can see the error I'm getting.

Got it. 
Since you have the new gateway can you do the following: 

- Re-open the on-premises data gateway (personal mode) from start menu 

- Sign in 

- Click on Export logs. This should put the gateway logs in a .zip file on your desktop 

- Send these logs to me (sent you my contact info as a message)

 

 

Hi @bfrancis

 

There has been a new release for the Personal Gateway here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-personal-mode/

 

I am hopeful that this would solve your current issues, so if you can install this and see if this now resolves your error?

 

If not then as per @dimazaid below, you can send him the log details to investigate further.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks.  I went to the URL and found the link to download the gateway.  The URL was the updated article in Power BI documentation.

 

I installed and am getting exactly the same error.  I'll send logs.  The gateway version looks like the same as the version I downloaded and installed yesterday 14.16.6349.1

 

Bob

Hi @bfrancis

 

Thanks for letting us know. I would say over to @dimazaid to look into the logs.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I reverted back to the previous version of the gateway provided by Dimah late yesterday afternoon.  I was able to get the SQL data source to take the credentials in Basic mode and all of the reports using that data source successfully refreshed last night.

 

So, there is definitely a problem with the latest version of the gateway with SQL Server 2008 sources using Basic authentication mode.

 

Thanks for all the help yesterday and I'm looking forward to the fixed version of the latest gateway.

Hi @bfrancis,

 

Thanks for sharing your question. But I didn't encounter above issue with the latest on-premises data gateway (personal mode) installed on my local machine, also using basic authentication. 

 

I would suggest you create a support ticket so that you can get more help about how to fix your problem. https://powerbi.microsoft.com/en-us/support/

 

Regards,
Yuliana Gu

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

I have been working with on this issue.  I am able to connect to other SQL databases with the latest PGW using Windows mode.  I think the problem may have something to do with the fact that the SQL db I am connecting to that fails with Basic mode authentication does not support encryption.  I get the following message when first connecting to this source using Power BI desktop using Basic authentication.

 

SQL db encryption dialog.png

 

Speculation on my part, but it's the only thing I can see that might be pertinent to the issue.

 

I found that I always got that when connecting to a SQL Server, it appears to always try and connect securely to SQL Server, and when that fails you get the prompt as shown below.

 

And in the past that did not cause any issues when doing that either.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I have been working with a Microsoft engineer on this problem and the root cause appears to be that the SQL Server database name is somehow being used by the Power BI service as lower case.  The database name in our SQL 2008 server is case sensitive and contains upper case letters, so the connection fails when the Power BI service uses lower case with the latest version of the personal gateway.  As I noted in previous posts, this problem did not happen with the previous version of the personal gateway or On-prem gateway.

 

Suggested workarounds from Microsoft are to turn off case sensitivity in our SQL 2008 server, or for me to delete all of my datasets and reupload making sure only the SQL 2008 data sources with correct database names are uploaded.  Be assured that the later case will not happen as it would take me weeks to rebuild dashboards and report shares.  I have not yet talked to our IT people, but I don't think disabling case sensitivity is a good idea either.  Plus, there is no guarantee either of these workarounds will work.

 

I believe this issues needs to remain an open ticket for Microsoft to fix.  It's not reasonable to ask users to change their working datasets just because the personal gateway or On-prem gateway has been changed.  The Power BI team has been regularly delivering great features and doing a great job of supporting us users.  The responsivness from Microsoft on this issue was excellent and Power BI is one of the best products Microsoft has released in years.  Please keep us happy and get this problem fixed ASAP.

 

I'd be happy to discuss this in person next week at the Data Insights Summit, just let me know.

Hi @bfrancis,

 

Thanks for sharing your question and the root cause for this problem. According to your response, we know that the SQL Server database name is somehow being used by the Power BI service as lower case with the latest version of the personal gateway, issue occurs when the database name is set to case sensitive. This will be a great notice to other users so that they can take care of this behavior when using the latest gateway. Besides, as you have submitted this issue to Microsoft, our product team will consider fixing it in later release. Appreciate for your patience.

 

Best regards,
Yuliana Gu

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

Hi @bfrancis

 

Have you tried to un-install and then re-install with it being an On-Premise gateway and see if that will resolve the error?

 

And can you also confirm that you can successfully connect to your SQL Server using Basic mode with SQL Server Management Studio?







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Previous post shows data source connecting with on-prem DMG.  Here's what I get from the On-Premises Data Gateway (personal mode) with the same data source.

 

DMG_SQL_source_error.JPG

DMG_SQL_source.JPG

It does appear to me that there is something wrong with the Personal Gateway.

 

I would suggest logging a support ticket, as I am not sure how else to troubleshoot the error.

 

https://powerbi.microsoft.com/en-us/support/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I have this data source in an On-premise gateway and it is working there.  I would prefer to only have the On-premise gateway, but it won't work with Oauth2 data sources (e.g. O365 Project Online, SP, etc.), so I have to have the personal gateway too.

 

I can log into the data base using an Excel file (I'm using SQL views in the database).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors