I've recently implemented a new backup process for our SQL Servers. The setup uses a scheduled task, running as an AD service account, that executes a PowerShell script. This AD service account has permissions to connect to a central SQL database and update statistics. This has proved convenient because I don't need to place a username/password in a plain text PowerShell script. However, this AD service account is not available for use on non-domain servers. Because I don't want to leave credentials in plain text, so here's the solution I came up with. 

1) Create a LOCALHOST\SQLBackup service account

2) Give the account the ability to log into the server

net localgroup "Remote Desktop Users" "LOCALHOST\SQLBackup" /add

3) Log into the server as LOCALHOST\SQLBackup and create a credential file

$path = "C:\scripts\sql\DOMAIN-SQLbackup.txt"
$username = "DOMAIN\SQLbackup"
$credreadonly = get-credential $username
$cred = $credreadonly | select-object *
$cred.password = $cred.password | convertfrom-securestring
$cred | export-clixml $path

Here's what DOMAIN-SQLbackup.txt looks like:

4) Log off the server as LOCALHOST\SQLBackup and revoke RDP permissions

net localgroup "Remote Desktop Users" "LOCALHOST\SQLBackup" /delete

5) Give the local service account the ability to run scheduled tasks and take backups

net localgroup "Backup Operators" "LOCALHOST\SQLbackup" /add

6) Install and configure the PowerShell backup script

