Using SqlConnectionStringBuilder in PowerShell

I wanted to use the SqlConnectionStringBuilder object in the SqlClient area but was having issues accessing the properties. The Object itself is just a collection with a key value structure.

To access the keys use the following PowerShell code:

$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder.Keys

To set a property just use the key as a reference. For example:

$builder["Data Source"] = $server
$builder["Initial Catalog"] = $database
$builder["Encrypt"] = $true
$builder["TrustServerCertificate"] = $false
$builder["User ID"] = $login
$builder["Password"] = $password
Sytone

I’m a principal engineer working at Microsoft. I have run and supported services for over twenty years and worked in multiple countries. I love working with passionate people and new technology.

Comments

  1. SqlConnectionStringBuild has a rather funky coexistence with PowerShell. Two other workarounds:

    $builder.psbase gives you access by property name – and when you type the . after psbase, you’ll (still) get intellisense, so you can free your brain from remembering (or manually discovering (RTFM? No!)) the property names.

    The Property parameter to New-Object will let you use the property names, as in
    $build = New-Object System.Data.SqlClient.SqlConnectionStringBuilder -Property {UserID=$login; Password=$password} # etc.

    Finally, I often use it in conjunction with either (Get-Credential).GetNetworkCredential() or [System.Configuration.ConfigurationManager]::ConnectionStrings (not critical to this specific exercise, but useful to know if you didn’t already)

    (commenting here because this was the first google hit)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.