Configure Aliases Programmatically Using WMI

In my previous post, I provided some sample C# code that allows you to configure SQL Server connection properties remotely through Windows Management Instrumentation. But WMI can also be used to configure client connection properties on a remote machine. Below I will provide some sample C# code to manage client side Aliases.

Alias is a client side feature which enables you to use an alternative name to make a connection. For example, if you don't want to use explicit server name in your application or you don't wish to use SqlBrowser service for discovery, you may create server aliases. For this reason, a lot of IT professionals have the need to programmatically configure aliases remotely because they want to centrally manage hundreds or thousands of deployed client machines. Through WMI, you will be able to achieve that goal.

The following is some code that enables you to enumerate, edit, and add new aliases:

using

System;
using System.Management;

. . . . .
class SqlClientWMI
{
   [STAThread]
static void Main(string[] args)
{
. . . . .
string cliName = "YourClientMachineName";
AddAlias(cliName, "My Alias 1", "MyServer1", "np", @"sqlquery");
AddAlias(cliName, "My Alias 2", "MyServer2", "tcp", "1433");
EditTcpAlias(cliName, "My Alias 2", 8888);
ShowAliases(cliName);
. . . . .
}

public static void ShowAliases(string client)
{
ManagementScope scope = new ManagementScope(@"\" + client + @"rootMicrosoftSqlServerComputerManagement");
ManagementClass clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
clientAlias.Get();

foreach (ManagementObject alias in clientAlias.GetInstances())
{
Console.WriteLine("[" + (String)alias.GetPropertyValue("AliasName") + "]: " +
(String)alias.GetPropertyValue("ServerName") + ", " +
(String)alias.GetPropertyValue("ProtocolName") + ", " +
(String)alias.GetPropertyValue("ConnectionString"));
}
}

public static void AddAlias(string client, string aliasName, string server, string prot, string conn)
{
ManagementScope scope = new ManagementScope(@"\" + client + @"rootMicrosoftSqlServerComputerManagement");
ManagementClass clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
clientAlias.Get();

ManagementObject alias = clientAlias.CreateInstance();
alias.SetPropertyValue("AliasName", aliasName);
alias.SetPropertyValue("ServerName", server);
alias.SetPropertyValue("ProtocolName", prot);
alias.SetPropertyValue("ConnectionString", conn);
alias.Put();
}

public static void EditTcpAlias(string client, string aliasName, int port)
{
ManagementScope scope = new ManagementScope(@"\" + client + @"rootMicrosoftSqlServerComputerManagement");
ManagementClass clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
clientAlias.Get();

foreach (ManagementObject alias in clientAlias.GetInstances())
{
alias.Get();
if (String.Equals((String)alias.GetPropertyValue("AliasName"), aliasName) &&
String.Equals((String)alias.GetPropertyValue("ProtocolName"), "tcp"))
{
alias.SetPropertyValue("ConnectionString", port.ToString());
alias.Put();
return;
}
}
}

}

Remember to add your own exception handling code. Please note that when you try to add an alias but an existing alias on that machine already has the same alias name, this name conflict will not throw an exception. Instead, that alias will take on the new property values. Also note that the alias name is case insensitive, meaning that if you first add "Alias 1" and then add "alias 1", only 1 alias will be added and the latter alias name will be displayed as its name.

For this remote WMI script to work, the client machine does not have to have SQL Server installed, you only need to install SNAC and SQL client components. Both of these them are on your SQL Server media or package. Additionally, the firewall on the client machines must allow remote administration.

Ju-Yi Kuo, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights