March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

Categories

March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

TCP IP Port in SQL Server 2012 and Firewall

TCP IP Port in SQL Server 2012

As an SQL Server Administrator you might need to find SQL Server TCP IP port configured for your SQL Server Instance. There are many reason to find it but most famous reasons is to give access to developer and set firewall exception if there is any. Generally 1433 is the default port but you may configure different ports if you have more than once SQL Server instance running or may be due  to security reason you would like to change the default port of SQL Server.

There are few different ways to find the SQL Server 2012 TCP/IP port  out of which I am going to mention few of the most used ways to reach TCP/IP port of SQL Server 2012.

1.)  Read TCP IP Port of SQL Server 2012 from Registry:

This is one of the most used ways. Either go to registry to find TCP IP port or execute extended stored procedure in SQL Server itself to read registry.

 

DECLARE @tcp_port nvarchar(10)
EXEC XP_REGREAD
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll’,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT
SELECT @tcp_port AS TCPIP_Port
GO

Read TCP IP Port From SQL Server 2012 Configuration Manager.

Open “SQL Server Configuration Manager” from Start Menu-> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager. Refer following screen shot for more details:

 1SQLServerConfigurationManager

 

 

Now move towards “SQL Server Network Configuration”-> Protocol for “SQLServerInstance”->TCP/IP property.

 
2SQLServerConfigurationManagerTCPIPProperty 3TCPIPPropertyPage

 

 

To assign a TCP/IP port number to the SQL Server Database Engine

  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

  2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

  3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

  4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.

  5. In the console pane, click SQL Server Services.

  6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.

After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

  • Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.

  • Create an alias on the client, specifying the port number.

  • Program the client to connect using a custom connection string.

Firewall

 To open a port in the Windows firewall for TCP access

  1. On the Start menu, click Run, type WF.msc, and then click OK.

  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.

  3. In the Rule Type dialog box, select Port, and then click Next.

  4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.

  5. In the Action dialog box, select Allow the connection, and then click Next.

  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.

  7. In the Name dialog box, type a name and description for this rule, and then click Finish.

To open access to SQL Server when using dynamic ports

  1. On the Start menu, click Run, type WF.msc, and then click OK.

  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.

  3. In the Rule Type dialog box, select Program, and then click Next.

  4. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.

  5. In the Action dialog box, select Allow the connection, and then click Next.

  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.

  7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>