Getting SQL Server Connection String

by Jul 20, 2015

If you’d like to contact an SQL Server database via PowerShell, you need a connection string. The connection string contains all pieces of information needed to contact the SQL server instance.

Traditionally, it is not trivial to construct such a connection string correctly. Here is a piece of code that defines a new function called Get-ConnectionString that helps you do this:

#requires -Version 2
function Get-ConnectionString
    $Path = Join-Path -Path $env:TEMP -ChildPath 'dummy.udl'
    $null = New-Item -Path $Path -ItemType File -Force
    $CommandArg = """$env:CommonProgramFiles\System\OLE DB\oledb32.dll"",OpenDSLFile "  + $Path 

    Start-Process -FilePath Rundll32.exe -ArgumentList $CommandArg -Wait
    $ConnectionString = Get-Content -Path $Path | Select-Object -Last 1
    $ConnectionString | clip.exe
    Write-Warning -Message 'Connection String is also available from clipboard'

When you run Get-ConnectionString, PowerShell opens a dialog, and you can submit and test the connection details. Once you close the dialog window, PowerShell returns the connection string you just created with the help of the UI dialog.

Twitter This Tip! ReTweet this Tip!