Pete's Log: SharePoint Designer doesn't like when you change BDC connection strings
Entry #2484, (SharePoint, Work)(posted when I was 45 years old.)
Sometimes you need to change what database server an External Content Type in SharePoint points at. And SharePoint Designer makes it easy to change the server name for an External System. After making your update, your external content types should happily load their data from the new server.
Problems won't surface until the next time you want to add or modify External Content Types using that External System, at least in the case where the old database server is no longer accessible. You'll get an error like one of the following:
If you're trying to create a new External Content Type it will tell you "The system definition with the same name as this data source already exists in the Business Data Connectivity Metadata Store and it refers to a different data source. Cannot complete operation generation. Add a connection to this data source with a different name and try again."
And if you're trying to edit an existing one, you'll get something like "Cannot connect to the SQL Server database defined in data source connection." with helpful added details like "Cannot connect to the LobSystem (External System)."
The reason for this is that SharePoint Designer (in all its wisdom) creates its own hidden reference to the external system, and when you update the connection details for the external system, it only updates the "production" reference to the external system, but not the hidden one that SharePoint Designer uses when you're editing External Content Types. So the External Content Types continue to work, but you can't edit them.
I could not find an "official" way to solve this problem. Microsoft does have a workaround for SharePoint Designer 2010, but none of their methods worked for me.
So instead I resorted to directly modifying the SharePoint BDC database. This is definitely not supported by Microsoft, but it happened to work for me (in SharePoint 2019). Proceed at your own risk.
In your BDC database, look in the AR_Property table for "RdbConnection Data Source" entries:
select * from AR_Property where name = 'RdbConnection Data Source'
Look for rows where the Value column contains the old database server name and update them to contain the new server name.
If you don't know the name of your BDC database, you can find it with PowerShell:
Get-SPDatabase | where {$_.Type -eq 'Microsoft.SharePoint.BusinessData.SharedService.BdcServiceDatabase'}
Again, directly modifying SharePoint databases is not supported by Microsoft, so make sure you know what you're doing before you proceed. And don't forget to have a backup available!