Skip to content

Instantly share code, notes, and snippets.

@JohnLBevan
Last active August 12, 2019 08:05
Show Gist options
  • Save JohnLBevan/9b6bc5d03bb6bd65860cc06c4485a0fc to your computer and use it in GitHub Desktop.
Save JohnLBevan/9b6bc5d03bb6bd65860cc06c4485a0fc to your computer and use it in GitHub Desktop.
Get List of Servers from SSMS (i.e. historically used connections list from the New Connection dialogue)
Add-Type -Path (Get-Command 'Microsoft.SqlServer.Management.UserSettings.dll').Source
[bool]$loaded = $false
Get-Item -Path 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\*\Microsoft.SqlServer.ConnectionInfo.dll' | Select-Object -ExpandProperty FullName | Sort-Object -Descending | ForEach-Object {if (!$loaded ){try {Add-Type -Path $_;Write-Verbose "Successfully loaded $_";$loaded=$true}catch{Write-Warning "Failed to load $_"}}}
[string]$settingsFilePath = (Resolve-Path (Join-Path -Path $env:APPDATA -ChildPath 'Microsoft/SQL Server Management Studio/*/SqlStudio.bin')).Path | Sort-Object -Descending | Select-Object -First 1
if ($settingsFilePath) {
try {
[System.IO.MemoryStream]$ms = [System.IO.File]::ReadAllBytes($settingsFilePath)
[System.Runtime.Serialization.Formatters.Binary.BinaryFormatter]$formatter = New-Object -TypeName 'System.Runtime.Serialization.Formatters.Binary.BinaryFormatter'
[Microsoft.SqlServer.Management.UserSettings.SqlStudio]$sqlStudio = $formatter.Deserialize($ms) #[Microsoft.SqlServer.Management.UserSettings.SettingsDictionary[[System.Guid],[Microsoft.SqlServer.Management.UserSettings.ServerTypeItem]]]
foreach ($serverTypeItem in $sqlStudio.SSMS.ConnectionOptions.ServerTypes.Values) { #[Microsoft.SqlServer.Management.UserSettings.ServerTypeItem]
foreach ($server in $serverTypeItem.Servers) { # [Microsoft.SqlServer.Management.UserSettings.ServerConnectionItem]
([PSCustomObject][Ordered]@{
Instance = $server.Instance
AuthMeth = ([Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]$server.AuthenticationMethod)
Connections = $server.Connections
})
}
}
} finally {
$ms.Dispose()
}
} else {
throw 'Could not find SqlStudio.bin. Older versions used MRU.dat... but I''ve not coded a solution for that, sorry!'
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment