Azure/PowerShell: Virtual Machines created from Images, Cleaning cached up SQL Server Host Names

As part of my DevOps role, I have created a set of standard images to be used by our QA engineers in creating test Virtual Machines on Azure. During setup, the QA lead reported that it was impossible to login to a SQL Server instance running on the virtual machine. The client being used to login to the instance of SQL Server was SQL Server Management Studio (SSMS). I investigated and found out that SSMS remembered the Server Name (host name) of the virtual machine from which the Azure image was created. The fix was simple. The QA lead needed to enter the current computer name of the virtual machine.

This was a usability issues that merited fixing. To explain more clearly assume the virtual machine that was used to create the Azure image was named, SrcHost4Image. This source virtual machine was running SQL Server. If a new Virtual Machine named, NewVM001, is created from the image, SSMS will show the origin host name:

The host, SrcHost4Image, no longer exists so there is no way to login with those SQL Server credentials.

The file where SSMS stores its most recently used server names should be updated. In earlier incarnations of SSMS that most recently used (MRU) server names were stored in a binary file, mru.dat for SQL Server Management Studio 2005 and SqlStudio.bin for more recent incarnations of SSMS. SQL Server Management Studio has moved to an XML file, UserSettings.xml, in order to store the MRU server names. On most configurations of Windows this file is found under:

C:\Users\%username%\AppData\Roaming\Microsoft\

    SQL Server Management Studio\18.0

The XML to be modified from the UserSettings.xml configuration file was of the form:

<#

<ServerConnectionItem>

    <Instance>SrcHost4Image</Instance>

    <AuthenticationMethod>0</AuthenticationMethod>

    <Connections>

    <Element>

        <Time>

        <long>-637307108466504502</long>

        </Time>

        <Item>

        <ServerConnectionSettings>

            <Instance>SrcHost4Image</Instance>

            <UserName>SrcHost4Image\Jan Narkiewicz</UserName>

#>

The PowerShell modifying the two <Instance> XML elements and the <UserName> XML element is as follows:

# $env:LOCALAPPDATA = C:\Users\Jan Narkiewicz\AppData\Roaming

# PowerShell 7 style Join-Path

[string] $userSettingsPath = Join-Path `

    $env:APPDATA 'Microsoft\SQL Server Management Studio\18.0'

[string] $userSettingsFilename = 'UserSettings.xml'

[string] $userSettingsFullFilename = 

    Join-Path $userSettingsPath $userSettingsFilename

function Get-FirstElement($elementCandidate)

{

    if ($elementCandidate.Count -eq 0)

    {

        return $null

    }

        

    elseif ($elementCandidate.Count -eq 1)

    {

        # if one element is found then no array is created 

        # (just an object)

        return $elementCandidate

    }

    

    elseif ($elementCandidate.Count -gt 1)

    {

        # if more than one element is found then an array is created 

        # (select 0th element)

        return $elementCandidate[0]

    }    

}

[xml]$settings = Get-Content $userSettingsFullFilename

<#

<ServerConnectionItem>

    <Instance>SrcHost4Image</Instance>

    <AuthenticationMethod>0</AuthenticationMethod>

    <Connections>

    <Element>

        <Time>

        <long>-637307108466504502</long>

        </Time>

        <Item>

        <ServerConnectionSettings>

            <Instance>SrcHost4Image</Instance>

            <UserName>SrcHost4Image\Jan Narkiewicz</UserName>

#>

$serverTypesElements = $settings.SqlStudio.SSMS.ConnectionOptions.ServerTypes.Element

if ($serverTypesElements.Count -eq 0)

{ 

    exit

}

[System.Xml.XmlElement] $serverTypeItemElement

$serverTypeItemElement = Get-FirstElement $serverTypesElements[0].Value.ServerTypeItem.Servers.Element

if ($serverTypeItemElement -eq $null)

{

    exit

}

    

[System.Xml.XmlElement] $serverConnectionItem = $serverTypeItemElement.Item.ServerConnectionItem

 

$serverConnectionItem.Instance = $env:computername

    

[System.Xml.XmlElement] $serverConnectionSettings = $serverConnectionItem.Element.ServerConnectionSettings

$serverConnectionSettings.Instance = $env:computername

$serverConnectionSettings.UserName = $env:computername + '\'+ $env:UserName

$settings.Save($userSettingsFullFilename)

Appendix A: Locations of SSMS User Settings Files

To force SSMS to forget legacy for older versions of SSMS, the user settings file (a binary file) should be deleted. This sections lists the legacy configuration files used by SQL Server Management Studio:

SQL Server Management Studio 2005: 

C:\Users\%username%\AppData\Roaming\Microsoft\

    Microsoft SQL Server\90\Tools\Shell\mru.dat

SQL Server Management Studio 2008: 

C:\Users\%username%\AppData\Roaming\Microsoft\

    Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SQL Server Management Studio 2012: 

C:\Users\%username%\AppData\Roaming\Microsoft\

    SQL Server Management Studio\11.0\SqlStudio.bin

SQL Server Management Studio 2014: 

C:\Users\%username%\AppData\Roaming\Microsoft\

    SQL Server Management Studio\12.0\SqlStudio.bin

SQL Server Management Studio 2016: 

C:\Users\%username%\AppData\Roaming\Microsoft\

    SQL Server Management Studio\13.0\SqlStudio.bin

SQL Server Management Studio 2017:

C:\Users\%username%\AppData\Roaming\Microsoft\

    SQL Server Management Studio\14.0\SqlStudio.bin