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
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\
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.- Create a empty text file and rename it “something.udl” to the filetype UDL.
- Edit the properties of the UDL definition. This can be done by doubleclick on the file in Windows Explorer.
- Pick a provider, e.g. “Microsoft OLE DB Provider for SQL Server”
- Enter the name of the SQL Alias as servername and security information.
- Click “Test”.
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.