How does a SQL Server instance alias work?

Question

I'm trying to understand how a SQL Server alias works, but the documentation is lacking to say the least.

Most blogs/videos/documents I can find state that all you need to do is go to SQL Server Configuration Manager and add an "Alias" under SQL Native Client Configuration / SQL Native Client Configuration (32 Bit), which points to the same port as the instance you want to create an alias for. And that's it, done, nothing else required.

Now unless the SQL Server is in a domain, and allowed to register its own SPNs and only Kerberos is being used (and assuming that an alias gets its SPN automatically registered, which I can't find any documentation to either confirm or refute this statement)... I can't really see how this is enough!

Assuming that either the SQL box is not on the domain, or that SPNs are not registered, if I create an alias called SQLTEST for SQL1 on port 1433 and, from a different computer, try to access SQLTEST bearing in mind the lack of SPNs the first thing the client will do is try to resolve SQLTEST to an IP address - yet there's no DNS record for SQLTEST.

So how does this actually work? What are the true requirements to get aliases working?

How does creating an alias differ from just creating 'A' or 'CName' records in DNS without actually creating an alias in SQL itself?

Answer 1

1) You don't need to define the alias on the SQL Server itself, unless you need a loopback for some reason.

2) The client doing the connecting does need the alias defined on it. Make sure you define it in both the 32bit and 64bit version of cliconfg to make sure you don't miss anything there.

• C:\Windows\system32\cliconfg.exe
• c:\WINDOWS\SysWOW64\cliconfg.exe

3) The Alias defined in cliconfg on the client is different from DNS in that you can specify a port instead of just a redirect. You could combine the two for maximum flexibility. So you could have cliconfg on the client using a DNS record to point to the correct host, but cliconfg specify a port number. Think of it as a etc\hosts file that only database drivers talk to. Effectively:

• Application - Connection string to My_SQL_Alias
• CLICONFG - My_SQL_Alias == MySQLHost:1564
• DNS - MySQLHost == MyRealSQLHostName
• FinalConnection == MyRealSQLHostName:1564

4) If part of a domain, you should be able to specify these things through group policy.

WHY USE IT??

I've not found many use cases in my career for setting these up. Really, if you need to hide the fact that you are connecting to an instance rather than the default instance then that's the only thing that really springs to mind. But the challenges with spreading this information out prohibits a lot of these use cases.

Answer 2

When you connect locally (From same box) Alias created on the SQL Server works fine. However, when you try connecting to the alias you created from a different box (same domain) you will not be able to connect.This is because alias does not resolve. You will need the DNS record for it to exist.

Steps involved to make your SQL ALias work from network

You can manually create alias in the box from where you are tryinmg to connect. I create same alias name using cliconfg.exe and use it to connect. This is because the SQL Server configuration manager does not create a DNS for your alias in the domain. So remote boxes need to have similar alias defined to make the connection successful.

For reference

Similar question