Accessing SQL Server on a Custom Port using SQL Server Management Studio

1. In the SSMS connection dialog, enter the server name followed by a comma and the port number. For example: server_name,1434 (replace 1434 with the desired port number).

Method 2: Creating an Alias (Recommended for Multiple Servers/Ports)

1. Open SQL Server Configuration Manager.
2. Navigate to SQL Server Network Configuration > Protocols for your SQL Server instance.
3. Right-click on TCP/IP and select "Aliases".
4. Click "Add" and provide a friendly alias name and the port number.

Now, in SSMS, you can simply use the alias name to connect to the server without specifying the port number each time.

Server Name=MyServer,1434;

In this example:

• Server Name is the name or IP address of your SQL Server machine.
• ,1434 specifies the port number (replace 1434 with your desired port).

Here's another example using an Alias (recommended approach for multiple servers/ports):

1. Alias Creation (on SQL Server machine):

• You won't have code for this step, but follow these instructions through SQL Server Configuration Manager:
  • Create an alias named "MySpecialServer" for the specific SQL Server instance listening on port 1435.

1. Connection String in SSMS:

Server Name=MySpecialServer;

• Server Name uses the alias name "MySpecialServer" which internally refers to the actual server and port (avoiding manual port specification each time).

This method involves changing the default port for all TCP/IP connections made by SQL Server tools on your machine, not just SSMS. It's generally not recommended as it can affect other applications that rely on the default port.

• Open SQL Server Native Client Configuration.
• Select Client Protocols.
• Right-click on TCP/IP and choose Properties.
• In the TCP/IP Properties window, navigate to the IP Addresses tab.
• Locate the TCP Port field and enter your desired port number (ensure it's available and not conflicting).
• Click Apply and OK to save changes.

Note: Remember to revert these changes if needed for other applications that rely on the default port.

Using a Connection String File (Advanced):

This method involves creating a separate file containing the connection string with the server name and port. SSMS can then import this file to establish the connection.

1. Create a text file (e.g., sqlserver_connection.txt).
2. Enter the connection string with the server name and desired port number:

Server Name=MyServer,1434;

1. In SSMS, navigate to File > Open > Recent Connections....
2. Click on Import Connection....
3. Select your connection string file (sqlserver_connection.txt) and click Open.

This approach allows you to manage connection details in a separate file and easily import them into SSMS when needed.