Как подключиться к sql server с другого компьютера

How to enable remote connections to SQL Server

So you have a VM or a remote server, that you have installed SQL Server, but you also want to connect to SQL Server using SQL Server Management Studio on your local system, but gives you this error.

This is because you haven’t configured it to allows inbound connections from firewall or from SQL Server itself, could be both.

Prerequisites

1. Remote server/VM must have SQL Server Management Studio and SQL Server Configuration Manager Installed.
2. Remote SQL Server must have a user that have read and write privileges (Here I’m using sa user)

All set, let’s start

Connect to the server/VM using Remote Desktop Connection and open up SQL Server Management Stdio on remote server, then connect to the SQL Server instance.

Now right click on the server and go to Properties.

On the Connections page under Remote server connections, make sure that the Allow remote connections to this server is checked.

Now open SQL Server Configuration Manager.

Visit Protocols for <instance name>, in my case Protocols for MSSQLSERVER under SQL Server Network Configuration node, go to TCP/IP and make sure the ‘Status’ is set to Enabled. If not, right click and select Enable.

After that, again right click and select Properties of TCP/IP protocol.

Goto IP Addresses tab and go down until you see IPALL section. Make sure the TCP Port is set to 1433. If not set it to 1433.

This warning will appear, so we need to restart the server. we can do it by using SQL Server Configuration manager.

Locate SQL Server under SQL Server Services, right click and select Restart.

Now lets configure the firewall.

Open up Windows Firewall.

Go to Inbound Rules and select New Rule.

Add the following rule. Follow images if unclear.

Rule Type: Port

Protocol and Ports: TCP, 1433

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLTCP1433

Click finish. We need to add another rule for UDP connections as well.

Rule Type: Port

Protocol and Ports: UDP, 1434

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLUDP1434

Let’ make SQL Server and SQL Server Browser accessible through firewall.

Rule Type: Program

Program: C:\Program Files\Microsoft SQL Server\<Server version>.<Instance name>\MSSQL\Binn\sqlservr.exe

e.g. C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn

Action: Allow the connection

Profile: Domain, Private, Public

Name: SQLSERVER

Как подключится к MS SQL Express Server с другого компьютера?

Добрый день.
Вопрос следующий: как подключится к MS SQL Express Server 2014, развернутой на домашнем компьютере в Windows 8 с другого компьютера, который подключен к домашней группе и с установленной на нем Windows 10? И просмотр существующих баз данных осуществлять через MS SQL Server Managment Studio 2014.

Раньше не имел с этим делом, так как подключался в офисе, где есть централизованный сервер и все компьютеры находятся в домене. Надеюсь на вашу помощь: статьи, ссылки и другое.

How to Configure Remote Access and Connect to a Remote SQL Server 2019?

In this article, you will learn to configure remote access on a SQL Server and connect to a remote SQL Server.

Steps to Configure Remote Access on a SQL Server

1. Open Microsoft SQL Server Management Studio by clicking on the Windows icon.

2. Then you will be prompted to connect to the server, here click on Connect.

3. To enable remote connection on SQL Server, right – click on the server and click on the Properties option. In the Server Properties dialog under the Connections tab, tick the Allow remote connections to this server option and click on OK.

4. Click on the Windows icon on the desktop and click on Microsoft SQL Server 2019. Then click on the drop-down and select the SQL Server Configuration Manager.

5. Then from the SQL Server Network Configuration select Protocols for your server. Ensure that TCP/IP protocol is enabled, if it’s not then right-click on TCP/IP and select the Properties option.

6. Under the Protocol tab, click on the drop-down for Enabled and select Yes. Then go to the IP Addresses tab and scroll down to IPAII.

7. If the TCP Dynamic Ports dialog box displays 0, it indicates that the Database Engine is listening on dynamic ports, delete the 0 and leave the TCP Dynamic Ports blank and set the TCP Port to 1433 and click on Apply. SQL Server uses port 1433 as the default instance.

