Подключение к удалённому SQL Server через SSMS

Чтобы подключить SQL Server Management Studio к SQL Server на хостинге, откройте Connect to Server и задайте четыре вещи: Server name - это хост и порт через запятую (db.example.net,14330 - двоеточие не сработает), Authentication - SQL Server Authentication, Login и Password - выданные вам учётные данные (на свежем сервере часто sa), а в настройке шифрования оставьте Mandatory и поставьте галочку Trust server certificate, если у сервера нет сертификата от доверенного центра. Это покрывает почти любое первое подключение. Остальная часть статьи объясняет каждое поле, опции, которые стоит поменять, что делать после входа и точные сообщения об ошибках для каждого варианта сбоя.

Что нужно перед началом#

Четыре сведения и одна программа:

• Хост - имя хоста или IP-адрес.
• Порт - по умолчанию у SQL Server это 1433, но серверы на хостинге часто используют другой, потому что многие серверы делят один адрес. Используйте выданный вам порт, а не порт по умолчанию.
• Логин - имя SQL-логина. На новом сервере это обычно sa, встроенный системный администратор.
• Пароль - для этого логина.
• SSMS - SQL Server Management Studio, бесплатный инструмент управления от Microsoft. Работает только на Windows.

Последние релизы SSMS (версия 20 и новее) ввели опции шифрования, описанные ниже. Если ваша копия старше, обновите её; старые версии используют старый драйвер с другими значениями по умолчанию и без исправлений за несколько лет. На macOS или Linux используйте Visual Studio Code с расширением MSSQL от Microsoft, DBeaver или консольный sqlcmd - у каждого поля из этой статьи там есть аналог. Azure Data Studio, который раньше был кроссплатформенным ответом, Microsoft вывела из эксплуатации, так что не начинайте в нём новую работу.

Прежде чем винить SSMS, проверьте, что порт доступен с вашей машины. В PowerShell:

PS> Test-NetConnection db.example.net -Port 14330

TcpTestSucceeded : True означает, что сетевой путь открыт, и всё, что падает после этого, - это настройка в SSMS или учётные данные. False означает, что мешает firewall - ваш, вашей сети или сервера, - и ничто в SSMS этого не исправит. Некоторые офисные и учебные сети блокируют исходящий трафик на необычные порты; попробуйте с другого подключения, чтобы исключить этот вариант.

Диалог Connect to Server, поле за полем#

Запятая - самая частая ошибка. Инструменты SQL Server унаследовали синтаксис host,port от исходных клиентских библиотек, и всё в стеке Microsoft - SSMS, sqlcmd, строки подключения ADO.NET, ODBC - его использует. Напишите db.example.net:14330, и клиент воспримет всю строку как имя хоста, не сможет её разрешить или попробует named pipes и выдаст сетевую ошибку, в которой ни слова о двоеточии. Форма с обратной косой чертой, host\INSTANCE, нужна для именованных экземпляров, которые находятся через службу SQL Server Browser на UDP 1434, а серверы на хостинге её не открывают; с явно указанным портом она вам не нужна.

Префикс tcp: (tcp:db.example.net,14330) говорит клиенту использовать TCP и ничего больше. Он необязателен, но не даёт клиенту сначала пробовать другие протоколы, когда что-то настроено неправильно, и от этого ошибки становятся понятнее.

В RE:NODE тарифы SQL Server настроены так, что этого диалога достаточно: у сервера свой пароль sa, а база данных создана для вас и назначена базой по умолчанию для sa, так что SSMS открывается сразу в ней. Имя сервера - это хост и порт, указанные на тарифе, через запятую.

Шифрование и Trust server certificate#

SSMS 20 заменил старую галочку «Encrypt connection» настройкой Encryption с тремя значениями и перешёл на драйвер, который шифрует по умолчанию:

С Mandatory - значением по умолчанию - клиент проверяет сертификат сервера так же, как браузер проверяет сертификат сайта: он должен вести по цепочке к центру, которому доверяет машина, а его имя должно совпадать с введённым именем сервера. SQL Server, которому не выдали сертификат, при запуске генерирует самоподписанный, которому не доверяет ни одна машина, и подключение падает вот с такой ошибкой:

