How to get datasource from Connectionstring using PowerShell?

Solutions

Option A - Use Dictionary Names

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

$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder["Data Source"] = "server_name"
$cnnBuilder."Initial Catalog" = "db_name"

Option B - Use PSBase

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

$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.PSBase.DataSource = "server_name"
$cnnBuilder.PSBase.InitialCatalog = "db_name"

Option C - 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."

$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder `
    -Property @{
        DataSource = "server_name"
        InitialCatalog = "db_name"
    }

Explanation

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#:

var cnnBuilder = new SqlConnectionStringBuilder();
cnnBuilder.DataSource = "server_name";
cnnBuilder.InitialCatalog = "db_name";

So the equivalent code in PS, should work:

$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.DataSource = "server_name"
$cnnBuilder.InitialCatalog = "db_name"

However, SqlConnectionStringBuilder is built on top 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):

public override object this[string keyword] {
    get {
        Keywords index = GetIndex(keyword);
        return GetAt(index);
    }
    set {
        Keywords index = GetIndex(keyword);
        switch(index) {
            case Keywords.DataSource: DataSource = ConvertToString(value); break;
            case Keywords.InitialCatalog: InitialCatalog = ConvertToString(value); break;
            // ***
        }
    }
}

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

internal static class DbConnectionStringKeywords {
    internal const string DataSource = "Data Source";
}

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.

Additional Reading

• Using SQLConnection object in PowerShell