8. You will be prompted for confirmation, click on OK. Then again click on OK on the TCP/IP Properties.

9. From the left pane of SQL Server Configuration Manager, click SQL Server Services and right-click SQL Server, and click Restart.

Steps to Configure a Windows Firewall for Database Engine Access

For adding a firewall exception for the 1433 port, follow the below steps:

1. Click on Windows icon. Then click on Windows Administrative Tools drop-down, scroll-down and select the Windows Firewall with Advanced Security option.

2. In the Windows Firewall with Advanced Security dialog, click on the Inbound Rules option in the left panel and select the New Rule from the Actions panel. This will open the New Inbound Rule Wizard.

3. In the New Inbound Rule Wizard, under Rule Type, click on the Port option and click Next.

4. In the Protocol and Ports window, mention the protocols and ports to which a rule applies. Select the TCP option under Does this rule apply to TCP or UDP? and in the Specific local ports, text box enter the 1433 port, and click Next.

5. In the Action window, select the Allow the connection option to specify the action to be taken when a connection matches the conditions specified in the rule and click on Next.

6. In the Profile window, specify the profile for which the rule applies. Here we have selected Domain. Then click on Next.

7. In the Name window, enter the name of the created rule and click on Finish.

8. You can now see the created rule in the list of inbound rules.

9. Now to connect to a remote server using the Windows Authentication, go to Server Properties and under the Security tab set the Server authentication to SQL Server and Windows Authentication mode and click on OK.

10. Then you will be prompted for restarting the server or else the changes won’t be reflected. Here, click on OK.

In this way, you can configure remote access and connect to a remote SQL server 2019.

Setting up Management Studio for remote SQL Server

Most of us, developers, love to write SQL code on our local machines not only because they’re executed faster, but also because we have a lot more control over the data as it is used only by us and is not team dependent. This was the case in my team until recently when the policy has changed and the new policy requires us to develop using a centralized database that was being hosted on one of the on-premise servers rather than using local SQL Servers for each individual developers.

There are both advantages and disadvantages to this approach. You can free up some space and RAM from your local machine by disabling the SQL Server Services, which can be an advantage for some. However, you’d need to connect to the server now and write queries keeping in mind that other developers are also using the same database. So, one should be aware of making any changes to the data.

The important steps to allow remote connections to an instance can be divided into the following parts, which are discussed in detail below.

1. Allowing Remote Connections
2. Add Remote User to the database
3. Configuring the SQL Server Instance
4. Configuring Windows Firewall
5. Fetch the Connection Details
6. Connecting to the remote machine

This article describes the steps on how to configure remote access on a SQL Server instance and connect to it from a developer’s machine using SSMS.

Connect to a Remote SQL Server

In order to be able to connect to the remote database, we need to configure it to allow remote machines to connect to it.

Allowing Remote Connections

1. Right-click the on the SQL Server instance name and select Properties.
2. Select Connections on the left-hand pane.
3. Under Remote Server Connections, check the box against «Allow remote connections to this server«.
4. Leave the default value for the Remote query timeout to 600.
5. Click OK.

The SQL Server is not configured to be able to allow remote machines to connect to this instance.

Add a Remote User to the database

Once the SQL Server is configured, we need to allow decide users should be able to connect and access the database objects. This is done by creating a SQL Login and then assigning specific database roles to those users.

In this case, since all the machines are in the same domain, we will proceed with the Windows Authentication mode.

1. Connect to the SQL Server instance as a server admin.
2. Expand Security and right-click on Logins.
3. Select New Login.
4. The Login — New dialog box appears.
5. On the General page, click Search.
6. The Search User, Service Account or Group dialog box appears.
7. Type the username in the object name text box and click Check Names.
8. This should automatically fetch the user details if there are any users with the same username.
9. Keep authentication mode as Windows Authentication.
10. Now, select Server Roles on the left-hand Page and select the checkbox against Public.
11. Navigate to User Mappings, and select the database on which the user needs access to.
12. We can assign any specific role as security demands. In this case, I’m assigning as db_owner.
13. On the Status page, select Permission as Grant and Login as Enabled and click OK.
14. The new login will be visible under the logins in the SQL Server Object Explorer.

