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 🙂
That workaround will work as long as the database you are connecting to isn’t case sensitive as well.
Which maniac installs case sensitive databases?
For some, that’s a security option 😁
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.
Well that is disappointing, as usual. I hope they fix this problem at some point.