Original article is here:

http://sqladm.blogspot.ru/2011/04/sql-alias.html

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

IMPORTANT: Since Site Admin is 32 bit applications we need to create 32 bit Alias.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 created 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.