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

Comments

• Anonymous
  September 10, 2007
  Hi Ju-Yi, I'm trying to navigate the wasteland of documentation surrounding SMO and WMI - there is either too much about similar topics or none about others. I'm looking to not only enable Tcp/Ip in the SQLExpress protocols, but also set the IP1, IP2 and IPAll properties associated with Tcp/Ip - How do you suggest doing this? I modified the prior examples to point to SQLExpress and get what appears to be success - but the management objects dont provide the clearest view for me to learn the object model simply by reading the members and/or traversing the properties... thanks, Rob