0

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?

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.

0

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

3
  • The way the answer is written makes it sound like the solution revolving around cliconfg.exe requires that both sides have the alias setup (i.e., client and server), which is not correct -- the alias is just required on the side trying to connect to that alias name. Could you made an edit to reflect that and I'll mark it as the correct answer? – JonU Apr 3 '19 at 11:09
  • 1
    Can you remove "same alias" as that implies that the alias must exist on both server and client? The alias is only required on the connecting side, the SQL Server doesn't know of the existence of the alias at all. – JonU Apr 3 '19 at 12:02
  • This does not answer some of the questions, such as how is this different from using a DNS alias. I don't know for sure, but I assume that the SQL client alias basically instructs the SQL client to bypass DNS, and since it includes a port number, it will connect to the instance listening on that port. It also probably doesn't work for some SQL clients (such as JDBC, jTDS, etc.). I would advise to not use either SQL aliases or DNS aliases because someone may delete those things in the future thinking they aren't necessary. Just use the server name and port in all connection strings. – Tony Hinkle Apr 3 '19 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.