Alias on Named Instance with non default port

Question

Hello,

I have created a sql alias on a named instance that has a non default port.

I can connect to the alias from the server that hosts the instance but i cannot connect to the alias remotely unless i specify the port i.e SQLALIAS,portnumber.

Is it possible to connect remotely using only the SQL Alias ? i dont want to share the port number with application teams.

Also, is it possible to do this without having to use cliconfg.exe on client machines ? there are over 300 client machines and it wont be feasible.

thanks

Answer 1

You can create an alias on the machine where SQL Server is running; but the alias works only on that machine.

It's not a global alias, it's a local setting and so you have to create on every client machine.

SQL Server-Browser resolves the instance name to the used IP port, so if the browser is running, the clients can connect to using the instance name instead of the IP port, e.g.

ServerName\InstanceName

Answer 2

thank you for your reply,

is it possible to create a global alias without creating on every client machine ?

the browser service is running...

Answer 3

There is no way to create a global SQL Server alias and if the browser is running, the client don't need to know th IP port, only the instance name.

Answer 4

The browser is running, and I have created the alias on the server, but i still cant connect from the client by issuing only alias name.

It will work from client with alias,portnumber but not alias name.

Im reading the following link and it doesnt mention that i need to create a client alias

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-ver16

"This article describes how to create or delete a server alias in SQL Server by using SQL Server Configuration Manager, for SQL Server 2019 (15.x) and previous versions.

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Aliases can be used with any client application. By creating server aliases, your client computer can connect to multiple servers using different network protocols, without having to specify the protocol and connection details for each one. In addition, you can also have different network protocols enabled all the time, even if you only need to use them occasionally. If you have configured the server to listen on a non-default port number or named pipe, and you have disabled the SQL Server Browser service, create an alias that specifies the new port number or named pipe."

Answer 5

Hi @Paul kavanagh

This link describes some ways SQL Server instance aliases work, perhaps you can use them as a reference: https://dba.stackexchange.com/questions/233805/how-does-a-sql-server-instance-alias-work.

Best regards,

Aniya