A connection was successfully established with the server, but then an error occurredduring the login process. (provider: SSL Provider, error: 0 - The certificate chainwas issued by an authority that is not trusted.)

Галочка Trust server certificate оставляет соединение зашифрованным и пропускает проверку. Для сервера на хостинге с самоподписанным сертификатом это обычная настройка, и она ощутимо лучше, чем отключение шифрования: ваш пароль и данные по-прежнему идут через интернет в зашифрованном виде. Вы теряете лишь защиту от того, кто перехватит соединение и предъявит свой сертификат, а для этого ему нужно находиться на сетевом пути между вами и сервером.

Если у сервера есть сертификат от доверенного центра на имя вроде sql.example.com, подключайтесь по этому имени и оставьте Trust server certificate неотмеченным, чтобы получить полную проверку. Host name in certificate в свойствах подключения нужен для случая, когда вы подключаетесь по одному имени, а в сертификате указано другое.

Режим Strict проверяет сертификат и не предназначен для самоподписанных конфигураций; с сервером, который предъявляет самоподписанный сертификат, используйте Mandatory с Trust server certificate.

Что стоит настроить в Options#

Кнопка Options >> открывает ещё три вкладки. Для удалённого сервера важны такие:

• Connect to database (Connection Properties) - <default> использует базу по умолчанию для логина. Если ввести здесь имя базы, вы сразу окажетесь в ней. Значение master - запасной выход для одной конкретной ошибки, описанной ниже в разделе о проблемах.
• Network protocol - <default> подходит; для удалённого хоста в любом случае используется TCP/IP.
• Connection time-out - поднимите, если подключаетесь по медленному или далёкому каналу и видите таймауты при входе.
• Execution time-out - 0 означает, что запросы никогда не прерываются по таймауту со стороны клиента; это значение SSMS по умолчанию, и оно нужно для долгих скриптов обслуживания.
• Use custom color - окрашивает строку состояния для этого подключения. Дайте продакшен-серверам красный цвет. Это ничего не стоит и уже уберегло многих от запуска тестового скрипта не в том окне.

SSMS умеет сохранять подключения с паролями в вашем профиле Windows. Удобно на собственной машине, плохая идея на общей, а сохранённый пароль sa - это полный ключ от сервера.

Первые шаги после подключения#

Откройте окно запроса (Ctrl+N или New Query) и убедитесь, где вы находитесь:

SELECT @@SERVERNAME AS server_name,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('ProductVersion') AS version,       DB_NAME() AS current_database,       SUSER_SNAME() AS login_name;

На сервере Express на хостинге это покажет Express Edition (64-bit), номер версии 16.0.x для SQL Server 2022 и базу, в которой вы оказались. Переключают базы и выпадающий список баз на панели инструментов, и USE [dbname];.

Затем, прежде чем писать код приложения под sa, создайте для приложения отдельный логин и пользователя только с нужными ему правами. sa может удалить любую базу на сервере, а строка подключения с ним внутри оседает в конфигурационных файлах, переменных окружения и на ноутбуках разработчиков. Точный скрипт есть в статье Логины, пользователи и роли SQL Server. Когда такой логин появится, используйте sa для администрирования из SSMS и ни для чего больше.

Object Explorer слева показывает базы, таблицы, представления и участников безопасности. Правый клик по таблице даёт Select Top 1000 Rows и Edit Top 200 Rows - это годится, чтобы посмотреть, и плохо подходит для массовых изменений: для всего, что затрагивает больше нескольких строк, используйте T-SQL внутри транзакции, которую можно откатить:

BEGIN TRANSACTION;UPDATE dbo.Customers SET Country = 'GB' WHERE Country = 'UK';-- check the row count in the Messages tab, then:COMMIT;   -- or ROLLBACK;

Загрузка и выгрузка данных через SSMS#

В SSMS для этого есть несколько инструментов, и правильный зависит от того, куда идут данные:

