SQL Alias

Some applications can not use a connectionstring on a given TCP port number or a named database instance. This can be solved by a SQL Alias. Usually this is created using SQL Server Configuration Manager, but a installation of SQL Server Client Connectivity is actually not required.
A SQL Alias can be created as a Connectivity alias using the tool"cliconfg.exe", that is a part of Windows Data Access Components (WDAC, formerly MDAC).

Builds

The "cliconfg.exe" comes in two builds

• 64-bit: %windir%\System32\cliconfg.exe
• 32-bit: %windir%\SysWOW64\cliconfg.exe

when you are on a 64-bit (x64) Windows. On a 64-bit Windows the 64-bit "cliconfg.exe" is default.
Usually you have to be local administrator to add a new alias.
Any SQL Alias if it is 32-bit or 64-bit created by "cliconfg.exe" can be crated and edited in SQL Server Configuration Manager if the SQL Client Connectivity is installed.

Registration

The registration of a SQL Alias is placed in the registry:

• 64-bit: \\HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\
• 32-bit: \\HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\

Like any other entry in the registry they can be managed by the native API or by Windows Management Instrumentation (WMI) using a COM tool like Windows Scripting Host (WSH) by JScript or using a .NET tool like PowerShell.
The PowerShell cmdlet New-ItemProperty can be used like this:
[String]$RegPath = 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
[String]$RegName = 'SPSQL'
[String]$RegData = 'DBMSSOCN,DBSERVER.bacon.lan,54321'

New-ItemProperty -Path $RegPath -Name $RegName -PropertyType String -Value $RegData
DBMSSOCN is the TCP/IP network libary.

Check SQL Alias

Like any other connection a SQL alias can be checked by a Universal Data Link (UDL) file.

1. Create a empty text file and rename it “something.udl” to the filetype UDL.
2. Edit the properties of the UDL definition. This can be done by doubleclick on the file in Windows Explorer.
3. Pick a provider, e.g. “Microsoft OLE DB Provider for SQL Server”
4. Enter the name of the SQL Alias as servername and security information.
5. Click “Test”.

WARNING! The content of a UDL file is clear text. If you enter username and password for a SQL Login, this will be readable.

Reference

MSDN Blogs > SQL Protocols: “Connection Alias”.
Molson Online: “Windows 7 64 bit and SQL Server cliconfg”.
MSDN Blogs > SQL Protocols: “Configure and manage SQL Server 2005 from your application using WMI”.
MSDN Blogs > SQL Protocols: “Configure Aliases Programmatically Using WMI”.

History

2011-04-23 Original blog entry.
2013-04-13 PowerShell cmdlet New-ItemProperty added.