$scriptPath = "C:\scripts\sql\backupsql.ps1"
$startTime = "05:00"
$backupUser = "LOCALHOST\SQLBackup"
invoke-expression ("schtasks -create -tn `"Backup-SQL`" -tr `"powershell -file '" + $scriptPath + "'`" -sc DAILY -st $startTime -rl HIGHEST -ru $backupUser -rp")

Note that the credential files are unique per server, meaning you can't transfer the file to a different server and decrypt it, even with the same user. 

In order to retrieve the encrypted password from the PowerShell script, I use the following function

function GetPassword($path)
{
	$account = import-clixml $path
	$account.password = $account.Password | convertto-securestring
	$cred = new-object system.Management.Automation.PSCredential($account.username, $account.password)
	return $cred.GetNetworkCredential().password
}

Here's example usage:

I wanted to add about 100 servers into SSMS registered servers:

But I definitely didn't want to spend the time manually adding each one. Especially because I wanted them sorted out in folders and also color coded. I searched around and found an example that bulk created the registered servers with T-SQL. That was helpful, but it didn't provide options for folders or color coding. I decided to write a PowerShell script to do this. The script takes a CSV file as input. Here's an example file:

DisplayName,Address,Folder,Type
server1,server1.company.com,Folder1,Prod
server2,server2.company.com,Folder1,PreProd
server3,server3.company.com,Folder1,QA
server4,server4.company.com,Folder2,Test
server5,server5.company.com,Folder2,Prod
server6,server6.company.com,Folder2,Dev
server7,server7.company.com,Folder3,PreProd
server8,server8.company.com,Folder4,Test

The CSV allows you to create a DisplayName for the server, point it to a certain address, categorize it into a folder, and specify a type. The type will decide which color it will be. In the script, the colors are defined with ARGB values because that's what the export file needs to use. Here's the PowerShell script that uses the input file:

######################################################
# Configuration
######################################################

$importfile = "~\Desktop\servers.csv"
$exportfile = "~\Desktop\export.regsrvr"

$prodColor = "-32640" # Red
$preprodColor = "-19094" # Orange
$qaColor = "-67413" # Yellow
$testColor = "-11294209" # Blue
$devColor = "-5243251" # Green

######################################################
# Constants
######################################################

$contents = ""

$section1 = @'
<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
    <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
    <baseURI>http://documentcollection/</baseURI>
  </identity>
  <xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
      <document>
        <docinfo>
          <aliases>
            <alias>/system/schema/RegisteredServers</alias>
          </aliases>
          <sfc:version DomainVersion="1" />
        </docinfo>
        <data>
          <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
            <xs:element name="ServerGroup">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="RegisteredServer">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
              <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:ServerGroups>
                        <sfc:Collection>

'@

$section2 = @'
                        </sfc:Collection>
                      </RegisteredServers:ServerGroups>
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>

'@

$section3 = @'
              </instances>
            </RegisteredServers:bufferData>
          </xs:schema>
        </data>
      </document>
    </definitions>
  </xs:bufferSchema>
</model>
'@

######################################################
# Functions
######################################################

function AddServers($csv)
{
	$script:contents = $section1
	
	# Build a list of unique folders
	$folders = @()
	foreach($c in $csv) {
		if(!$folders.contains($c.Folder)) {
			$folders += $c.Folder
		}
	}
	
	# Add an entry for each folder
	foreach($f in $folders)
	{
		$script:contents += 
			"                          <sfc:Reference sml:ref=`"true`">`r`n" + 
			"                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/" + $f + "</sml:Uri>`r`n" + 
			"                          </sfc:Reference>`r`n"
	}
	
	$script:contents += $section2
	
	# For each folder, add a folder entry, and within it, add an entry for each server in that folder
	foreach($f in $folders)
	{
		$script:contents += 
			"                <document>`r`n" + 
			"                  <docinfo>`r`n" + 
			"                    <aliases>`r`n" + 
			"                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/" + $f + "</alias>`r`n" + 
			"                    </aliases>`r`n" + 
			"                    <sfc:version DomainVersion=`"1`" />`r`n" + 
			"                  </docinfo>`r`n" + 
			"                  <data>`r`n" + 
			"                    <RegisteredServers:ServerGroup xmlns:RegisteredServers=`"http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08`" xmlns:sfc=`"http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08`" xmlns:sml=`"http://schemas.serviceml.org/sml/2007/02`" xmlns:xs=`"http://www.w3.org/2001/XMLSchema`">`r`n" +
			"                      <RegisteredServers:RegisteredServers>`r`n" + 
			"                        <sfc:Collection>`r`n"
		
		# Within each folder, add an entry for each of the servers in that folder
		foreach($c in $csv)
		{
			if($f -eq $c.Folder)
			{
		$script:contents += 
			"                          <sfc:Reference sml:ref=`"true`">`r`n" + 
			"                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/" + $f + "/RegisteredServer/" + $c.DisplayName + "</sml:Uri>`r`n" + 
			"                          </sfc:Reference>`r`n"
			}
		}
		
		$script:contents += 
			"                        </sfc:Collection>`r`n" + 
			"                      </RegisteredServers:RegisteredServers>`r`n" + 
			"                      <RegisteredServers:Parent>`r`n" + 
			"                        <sfc:Reference sml:ref=`"true`">`r`n" + 
			"                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>`r`n" + 
			"                        </sfc:Reference>`r`n" + 
			"                      </RegisteredServers:Parent>`r`n" + 
			"                      <RegisteredServers:Name type=`"string`">" + $f + "</RegisteredServers:Name>`r`n" + 
			"                      <RegisteredServers:Description type=`"string`" />`r`n" + 
			"                      <RegisteredServers:ServerType type=`"ServerType`">DatabaseEngine</RegisteredServers:ServerType>`r`n" + 
			"                    </RegisteredServers:ServerGroup>`r`n" + 
			"                  </data>`r`n" + 
			"                </document>`r`n"
	}

	# Add an entry for each server
	foreach($c in $csv)
	{
		$script:contents += 
			"                <document>`r`n" + 
			"                  <docinfo>`r`n" + 
			"                    <aliases>`r`n" + 
			"                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/" + $c.Folder + "/RegisteredServer/" + $c.DisplayName + "</alias>`r`n" + 
			"                    </aliases>`r`n" + 
			"                    <sfc:version DomainVersion=`"1`" />`r`n" + 
			"                  </docinfo>`r`n" + 
			"                  <data>`r`n" + 
			"                    <RegisteredServers:RegisteredServer xmlns:RegisteredServers=`"http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08`" xmlns:sfc=`"http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08`" xmlns:sml=`"http://schemas.serviceml.org/sml/2007/02`" xmlns:xs=`"http://www.w3.org/2001/XMLSchema`">`r`n" +
			"                      <RegisteredServers:Parent>`r`n" + 
			"                        <sfc:Reference sml:ref=`"true`">`r`n" + 
			"                          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/" + $c.Folder + "</sml:Uri>`r`n" + 
			"                        </sfc:Reference>`r`n" + 
			"                      </RegisteredServers:Parent>`r`n" + 
			"                      <RegisteredServers:Name type=`"string`">" + $c.DisplayName + "</RegisteredServers:Name>`r`n" + 
			"                      <RegisteredServers:Description type=`"string`" />`r`n" + 
			"                      <RegisteredServers:ServerName type=`"string`">" + $c.DisplayName + "</RegisteredServers:ServerName>`r`n"
		
		if($c.Type.tolower() -eq "prod" -or $c.Type.tolower() -eq "preprod" -or $c.Type.tolower() -eq "qa" -or $c.Type.tolower() -eq "test" -or $c.Type.tolower() -eq "dev")
		{
		
		$script:contents += 
			"                      <RegisteredServers:UseCustomConnectionColor type=`"boolean`">true</RegisteredServers:UseCustomConnectionColor>`r`n"
		
			if($c.Type.tolower() -eq "dev")
			{
		
		$script:contents += 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">" + $devColor + "</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
			}
			elseif($c.Type.tolower() -eq "test")
			{
		
		$script:contents += 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">" + $testColor + "</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
			}
			elseif($c.Type.tolower() -eq "qa")
			{
		
		$script:contents += 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">" + $qaColor + "</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
			}
			elseif($c.Type.tolower() -eq "preprod")
			{
		
		$script:contents += 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">" + $preprodColor + "</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
			}
			else
			{
		
		$script:contents += 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">" + $prodColor + "</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
			}
		
		
		}
		else
		{
		
		$script:contents += 
			"                      <RegisteredServers:UseCustomConnectionColor type=`"boolean`">false</RegisteredServers:UseCustomConnectionColor>`r`n" + 
			"                      <RegisteredServers:CustomConnectionColorArgb type=`"int`">-986896</RegisteredServers:CustomConnectionColorArgb>`r`n"
		
		}
        
		$script:contents += 
			"                      <RegisteredServers:ServerType type=`"ServerType`">DatabaseEngine</RegisteredServers:ServerType>`r`n" + 
			"                      <RegisteredServers:ConnectionStringWithEncryptedPassword type=`"string`">server=" + $c.Address + ";trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>`r`n" + 
			"                      <RegisteredServers:CredentialPersistenceType type=`"CredentialPersistenceType`">PersistLoginName</RegisteredServers:CredentialPersistenceType>`r`n" + 
			"                    </RegisteredServers:RegisteredServer>`r`n" + 
			"                  </data>`r`n" + 
			"                </document>`r`n"
	}
	
	$script:contents += $section3
}

######################################################
# Main
######################################################

$csv = import-csv $importfile

AddServers $csv

$contents | out-file $exportfile

Running the script will create an export *.regsrvr file, which you can then import to SSMS:

And the end result is all of my servers registered and categorized into folders:

And finally, when I open them I get the color coding as defined by the script: