Configuring and using SQL Server client aliases

438

In this post I cover SQL Server client aliases, what they are and when they’re useful, as well as how to configure, update, and use them.

I’ll be demoing ways of creating and managing aliases via dbatools, cliconfig, SQL Server Configuration Manager, and registry keys.

What are SQL Server client aliases?

SQL Server client aliases are alternate ways of connecting to a SQL Server instance by using another name.

The alias contains all the information required to connect to an existing instance, but exposes it to client applications with a different name redirecting the connection from its name to the existing instance completely transparent for the client application.

In essence, SQL Server client aliases are registry entries that reside on the same host as the client application and can redirect connections to another SQL Server instance than the one specified in the application’s connection parameters.

For example, a client alias named ProdVM01 which would hint at a default instance pointing to ProdVM01\SQL03 would redirect any application connections using ProdVM01 to ProdVM01\SQL03.

The anatomy of an alias

Before going any further, I think it’s helpful to get an understanding of the components that make up a client alias.

Depending on the tool used to create/view/manage the aliases, the terminology might differ a bit, so I’m adding all naming versions per component.

Component	Description
Name/Server alias/Alias	This is what the client app will have in its connection parameters when attempting to connect
Protocol/Network Library	The protocol that the alias will use, can be either TCP/IP or Named Pipes
Server/Server name/connection parameters	This is what the alias redirects the connections to, where the actual instance lives

A 32 bit client can only use a 32 bit alias, the same goes for a 64 bit client which will only be able to use the 64 bit alias.

As a best practice I create the same alias for both 32 and 64 bit architectures.

Use cases

Aliases are perfect for the following situations:

• software vendor insists that their application requires a default instance and cannot connect to named instances, but you’re environment only has named instances
• migrating the instance used by one or more application without having to keep the same host and instance names and to update the connection parameters – this is great for applications with: a. hard-coded connection parameters and/or b. the ones that don’t have centralized config files on an app server, but instead are on users’ workstations, in which case the aliases can be deployed as registry keys via Microsoft Endpoint Configuration Manager (formerly SCCM).

A few things to note:

• only applications using either the SQL Server Native Client or the Microsoft ODBC Driver for SQL Server make use of these aliases
• the new go-mssql-based sqlcmd (aka go-sqlcmd) does not work with these aliases – see the above point for an explanation 🙂
• in cases where only the hostname changes, but the instance name stays the same, it is easier to use a DNS level CNAME alias that maps the old hostname to the new hostname or IP

Creating and managing aliases

There are 4 ways (that I’m aware of) to create aliases.

I’ll go through each one of them in no specific order (besides alphabetical), outlining some potential pros and cons.

All of the following examples are of TCP/IP aliases because I have never had to use an alias created for the Named Pipe protocol.

Create SQL Server client aliases with dbatools

dbatools has a 3 commands dedicated to aliases:

• New-DbaClientAlias – for creating aliases
• Get-DbaClientAlias – to list existing aliases
• Remove-DbaClientAlias – to delete aliases

The command to create an alias using the TCP/IP protocol, if creating it on the local machine (the one where dbatools is executed from) is:

When creating it on a remote computer, let’s say you’re running the command from your laptop but the alias should be on an app server, the -ComputerName parameter will need to be added:

Neither -ServerName nor -Alias are limited to just named instances. You can provide just a hostname to either, in case of a default instance, or 'Hostname,PortNumber' or 'IP,PortNumber' (apostrophes included in order to not break the command) and mix them to match your requirements.

You can even create SQL Server client aliases for Azure SQL DB connections.

To view existing aliases you can use Get-DbaClientAlias to list them in PowerShell directly.

Or you can pipe Get-DbaClientAlias to Out-GridView to view them in a table.

Removing aliases with dbatools is also simple, using the Remove-DbaClientAlias command

Or, to view in an interactive table that lets you select which alias you want to delete.

Pros:

• dbatools makes it easy to deploy aliases to multiple app servers in one go, just provide a comma-separated list of VMs to the -ComputerName parameter
• easy to automate

Cons:

• you can’t use it to modify existing aliases, just delete and recreate them
• you need to be an admin on the hosts where you want to create/delete aliases, so it’s a no-go for mass deployments on workstations – although this should be an edge case and people should use a centralized app server
• if your security team isn’t ok with having PowerShell remoting enabled you can’t really use dbatools to manage aliases on multiple app servers without having to RDP into every one of them

Create SQL Server client aliases via registry keys

Since the aliases are just registry keys, it’s fairly easy to create them either directly through the Windows Registry Editor or as .reg files and then load them in the client’s registries.

The aliases are located in different sections of the registry depending on whether they’re 32 or 64 bit.

32 bit aliases reside in HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo

64 bit aliases reside in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

A few things to keep in mind when creating aliases via registry keys:

• the type of registry key is string value ( REG_SZ )
• the name of the key is the alias name, so if your app tries to connect to IP 192.168.1.170 and port 1080, the key name is 192.168.1.170,1080
• the value of the key is in this format: Protocol,HostOrIP,Port or Protocol,Host\Instance
• the protocol names are different in the key’s value: TCP/IP is DBMSSOCN; and Named Pipes is DBNMPNTW

Here’s how aliases look in Registry Editor

Using Registry Editor

Let’s say I want to add an alias on AppServer01 for an application that wants to connect via TCP/IP to a default instance that resided VM001 and, now, the new instance is a named one – WinSrv2k22\SQL2022.