Configuring the SQL Server Instance

Now that the database instance is configured for remote connections, we need to allow remote traffic to be allowed on the server. This can be done by following the steps below:

1. Select SQL Server 2016 Configuration Manager from the Start.
2. Navigate to Protocols for <<YOUR SERVER NAME>> under SQL Server Network Configuration on the left-hand pane.
3. Make sure that the TCP/IP Protocol Name is Enabled.

Once the TCP/IP Protocol is Enabled, we need to configure the port on which the SQL Server will allow inbound traffic.

1. Right-click on TCP/IP Protocol Name and select the Property option.
2. Navigate to the IP Addresses tab and scroll down to the section named «IPAII«.
3. If the TCP Dynamic Ports is set to 0 (indicates the Database Engine is listening on dynamic ports), then remove the 0 and set it to blank.
4. Update the value for TCP Port to 1434. This is the default port that is being used by the SQL Server Database Engine and click OK.
5. A warning might be displayed which will prompt to restart the service.

Now that the IP Ports are configured, we need to restart the SQL Server service, which will run the Database Engine on the ports as specified earlier.

1. Select SQL Server Services.
2. On the right-hand panel, right-click the SQL Server (Instance Name) and select Restart.
3. The service will be restarted.

Configuring Windows Firewall

Once the database instance is configured to accept remote connections, we need to add an exception in the Windows Firewall for the port (1434) to allow TCP/IP traffic on this port.

1. Click Start and select Administrative Tools.
2. In the Administrative Tools window, double-click Windows Firewall with Advanced Security.
3. The Windows Firewall with Advanced Security window opens.
4. Click Inbound Rules on the left-hand panel.
5. On the right-hand panel, click New Rule under Actions.
6. The New Inbound Rule Wizards starts.
7. Select the Port option under Rule Type and then click on Next.
8. Under Protocols and Ports, select TCP and Specified local ports as «1434» (since we want to allow traffic on this port).
9. Click Next.
10. Select Allow the connection under Action and click on Next.
11. Select all the three options under Profile (This depends on whom do you want to allow access to the database instance).
12. Click Next.
13. The final step is to provide a Name to this new rule.
14. The Description is not mandatory, so you may choose not to fill it in.
15. Finally, click Finish.
16. The new rule should now be visible in the Inbound Rules section.

Fetch the Connection Details

Now that everything has been set up properly to allow inbound traffic over the firewall, we are ready to connect to this machine from any other machine in the network. For this, we need to get the correct IP Address of the machine and the Instance Name of the database (only in case of a named instance).

1. Open Command Prompt and type ipconfig.
2. Copy the IPv4 Address. In this case, it is «10.13.106.38«.
3. Also, open SQL Server Configuration Manager.
4. Select SQL Server Services on the left-hand panel and copy the name of the instance of the database. This is only required in case the database is running on a named instance.
5. In this case, the name of the instance is SQLTEST01.

Connecting to the remote machine

Once the remote server is configured, now we should be able to connect to the SQL Database Instance from any other PCs.

1. Open SQL Server Management Studio.
2. Provide the Server name in the format <<REMOTE MACHINE IP>>\<<INSTANCE NAME>>.
3. In this case, it is 10.13.106.38\SQLTEST01.
4. Since I’m in the same domain as the remote server, I’d be using Windows Authentication.
5. Click Connect.
6. The remote database instance should be now accessible from the local machines.
7. Now you should be able to browse all the databases that the user has been granted access with.

Take Away

This article describes how to set up a remote server to allow other users to connect to a named instance of the SQL Server Database Engine.