If you worked with BCS in SharePoint 2010 you know that you can configure your BCS models and connection information right in the model configuration file, and that’s something developers will create for you. But once your model has been deployed to the server – all of the configuration information stays there. Some of the model details you can change using SharePoint Designer, some others you just can’t get to.
One of the most common things you’d want to change is connection information for your LOBSystem. There is not SharePoint out-of-the-box UI for it, but thankfully PowerShell can access and change BCS LOB System connection configuration.
Just so we’re on the same page, I assume you already have BCS model installed in your farm. Let’s assume your BCS system instance (LobSystemInstance) has the name of My.Project.BCSInstance; this is important to change in the commands below.
To get your LobSystemInstance value also used in the script below, navigate to Central Administration -> Manage Service Applications -> click Business Data Connectivity Service
-> switch the view to External Systems
In the same screen you will find your External System Name (in our case it’s My.Project.BCSSystem).
To find out External System Instance Name (in my case My.Project.BCSInstance), click on the BCS system name from the screen above and grab a value of External System Instance Name
Also, I assume you’re connecting to a SQLServer machine with the machine name of MySqlserver, you can also change that in the script below.
Open SharePoint 2010 Management Shell and execute the following script, substituting variables with your own.
$lob = Get-SPBusinessDataCatalogMetadataObject
-BdcObjectType "LobSystem"
-Name "My.Project.BCSSystem"
-ServiceContext "http://[site URL]"
$instance = $lob.LobSystemInstances | Where-Object {$_.Name -eq "My.Project.BCSInstance"}
Set-SPBusinessDataCatalogMetadataObject –Identity $instance
–PropertyName "AuthenticationMode" –PropertyValue "PassThrough"
Set-SPBusinessDataCatalogMetadataObject –Identity $instance
–PropertyName "RdbConnection Data Source" –PropertyValue "MySqlserver"
Set-SPBusinessDataCatalogMetadataObject –Identity $instance
–PropertyName "RdbConnection User ID" –PropertyValue "My.Sql.User"
Set-SPBusinessDataCatalogMetadataObject –Identity $instance
–PropertyName "RdbConnection Password" –PropertyValue "MyPassword#!"
Set-SPBusinessDataCatalogMetadataObject –Identity $instance
–PropertyName "RdbConnection Integrated Security" –PropertyValue " "
$instance.Update()
$lob.Update()
That’s it – the script above will connect to the BCS system instance and change any provisioned variables with the ones in the script.
Essentially, we’ve updated the connection information to our SQL Server backed BCS connection to use new username and password.
Loooot’s more on BCS configurations with PowerShell you can find in my newly published book!
Enjoy!
