Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
I have a Microsoft SQL Server database on a Windows VM currently serving data over over server.fq.d.n,<port-number>. Users are able to connect to this database just fine using this <fqdn>,<port> address, but some of them want to connect with an alias that is just an fqdn without a port number alias.fq.d.n.
How would I go about doing this?
My database instance is named xyz-services\foo.
MS SQL provides the SQL Server Browser Service, that allows clients to query the server and ask for the port of the SQL Server instance that hosts the database.
This service is enabled by default when an SQL server is set up, so it should be available to your clients. If your client's cant reach it check if the service is running and your firewall.
This should be used anyway, since the port for the SQL Server instances is by default not fixed. It is determined at startup and if you have multiple SQL Server instances on one host it is not guaranteed that the specific SQL Server instance will always have the same port (as long as you don't set it to a fixed value).
As far as i can tell you cannot omit the port number on TCP connection strings. So when the connection should be made using a FQDN you have to supply the port number.
You can use a connection alias on the client.
https://blogs.msdn.microsoft.com/sql_protocols/2007/01/07/connection-alias/
This is configured through either SQL Server Configuration Manager, or directly in the registry. The article mentions c:\windows\system32\cliconfig.exe which doesn't exist on my windows 10 laptop.
