Beware the CMS Import

Many people already have connections setup in their local SSMS to all the servers in their environment.  We can import those connections into our CMS so we don’t have to manually recreate them all.  We can also export the connections in our CMS to have available for importing to another CMS, another local SSMS, or simply as an additional backup.  Please note that this should be an additional backup, because you should be backing up the MSDB on your CMS server.

Let’s look at an example.  We are going to export three local connections and import them to the East/Dev group in our CMS.  Here is what our local connection options look like, followed by what we see in SSMS.

Alias	Server/Instance	Authentication Type
Server1	Server1	Windows
Server1/INST01	Server1/INST01	SQL
File2.camelot.com	File2	Windows

We start by right clicking on Local Server Groups and going to Tasks>Export.  The following dialog allows us to choose which local group we want export, where to export it to, and if we want to include passwords.  By default, the option is selected to NOT include passwords, but we are going to uncheck that box so we can see what gets exported from a security perspective.

Before we import this into our CMS we want to make sure that no passwords are exported in clear text.  We want to look at this for a connection using Windows Authentication as well as a connection using SQL authentication.  We know from our matrix above that Server1 is using Windows Authentication, so let’s check that out first and see what got exported.

<RegisteredServers:ConnectionStringWithEncryptedPassword
type=”string”>server=Server1;trusted_connection=true;pooling=false;packet
size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>

We can see that the connection string is using the trusted_connection=true option as expected, so no password is included or necessary.  Now we know we don’t have to worry about plain text passwords being included with Windows Authenticated connections.  However, we still need to look at a SQL Authenticated connection.

<RegisteredServers:ConnectionStringWithEncryptedPassword
type=”string”>server=Server1INST01;uid=sa; password=AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAWN50rqJbK0KumkZmeSq0rQQAAAACAAAAADZgAAwAAAABAAAACf0muawUxos7e658B76JBJAAAAAASAAACgAAAAEAAAANgj0Wy9O+YOw2tppBqYAAAAplr80XKV/SuFiRHFwGnEQTno6rDFFAAAAP3b2Ll4WMHyOKzOMuCQqekAivqX;pooling=false;packet
size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>

Here we can see that the username is displayed in plain text, but the password is encrypted.  If displaying the username is a security or auditing concern for your company then you need to be aware of this.  However, we can rest easier knowing that the password is not exposed in plain text.

NOTE: Remember that CMS does not allow SQL Authenticated connections, so pay attention to what happens when you import it.  You can read more about that in my post on Disadvantages of using CMS.

Switching back into SSMS, we can import these connections to our CMS by right clicking the folder we want to put them in and going to Tasks>Import.  The dialog we receive asks for the file to import and the folder we want them imported to.  The folder to import to will default to the folder you right clicked on in the previous step, but you can change it here if you wish.

We have now successfully exported our local connections into our CMS.  Here is what our CMS looks like now.

There is only one thing left to check.  The connection for Server1/INST01 was using SQL Authentication in our local SSMS and that is not allowed in our CMS.  Here is what happens when you import a connection that uses a SQL Authenticated login.

You can still see the SQL Login ID that was specified for the connection, but Windows Authentication is now selected and greyed out.  SQL Authenticated logins automatically get switched to Windows Authenticated logins upon import.