Power BI Desktop overwrites Authentication Settings in Similar Connections

Yesterday I was facing an annoying issue. I have two Power BI datasets. Both connect to the same Azure SQL database, but with different SQL users (they have both different permissions on the data in the database). This can be a use case for example where you have data for multiple clients inside one single database.

Let’s say I have dataset A and dataset B, both are two different Power BI Desktop files (.pbix). They connect to the same database, but pbix A uses SQL user alpha and pbix B uses SQL user beta. I enter the connection information in pbix A and refresh the dataset. Everything is fine. I update the connection settings in pbix B and refresh that dataset as well. Again, everything works. However, when I open pbix A again and refresh, it uses SQL user beta instead of alpha. Because the connection string (server – database name) is the same, Power BI Desktop overwrites the settings.

Luckily there’s an easy work around (thanks to @benrebooted and @freder1ck for the suggestion). You simply change the name of the database to uppercase in one connection, and to lowercase in the other connection. Since Power Query is case sensitive (and that’s where the connection is created), Power BI Desktop will treat both connections as separate and will no longer overwrite the database credentials.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

5 thoughts to “Power BI Desktop overwrites Authentication Settings in Similar Connections”

  1. You are in for a rude awakening. We have a ticket open with Microsoft for a similar issue. They told us that the “connection strings are case sensitive” dogma is actually more of an accident, and that their backend team is performing all kinds of crazy transforms on these strings. There is NO guarantee that your “solution” will work for any length of time.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.