Как разрешить удаленные соединения на sql сервере

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.

Для системного администратора

Включение удаленного доступа к SQL Server 2008 Express

По умолчанию после установки SQL Server 2008 Express не разрешает удаленное подключение. В данной статье мы рассмотрим необходимые шаги для включения возможности удаленного подключения.

При попытке подключения вы можете получить следующие сообщения об ошибках:

• “Cannot connect to SQL-Server-Instance-Name
  An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)”
• “Cannot connect to SQL-Server-Instance-Name
  An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)”
• “Cannot connect to SQL-Server-Instance-Name
  Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”

Для включения возможности удаленного подключения к SQL Server 2008 Express выполните следующие шаги:

1. Запустите службу SQL Server Browser. SQL Server Browser слушает входящие запросы к ресурсам  Microsoft SQL Server  и предоставляет информацию о существующих экземплярах SQL Server на компьютере.
2. Включить протокол TCP/IP в SQL Server 2008 Express.
3. (Дополнительно) Изменить способ аутентификации на SQL Server and Windows Authentication. По умолчанию SQL Server 2008 Express работает только в режиме аутентификации Windows, поэтому вы можете подключиться только под пользователем Windows.

Note: В SQL Server 2008 Express нет SQL Server Surface Area Configuration, поэтому настраивать все необходимо через SQL Server Configuration Manager.

Пошаговые действия:

1. Откройте SQL Server Configuration Manager. Запустите Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools ->SQL Server Configuration Manager.
   
2. В SQL Server Configuration Manager выберите SQL Server Services в левом меню. Если служба SQL Server Browser запущена, перейдите к шагу 6.
   
3. Зайдите в свойства службы SQL Server Browser и установите аккаунт запуска. В данном примере это аккаунт Local Service.
   
4. Перейдите на вкладку Service и установите режим запуска Automatic. В противном случае служба не будет автоматически стартовать после перезагрузки компьютера. Нажмите OK для применения изменений.
   
5. Выберите службу и запустите её.
   
6. Зайдите в SQL Server Network Configuration -> Protocols for SQLEXPRESS. Как вы видите протокол TCP/IP отключен.
   
7. Нажмите на него правой кнопкой и выберите Enable.
   
8. Появится предупреждение что необходимо перезапустить SQL Service для применения изменений.
   
9. Перейдите опять на узел SQL Server Services. Выберите SQL Server (SQLEXPRESS) и перезапустите данную службу.
   
10. Откройте Microsoft SQL Server Management Studio и подключитесь к SQL Server 2008 Express.
    
11. Нажмите правой кнопкой на SQL Server и выберите Properties.
    
12. В свойствах сервера перейдите в раздел Security. Выберите режим SQL Server and Windows Authentication.
    
13. Опять появится предупреждение о необходимости перезапуска службы SQL.
    
14. Нажмите правой кнопкой на SQL сервере и выберите Restart.
    
15. На этом все. Теперь вы сможете удаленно подключиться к SQL Server 2008 Express.

Полезная информация

Хотите начать зарабатывать хорошо не работая по 8 часов на дядю. В этом вам может помочь Плеяна – качественная профессиональная косметика оптом по выгодным ценам. Моя жена после декретного отпуска решила не выходить на работу а заняться продажей косметики Плеяна. Дело очень быстро пошло очень хорошо, поэтому теперь она и не подумывает о другой работе.

Тем, кто пытается продать, купить и арендовать квартиру я рекомендую пользоваться Большим Сервером Недвижимости – просто отличный портал о недвижимости. На портале можно разместить бесплатные объявление, также сделан очень удобный поиск. Те, кто впервые связываются с продажей недвижимости, могут воспользоваться бесплатной онлайн-консультацией опытных специалистов. В целом все сделано для того, чтобы продажа квартир проходила быстро и выгодно для всех.

Этот пост January 23, 2012 at 11:40 am опубликовал molse в категории SQL. Желающие могут оформить RSS подписку на комменты. Both comments and trackbacks are currently closed.

2 комментов оставлено (Add 1 more)

Отличная статья! Помогла.
Прыгал с бубном пол-дня 🙂 пока не нашел эту заметку.
Спасибо. ( Express 2008 )

При установке на сервер 2008 Core, не доступна оснастка mmc, а значит, невозможно запустить Server Configuration Manager. Чтобы обойти эту проблему для SQL Server 2008 R2, нужно в ветке HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp изменить значение параметра Enabled с 0 на 1 и так же перезапустить службу SQL Server.
Для Express, я полагаю, действия аналогичные.

Как на sql server разрешить удаленные соединения

Configure the remote access Server Configuration Option

This article is about the remote access configuration option, which is a deprecated SQL Server to SQL Server communication feature.

This option affects servers that are added by using sp_addserver and sp_addlinkedserver. You should leave remote access enabled (the default) if you use linked servers.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

If you reached this page because you’re having trouble connecting to SQL Server, see one of the following articles instead:

Programmers may be interested in the following articles:

Manage remote access

The remote access configuration option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running.

The default value for the remote access option is 1 (enabled). This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server. To prevent local stored procedures from being run from a remote server or remote stored procedures from being run on the local server, set the option to 0 (disabled).

This setting doesn’t take effect until you restart SQL Server.

Remote access is required for the log shipping status report in SQL Server Management Studio (SSMS) to work and the LSAlert Job to complete appropriately.

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Using SQL Server Management Studio

To configure the remote access option

In Object Explorer, right-click a server and select Properties.

Select the Connections node.

Under Remote server connections, select or clear the Allow remote connections to this server check box.

Using Transact-SQL

To configure the remote access option

Connect to the Database Engine.

From the Standard bar, select New Query.

Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote access option to 0 .