Could not change the data source to another DB on the same server

[arunbyc]

I tried to change the data source of a visual (abc.pbix)  from one database ("MyDatabase-Dev") to another database ("MyDatabase-Test") which is also on the same server using Parameters but that did not work. So I tried to change the data source manually to troubleshoot the issues.

I created a dummy visual in the Test database using one of the tables from abc.pbix.  clicked TransformData, copied the code from the "Navigation" step, and pasted it on the Navigation step of the same table in adc.pbix on Dev in power query editor.

I got an error message that "MyDatabase-Test" wasn't recognized. Make sure you spelled it correctly".

(This was the same message I received even when I tried changing the data source through parameters). But this time I did not even type the name. I cut and pasted it as above.

I then tried to do the same copy/paste the source name using the advanced editor instead of in the Power Query window.

I received a message that "Mydatabase-Test matches no exports. Did you miss a module reference?

I have to get the Reports to be tested in the Test environment soon. Can you please help how to resolve or troubleshoot this issue?

Thanks

AR

[rohit1991 · Accepted solution]

Ensure you can connect to MyDatabase-Test via SQL Server Management Studio to verify access and credentials. Go to File > Options and Settings > Data Source Settings in Power BI, clear permissions, and re-enter credentials for MyDatabase-Test. In Power Query, update the Source step to:

Source = Sql.Database("ServerName", "MyDatabase-Test")

Ensure the database name is correctly spelled. Create a new Power BI report and connect to MyDatabase-Test to confirm the connection works. Verify all queries use the updated parameter and not hardcoded values. If issues persist, involve your DBA to check server/module settings.

[arunbyc]

Even after clearing permissions, I kept getting errors that either the database name was not recognized or that my Credentials were not valid, and so on.  I could finally get this to work, and use parameters to change the DataSource, only after I changed the Privacy Level from "Organizational" to "None". While this is working now, I am a little concerned whether this Privacy level is acceptable. Can you please help me with the following:

1) Are changing the Data Source and selecting "Organizational" privacy level somehow not compatible?

(I was forced to re-login every time I changed Data Source when privacy was "Organizational"). Or is it a coincidence?

2) Right now I have all tables on one server but in Version 2 I may have two data sources, possibly from two different servers within our company. Will the Privacy level of None cause any issues at that time?

AR

[arunbyc]

Thanks for your reply. My access to Mydatabase Test in SSMS works fine.

Coming to PowerBI,  since "clear permissions" gives a warning that you can't get those permissions back, I want to make sure I'm doing this right.

Here's what I did:

1. Blank Report - >  get Data - >Connected to  MyDatabase-Test 

2. Went to Options and Settings.

I saw two options in the Search Datasource settings window:

• Data Sources in Current File: Lists MyDatabase-Dev and MyDatabase-Test.

• Global Settings: Shows three options:

  1. Server name

  2. MyDatabase-Dev

  3. MyDatabase-Test

Both options have a "Clear Permissions" button.

I don't want to accidentally lose access to the entire server or the Dev if something goes wrong. Can you advise me on the following, so I am sure of the specifics:

1. Which option should I select to clear permissions when I go to  Options and Settings > Data Source Settings- The Current file or Global Permissions?

2. If I highlight MyDatabase-Test, does it only clear permissions for that connection without affecting the permissions to the entire server or Dev database?

[v-xingshen-msft · Accepted solution]

Hi @arunbyc ,

Based on our test results, if you select a specific data (e.g. MyDatabase-Test) in the “Data Source Settings” and clear its permissions, this will only affect the permissions settings for that specific connection. Other data sources (e.g. MyDatabase-Dev) will not be affected, you can remove Test permissions in Gobal Permissions, in Data Sources in Current File, we can only change it to replace other databases, not remove it completely.
For you want to change the data source of the problem, I would like to add a point is to ensure that your two table structure is the same, the same type of data, if not the same then there will be the following situation Power BI can not recognize the export of matching fields or tables, there will be an error similar to “matches no exports”.
Below are the relevant resolved cases I found for you I hope this helps!
Solved: clear permission data source power bi - Microsoft Fabric Community

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991 · Accepted solution]

Ensure you can connect to MyDatabase-Test via SQL Server Management Studio to verify access and credentials. Go to File > Options and Settings > Data Source Settings in Power BI, clear permissions, and re-enter credentials for MyDatabase-Test. In Power Query, update the Source step to:

Source = Sql.Database("ServerName", "MyDatabase-Test")

Ensure the database name is correctly spelled. Create a new Power BI report and connect to MyDatabase-Test to confirm the connection works. Verify all queries use the updated parameter and not hardcoded values. If issues persist, involve your DBA to check server/module settings.