Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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
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.
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.
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
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?
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.
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.