0

Ok I'm trying to understand how a MSSQL Alias works but documentation is lacking to say the best.

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!

I mean 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?

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

1

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

  • But cliconfg.exe doesn't require the existence of an alias on the SQL Server - i.e., you can just create an alias on cliconfg.exe and connect from the client to the server without ever creating an alias on the server itself. So what's the point of aliases setup in SQL Server Configuration Manager? – JonU Apr 3 at 8:32
  • My bad,, i mixed it up. Edited. – Ramakant Dadhichi Apr 3 at 8:39
  • DNA record? Typo? :) – JonU Apr 3 at 8:51
  • I need a coffeee...;) – Ramakant Dadhichi Apr 3 at 8:51
  • 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 at 12:02
0

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.

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.