• Generate Scripts (правый клик по базе, Tasks) записывает схему и, по желанию, данные в виде T-SQL. Хорошо для небольших баз и для переноса между версиями, поскольку скрипт выполняется на любой версии, поддерживающей его синтаксис.
• Import Flat File загружает CSV в новую таблицу с предпросмотром типов колонок. Быстро для разовых импортов.
• Export Data-tier Application создаёт .bacpac - пакет из схемы и данных, который можно импортировать в другой SQL Server или Azure SQL Database.
• Back Up и Restore работают с нативными файлами .bak, но путь к файлу в этих диалогах - это путь на сервере, а не на вашем ПК. Чтобы восстановить .bak, который лежит у вас локально, его сначала нужно загрузить на сервер, в каталог, который может читать процесс SQL Server.

Для всего большого или повторяющегося консольные инструменты лучше мастеров: sqlcmd для скриптов и bcp для массового копирования, о них - в статье sqlcmd и bcp. Перенос целой базы с другого хостинга - отдельная процедура с правилами версий, описанная в статье Перенос базы на хостинг SQL Server.

Решение проблем с подключением#

`A network-related or instance-specific error occurred while establishing a connection to SQL Server. ... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)` - клиент так и не достучался до сервера по TCP. Проверьте, нет ли двоеточия вместо запятой, неправильного порта или firewall. Test-NetConnection из примера выше скажет, что именно.

`(provider: TCP Provider, error: 0 - No such host is known.)` - имя хоста не разрешается. Опечатка или DNS-запись, которой ещё нет.

`(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)` - вы использовали host\INSTANCE, и клиент ищет службу SQL Server Browser. Используйте вместо этого host,port.

`The certificate chain was issued by an authority that is not trusted.` - шифрование в режиме Mandatory, а сертификат самоподписанный. Отметьте Trust server certificate.

`The target principal name is incorrect.` - сертификат действителен, но выдан на другое имя, чем то, что вы ввели. Подключайтесь по имени из сертификата, задайте Host name in certificate или доверьтесь сертификату.

`Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)` - неверный пароль, неверное имя логина или логин отключён. Сообщение намеренно не говорит, что именно; это говорит номер state в журнале ошибок сервера. Введите пароль заново, а не вставляйте его, поскольку вставленные пароли часто тащат за собой пробел в конце.

`Cannot open user default database. Login failed. (Error: 4064)` - база по умолчанию для логина удалена, переименована или находится в офлайне. На этом попадаются любители наводить порядок: на сервере, где базой по умолчанию для sa является созданная для вас база, её удаление или переименование означает, что sa больше не может войти обычным способом. Исправляется так: откройте Options >> Connection Properties, введите master в Connect to database, подключитесь и выполните:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [master];

Подключение работает, а потом обрывается после простоя. Что-то на сетевом пути закрывает простаивающие TCP-соединения. Переподключитесь и держите долгую работу в скриптах, а не в простаивающих окнах.

FAQ#

Почему SSMS нужна запятая, а не двоеточие перед портом?

Потому что клиентские библиотеки SQL Server определяют имя сервера как host,port, и так было всегда. Форма с двоеточием, которую используют большинство других баз данных и URL, читается как часть имени хоста. Исключение - JDBC: в его URL используется двоеточие.

Безопасно ли отмечать Trust server certificate?

Соединение остаётся зашифрованным, так что пароли и данные нельзя прочитать в пути. Пропускается лишь подтверждение подлинности сервера, которое защищает от атакующего, находящегося на сетевом пути. Для сервера на хостинге с самоподписанным сертификатом это стандартная настройка; используйте доверенный сертификат и полную проверку, когда этого требует модель угроз.

Можно ли использовать SSMS на Mac?

Нет, SSMS работает только на Windows. Используйте Visual Studio Code с расширением MSSQL, DBeaver или sqlcmd. Те же имя сервера, логин и настройки шифрования применяются в каждом из них.

Стоит ли использовать логин sa для приложения?

Нет. Используйте sa для администрирования из SSMS и создайте для каждого приложения отдельный логин только с нужными ему правами. Если учётные данные приложения утекут, ущерб ограничится тем, что может этот логин.

Почему я могу подключиться из дома, но не с работы?

Ваша рабочая сеть блокирует исходящие соединения на порт сервера. Запустите Test-NetConnection в обоих местах, чтобы убедиться, а затем либо попросите разрешить этот порт, либо подключайтесь из другого места.