Connecting To Another Computer
To enhance security, the Database Engine of SQL Server Developer, Express, and Evaluation editions cannot be accessed from another computer when initially installed. This lesson shows you how to enable the protocols, configure the ports, and configure the Windows Firewall for connecting from other computers.
This lesson contains the following tasks:
Enabling Protocols
Configuring a Fixed Port
Opening Ports in the Firewall
Connecting to the Database Engine from Another Computer
Connecting Using the SQL Server Browser Service
Enabling Protocols
To enhance security, SQL Server Express, Developer, and Evaluation install with only limited network connectivity. Connections to the Database Engine can be made from tools that are running on the same computer, but not from other computers. If you are planning to do your development work on the same computer as the Database Engine, you do not have to enable additional protocols. Management Studio will connect to the Database Engine by using the shared memory protocol. This protocol is already enabled.
If you plan to connect to the Database Engine from another computer, you must enable a protocol, such as TCP/IP.
How to enable TCP/IP connections from another computer
On the Start menu, point to All Programs, point to Microsoft SQL Server 2017, point to Configuration Tools, and then click SQL Server Configuration Manager. 49172
Note
You might have both 32 bit and 64 bit options available.
Note
Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows. The file name contains a number representing the version number of the SQL Server. To open Configuration Manager from the Run command, here are the paths to the last four versions when Windows is installed on the C drive.
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 (11.x) C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for .
The default instance (an unnamed instance) is listed as MSSQLSERVER. If you installed a named instance, the name you provided is listed. SQL Server 2012 Express installs as SQLEXPRESS, unless you changed the name during setup.
In the list of protocols, right-click the protocol you want to enable (TCP/IP), and then click Enable.
Note
You must restart the SQL Server service after you make changes to network protocols; however, this is completed in the next task.
Configuring a Fixed Port
To enhance security, Windows Server 2008, Windows Vista, and Windows 7 all turn on the Windows Firewall. When you want to connect to this instance from another computer, you must open a communication port in the firewall. The default instance of the Database Engine listens on port 1433; therefore, you do not have to configure a fixed port. However, named instances including SQL Server Express listen on dynamic ports. Before you can open a port in the firewall, you must first configure the Database Engine to listen on a specific port known as a fixed port or a static port; otherwise, the Database Engine might listen on a different port each time it is started. For more information about firewalls, the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access.
Note
Port number assignments are managed by the Internet Assigned Numbers Authority and are listed at https://www.iana.org. Port numbers should be assigned from numbers 49152 through 65535.
Configure SQL Server to listen on a specific port
In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.
In the right pane, double-click TCP/IP.
In the TCP/IP Properties dialog box, click the IP Addresses tab.
In the TCP Port box of the IPAll section, type an available port number. For this tutorial, we will use 49172.
Click OK to close the dialog box, and click OK to the warning that the service must be restarted.
In the left pane, click SQL Server Services.
In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.
Opening Ports in the Firewall
Firewall systems help prevent unauthorized access to computer resources. To connect to SQL Server from another computer when a firewall is on, you must open a port in the firewall.
Important Opening ports in your firewall can leave your server exposed to malicious attacks. Be sure to understand firewall systems before opening ports. For more information, see Security Considerations for a SQL Server Installation.
After you configure the Database Engine to use a fixed port, follow the following instructions to open that port in your Windows Firewall. (You do not have to configure a fixed port for the default instance, because it is already fixed on TCP port 1433.)
To open a port in the Windows firewall for TCP access (Windows 7)
On the Start menu, click Run, type WF.msc, and then click OK.
In Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
In the Rule Type dialog box, select Port, and then click Next.
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.
Type 1433 for the default instance.
Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Click Next.
In the Action dialog box, select Allow the connection, and then click Next.
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.
In the Name dialog box, type a name and description for this rule, and then click Finish.
For more information about configuring the firewall including instructions for Windows Vista, see Configure a Windows Firewall for Database Engine Access. For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access.
Connecting to the Database Engine from Another Computer
Now that you have configured the Database Engine to listen on a fixed port, and have opened that port in the firewall, you can connect to SQL Server from another computer.
When the SQL Server Browser service is running on the server computer, and when the firewall has opened UDP port 1434, the connection can be made by using the computer name and instance name. To enhance security, our example does not use the SQL Server Browser service.
To connect to the Database Engine from another computer
On a second computer that contains the SQL Server client tools, log in with an account authorized to connect to
SQL Server, and open Management Studio.
In the Connect to Server dialog box, confirm Database Engine in the Server type box.
In the Server name box, type tcp: to specify the protocol, followed by the computer name, a comma, and the port number. To connect to the default instance, the port 1433 is implied and can be omitted; therefore, type tcp:
. In our example for a named instance, type tcp:
,49172.
Note If you omit tcp: from the Server name box, then the client will attempt all protocols that are enabled, in the order specified in the client configuration. In the Authentication box, confirm Windows Authentication, and then click Connect.
Connecting Using the SQL Server Browser Service
The SQL Server Browser service listens for incoming requests for SQL Server resources and provides information about SQL Server instances installed on the computer. When the SQL Server Browser service is running, users can connect to named instances by providing the computer name and instance name, instead of the computer name and port number. Because SQL Server Browser receives unauthenticated UDP requests, it is not always turned on during setup. For a description of the service and an explanation of when it is turned on, see SQL Server Browser Service (Database Engine and SSAS).
To use the SQL Server Browser, you must follow the same steps as before and open UDP port 1434 in the firewall.
This concludes this brief tutorial on basic connectivity.
***********************************************************************
This lesson contains the following tasks:
Enabling Protocols
Configuring a Fixed Port
Opening Ports in the Firewall
Connecting to the Database Engine from Another Computer
Connecting Using the SQL Server Browser Service
Enabling Protocols
To enhance security, SQL Server Express, Developer, and Evaluation install with only limited network connectivity. Connections to the Database Engine can be made from tools that are running on the same computer, but not from other computers. If you are planning to do your development work on the same computer as the Database Engine, you do not have to enable additional protocols. Management Studio will connect to the Database Engine by using the shared memory protocol. This protocol is already enabled.
If you plan to connect to the Database Engine from another computer, you must enable a protocol, such as TCP/IP.
How to enable TCP/IP connections from another computer
On the Start menu, point to All Programs, point to Microsoft SQL Server 2017, point to Configuration Tools, and then click SQL Server Configuration Manager. 49172
Note
You might have both 32 bit and 64 bit options available.
Note
Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows. The file name contains a number representing the version number of the SQL Server. To open Configuration Manager from the Run command, here are the paths to the last four versions when Windows is installed on the C drive.
SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 (11.x) C:\Windows\SysWOW64\SQLServerManager11.msc
SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for .
The default instance (an unnamed instance) is listed as MSSQLSERVER. If you installed a named instance, the name you provided is listed. SQL Server 2012 Express installs as SQLEXPRESS, unless you changed the name during setup.
In the list of protocols, right-click the protocol you want to enable (TCP/IP), and then click Enable.
Note
You must restart the SQL Server service after you make changes to network protocols; however, this is completed in the next task.
Configuring a Fixed Port
To enhance security, Windows Server 2008, Windows Vista, and Windows 7 all turn on the Windows Firewall. When you want to connect to this instance from another computer, you must open a communication port in the firewall. The default instance of the Database Engine listens on port 1433; therefore, you do not have to configure a fixed port. However, named instances including SQL Server Express listen on dynamic ports. Before you can open a port in the firewall, you must first configure the Database Engine to listen on a specific port known as a fixed port or a static port; otherwise, the Database Engine might listen on a different port each time it is started. For more information about firewalls, the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access.
Note
Port number assignments are managed by the Internet Assigned Numbers Authority and are listed at https://www.iana.org. Port numbers should be assigned from numbers 49152 through 65535.
Configure SQL Server to listen on a specific port
In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.
In the right pane, double-click TCP/IP.
In the TCP/IP Properties dialog box, click the IP Addresses tab.
In the TCP Port box of the IPAll section, type an available port number. For this tutorial, we will use 49172.
Click OK to close the dialog box, and click OK to the warning that the service must be restarted.
In the left pane, click SQL Server Services.
In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.
Opening Ports in the Firewall
Firewall systems help prevent unauthorized access to computer resources. To connect to SQL Server from another computer when a firewall is on, you must open a port in the firewall.
Important Opening ports in your firewall can leave your server exposed to malicious attacks. Be sure to understand firewall systems before opening ports. For more information, see Security Considerations for a SQL Server Installation.
After you configure the Database Engine to use a fixed port, follow the following instructions to open that port in your Windows Firewall. (You do not have to configure a fixed port for the default instance, because it is already fixed on TCP port 1433.)
To open a port in the Windows firewall for TCP access (Windows 7)
On the Start menu, click Run, type WF.msc, and then click OK.
In Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
In the Rule Type dialog box, select Port, and then click Next.
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.
Type 1433 for the default instance.
Type 49172 if you are configuring a named instance and configured a fixed port in the previous task. Click Next.
In the Action dialog box, select Allow the connection, and then click Next.
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.
In the Name dialog box, type a name and description for this rule, and then click Finish.
For more information about configuring the firewall including instructions for Windows Vista, see Configure a Windows Firewall for Database Engine Access. For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access.
Connecting to the Database Engine from Another Computer
Now that you have configured the Database Engine to listen on a fixed port, and have opened that port in the firewall, you can connect to SQL Server from another computer.
When the SQL Server Browser service is running on the server computer, and when the firewall has opened UDP port 1434, the connection can be made by using the computer name and instance name. To enhance security, our example does not use the SQL Server Browser service.
To connect to the Database Engine from another computer
On a second computer that contains the SQL Server client tools, log in with an account authorized to connect to
SQL Server, and open Management Studio.
In the Connect to Server dialog box, confirm Database Engine in the Server type box.
In the Server name box, type tcp: to specify the protocol, followed by the computer name, a comma, and the port number. To connect to the default instance, the port 1433 is implied and can be omitted; therefore, type tcp:
. In our example for a named instance, type tcp:
Note If you omit tcp: from the Server name box, then the client will attempt all protocols that are enabled, in the order specified in the client configuration. In the Authentication box, confirm Windows Authentication, and then click Connect.
Connecting Using the SQL Server Browser Service
The SQL Server Browser service listens for incoming requests for SQL Server resources and provides information about SQL Server instances installed on the computer. When the SQL Server Browser service is running, users can connect to named instances by providing the computer name and instance name, instead of the computer name and port number. Because SQL Server Browser receives unauthenticated UDP requests, it is not always turned on during setup. For a description of the service and an explanation of when it is turned on, see SQL Server Browser Service (Database Engine and SSAS).
To use the SQL Server Browser, you must follow the same steps as before and open UDP port 1434 in the firewall.
This concludes this brief tutorial on basic connectivity.
***********************************************************************
No results found