In this case navigate to one of the two locations (the 32 bit or the 64 bit one) right click in the right half of Registry Editor and hover on New and then click on String Value.

Once the new key is there

I rename it to VM001 (right click on it and then click on Rename) and then I double click on it and fill in the following value for the key:

And click Ok to save the changes.

Afterwards I repeat the steps in the other location so that I have both 32 and 64 bit aliases in place.

Using .reg files

For the .reg files option I just need a template for each 32 and 64 bit version of the alias.

Note that when working with named instances in .reg files the backslash needs to be escaped by adding a second backslash, but it’s displayed with just one backslash in Registry Editor or any other tool that you want to use.

• 32 bit alias

• 64 bit alias

I can copy-paste these templates in Notepad, edit them as needed and then save them in C:\temp as .reg files – one file for 32 bit and another for 64 bit.

Once I have the files I can copy them in a folder on AppServer01, double click on them and confirm to import them into the VM’s registry or I can use Command Prompt opened as admin to import them.

Azure SQL DB example

• Azure SQL DB 32 bit alias

• Azure SQL DB 64 bit alias

Pros:

• does not require any extra tools or utilities
• adds portability in case of using .reg files – this means that you can create the alias keys and distribute them to users that might need them either directly or via Microsoft Endpoint Configuration Manager (SCCM)
• you can update existing aliases by editing the registry keys or the .reg files and then re-importing them
• you can export existing aliases (regardless which tool created them) right from Registry Editor
• a basic PowerShell script can be created to generate .reg files based on existing template files to make things easier

Cons:

• editing .reg files does require a bit more attention the first few times to get used to \\
• you need to RDP into the application’s host if you want to make the changes yourself and not push them via tools like Microsoft Endpoint Configuration Manager (or unless you have remote registry enabled; but why would anyone have that?)

Create SQL Server client aliases with the SQL Server Client Network Utility – cliconfig

The SQL Server Client Network Utility (aka cliconfg) is shipped with Windows and does not require any additional components to be installed.

It has one executable for 32 bit and one for 64 bit.

32 bit cliconfig executable path – C:\Windows\SysWOW64\cliconfg.exe

64 bit cliconfig executable path – C:\Windows\System32\cliconfg.exe

To start either of the executable I use Win+R, paste the full path there, and confirm the UAC prompt, or open PowerShell or Command Prompt as admin and open cliconfig from there.

Once opened, I navigate to the Alias tab where I can Add/Edit/Delete aliases as needed.

Pros:

• cliconfig is shipped with the OS and does not require any additional components
• the UI is pretty intuitive,e specially if you’re not used to working with registry keys and/or the command line

Cons:

• you need to RDP into the application host in order to use cliconfig
• the process is fully manual

Create SQL Server client aliases from the SQL Server Configuration Manager

On an application host where SQL Server is already installed, I can use SQL Server Configuration Manager to Add/Edit/Delete aliases.

SQL Server Configuration Manager also has dedicated ways of managing each 32 and 64 bit aliases.

The menu is pretty straight-forward

In the following example, connections to VM001 are redirected to 192.168.1.135 to an instance listening on port 1488.

Pros:

• the UI is intuitive and fairly easy to use
• comes built into SQL Server Configuration Manager

Cons:

• fully manual process
• requires SQL Server to be installed
• is no longer available in the SQL Server 2022 Configuration Manager

Using SQL Server client aliases

This is simple, for an existing application to use an alias you don’t have to change anything on the application side, just create the alias on the host from where the application connects to SQL Server.

For SSMS, sqlcmd* and PSBlitz there is nothing special required, I just provide the alias instead of the server name.

Here are a few examples:

SSMS

SSMS with aliases for Azure SQL DB

In this case you’ll need to pay attention to the following:

• specifying the database name in Options > Connection Properties (although in my tests it works just as well with leaving <default> selected)
• appending @YourServer.database.windows.net to the login name used to connect

Here’s my example:

And the actual connection test:

sqlcmd

sqlcmd with alias for Azure SQL DB

The same rule about appending the server name to the login applies here as well.

PSBlitz

The gotcha with sqlcmd

As mentioned earlier, the new go-mssql-based sqlcmd (aka go-sqlcmd) does not work with these aliases.

This is because it uses neither the SQL Server Native Client nor the Microsoft ODBC Driver for SQL Server.

On my test client VM I have both the native sqlcmd that’s part of the Microsoft SQL Command Line Utilities (MsSqlCmdLnUtils), as well as go-sqlcmd.

Using where (or where.exe in PowerShell), I can list the complete paths to all the available sqlcmd executable available on this VM.

The first one listed is the 64 bit one, this is the path that simply typing sqlcmd in cmd end executing it will resolve to.

The second one is the 32 bit sqlcmd, and the third one is the go-sqlcmd.

To use either of the last two executables, I have to call them using their full paths.

Since the 32 bit sqlcmd will use the appropriate architecture of the alias, and successfully connect, I’ll just go straight to the go-sqlcmd test.

The first attempt uses the actual instance name and connects successfully, the second attempt uses the alias and fails to connect because the go-mssql driver does not check for aliases.

Conclusion

SQL Server Client aliases are a great way to facilitate connectivity to new instances with different names for applications that might not allow directly changing their database connection info.

They’re easy to create and maintain and there are multiple solutions that facilitate both means of automation as well as portability for when aliases need to be deployed in a large environment.