How to get datasource from Connectionstring using PowerShell?


In .Net we can get the datasource from a connectionstring using below mechanism:

I was trying to do that in PowerShell but getting the following exception:

New-Object : Exception calling “.ctor” with “1” argument(s): “Keyword
not supported: ‘metadata’.” At line:1 char:17
+ $ConstringObj = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($con …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

How to do that in PowerShell?



There’s some weird behavior when using SqlConnectionStringBuilder in PowerShell – let me explain

Since it’s a dotnet class, you’d expect all of the same properties and methods available in C#

For example, this works fine in C#:

So the equivalent code in PS, should work:

However, SqlConnectionStringBuilder is built ontop of DbConnectionStringBuilder which implements IDictionary so fundamentally we’re working with a dictionary object that has some syntactic sugar wrappers

.NET resolves this with an override on the dictionary accessors and setters like this (simplified here):

So really, it’s taking the DataSource property and mapping it to the "Data Source" key (with space)

Whenever PS assigns or retrieves a value, it has to decide whether to use the underlying dictionary implementation or the property. And when you look for DataSource in the dictionary (without the space), that sql connection keyword doesn’t exist.


Opt 1 – Use Dictionary Names

You can use the bracket or dot notation with the actual sql key to access the entry in the hashtable

Opt 2 – Use PSBase

PSBase returns the “raw view of the object” and will give us the default behavior in dotnet

Opt 3 – Use -Property Parameter

During the construction, you can set the -Property parameter on New-Object which “sets property values and invokes methods of the new object.”

Additional Reading

Using SQLConnection object in PowerShell


How to get datasource from Connectionstring using PowerShell? by licensed under CC BY-SA | With most appropriate answer!

Leave a Reply