SSMS installation on off-line computer

In secure networks I have several times found it necessary to install a tool station computer without internet access. Most tools like editors can easily be downloaded from another computer and the installed on the tool station. But Microsoft SQL Server Management Studio (SSMS) has some issues when installing without internet access.

This is an example on the type of error message I usually get. In this case it is from installing SSMS 17.2 on a danish Windows 7. But I have had similar messages on previous SSMS versions and on other Windows version.
I have not found a official description from Microsoft on this situation. One of the first times I ran into this I was actually sitting with several Microsoft SQL Server consultants and PFE's, and they came up with a solution by some mails.

When a SSMS Setup Failure happens it is also logged in a SsmsSetup log file. This log file is in the folder "C:\Users\<User Name>\AppData\Local\Temp\SsmsSetup".
(** Insert example **)

The short description is that SSMS is missing two certificates, that it is used to download during SSMS installation. The two certificates are

• MicRooCerAut2011_2011_03_22 (link)
• MicRooCerAut_2010-06-23 (link)

You will have to install the certificates before installing SSMS.
After you have downloaded the certificates as cer-files you will have to import the certificates into Windows. It does not matter where you place the cer-files.
I know there are several guides on  importing certificates into Windows, but I go through the details anyhow to avoid doubt.

1. Start Microsoft Management Console (mmc.exe)
2. Add the snap-in "Certificates" (Ctrl + M)
3. When you mark the snap-in to be added to MMC you will have to select which account type the snap-in should manage. Select Computer Account 
4. You will also have to select which computer the snap-in should manage. Select Local Computer
5. After adding the Certificates snap-in you should expand Certificates > Root Key Centers... (or Trusted Root Certification Authorities) > Certificates
6. Right-click the item Certificates and select All Tasks > Import...
7. This will start the guide Certificate Import
8. Select to place all certificates in the Certificate Store named Root Key Center... (or Trusted Root Certification Authorities)
9. When both certificates are imported with success you can close MMC

All this I am sure can to automated by a script, but as the computer is off-line there are also some issues getting the script file to the computer.

Code Region in Management Studio

In PowerShell ISE I am used to put the code in collapsable/expandable blocks with #region, but that is not direct possible in SQL Server Management Studio.

But this entry in stackoverflow gives a workaround: "sql server #region".
The precise details on how to configure Management Studio is given by Buck Woody in the elder blog post "Code Collapse and Expand Feature in SQL Server Management Studio 2008".
Actually Buck gives the whole workaround, but my search gave me stackoverflow first...

A short example:
BEGIN -- Region Title
--several
--lines
--of
--T-SQL
--and
--comments
END -- Region Title
If you put a space or two last in the region title the region collapse in a visual pleasant way.
I repeat the region title at the end of the block. Sometimes there are more than one screen full in one block, and this way I am reminded about the context of the END-line.

And it works fine in Management Studio 2014, also with SQL Prompt installed.

Management Studio 2005 registered servers

Unfortunaly it is not possible to share the registered servers in Management Studio 2005 while the file "RegSrvr.xml" can only be placed in the folder "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell".
A solution could be to generate the registered servers file dynamic using MSXML and JScript.
This is a basic example on how to generate a registered servers file with one database instance:

var ProgId = "MSXML2.DOMDocument.3.0";
var XmlDoc = new ActiveXObject( ProgId );
XmlDoc.async = false;
XmlDoc.validateOnParse = false;
XmlDoc.resolveExternals = false;

var XmlNode= XmlDoc.createProcessingInstruction( "xml", "version='1.0' encoding='utf-8'" );
XmlDoc.appendChild( XmlNode );

XmlNode = XmlDoc.createElement( "RegisteredServers" );

var ServerType = XmlDoc.createElement( "ServerType" );
var ServerTypeId = XmlDoc.createAttribute( "id" );
ServerTypeId.value = "8c91a03d-f9b4-46c0-a305-b5dcc79ff907";
ServerType.setAttributeNode( ServerTypeId );
var ServerTypeName = XmlDoc.createAttribute( "name" );
ServerTypeName.value = "Database Engine";
ServerType.setAttributeNode( ServerTypeName );

var Server = XmlDoc.createElement( "Server" );
var ServerName = XmlDoc.createAttribute( "name" );
ServerName.value = "SANDBOX\\SSDB\_0";
Server.setAttributeNode( ServerName );
var ServerDescription = XmlDoc.createAttribute( "description" );
ServerDescription.value = "Primary sandbox database instance";
Server.setAttributeNode( ServerDescription );

var ConnectionInformation = XmlDoc.createElement( "ConnectionInformation" );

var SrvType = XmlDoc.createElement( "ServerType" );
SrvType.text = ServerTypeId.value;
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvType );

var SrvName = XmlDoc.createElement( "ServerName" );
SrvName.text = "tcp:SANDBOX.SQLAdmin.dk,1521";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvName );

var AuthenticationType = XmlDoc.createElement( "AuthenticationType" );
AuthenticationType.text = "0";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AuthenticationType );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "UserName" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "Password" ) );

var AdvancedOptions = XmlDoc.createElement( "AdvancedOptions" );

var PacketSize = XmlDoc.createElement( "PACKET_SIZE" );
PacketSize.text = "4096";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( PacketSize );

var ConnectionTimeout = XmlDoc.createElement( "CONNECTION_TIMEOUT" );
ConnectionTimeout.text = "15";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ConnectionTimeout );

var ExecTimeout = XmlDoc.createElement( "EXEC_TIMEOUT" );
ExecTimeout.text = "0";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ExecTimeout );

var EncryptConnection = XmlDoc.createElement( "ENCRYPT_CONNECTION" );
EncryptConnection.text = "False";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( EncryptConnection );
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AdvancedOptions );
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );

Server.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );
Server.appendChild( ConnectionInformation );
Server.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );

ServerType.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );
ServerType.appendChild( Server );
ServerType.appendChild( XmlDoc.createTextNode( "\n\t" ) );

XmlNode.appendChild( XmlDoc.createTextNode( "\n\t" ) );
XmlNode.appendChild( ServerType );
XmlNode.appendChild( XmlDoc.createTextNode( "\n" ) );
XmlDoc.appendChild( XmlNode );

XmlDoc.save( "RegSrvr.xml" );

Please notice that the server name to be displayed has extra "\" to get the right output. Also there are added some newline and tab code to get a readable result:

<?xml version="1.0" encoding="utf-8"?>
<registeredservers>
 <servertype id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
  <server name="SANDBOX\SSDB_0" description="Primary sandbox database instance">
   <connectioninformation>
    <servertype>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</servertype>
    <servername>tcp:SANDBOX.SQLAdmin.dk,1521</servername>
    <authenticationtype>0</authenticationtype>
    <username/>
    <password/>
    <advancedoptions>
     <PACKET_SIZE>4096</PACKET_SIZE>
     <CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>
     <EXEC_TIMEOUT>0</EXEC_TIMEOUT>
     <ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>
    </advancedoptions>
   </connectioninformation>
  </server>
 </servertype>
</registeredservers>

If you have a repository on you installed SQL Server services like Microsoft System Center Operations Manager (SCOM) you could use that as source in a extended version of the script above.
If you want to extend the script to generate registrations of instances of Integration Services, Analysis Services or Reporting Services, you can first register one instance of each manually to get the needed Server Types.

MSXML is documented by Microsoft at MSDN Library > Win32 and COM Development > Data Access and Storage > MSXML.

The script can only generate RegSrvr.xml for Management Studio 2005 while the format for Management Studio 2008 is rather different - but more on that another day...