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.
The post Power BI Desktop overwrites Authentication Settings in Similar Connections first appeared on Under the kover of business intelligence.