Строки подключения SQL Server для любого драйвера

Строке подключения SQL Server нужны пять вещей: хост и порт, база данных, логин, пароль и настройка шифрования, соответствующая сертификату сервера. На порте ошибается большинство - .NET и ODBC ставят его после запятой (db.example.net,14330), а JDBC и строки в стиле URL используют двоеточие. На шифровании ошибаются остальные: каждый текущий драйвер Microsoft шифрует по умолчанию и проверяет сертификат сервера, так что серверу с самоподписанным сертификатом нужен TrustServerCertificate=True (или то, как это пишется в вашем драйвере), пока вы не установите доверенный. Эта статья даёт рабочую строку для каждого распространённого драйвера, ключевые слова, которые стоит знать, и ошибки, к которым приводит каждый промах.

Из чего состоит любая строка подключения#

Каким бы ни был драйвер, внутрь идут одни и те же сведения. Меняется только написание.

В .NET вместо Server также принимаются Data Source, Address и Addr, а вместо Database - Initial Catalog. Это синонимы; в старой документации используются длинные формы.

В примерах ниже используются db.example.net, порт 14330 и логин app_user. Замените их своими значениями. В RE:NODE тариф SQL Server показывает, какие хост и порт использовать; сервер поставляется с логином sa и созданной для вас базой, а приложение должно подключаться под собственным логином, а не под sa - скрипт для его создания есть в статье Логины, пользователи и роли SQL Server.

Шифрование по умолчанию, драйвер за драйвером#

Эта таблица объясняет большинство жалоб в духе «со старым драйвером работало»:

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

1. Доверять сертификату - TrustServerCertificate=True. Соединение по-прежнему зашифровано; подлинность сервера не проверяется. Это обычная настройка для сервера на хостинге с самоподписанным сертификатом.
2. Установить на сервер доверенный сертификат на имя, по которому вы подключаетесь, и оставить проверку включённой. Правильное решение, когда в вашу модель угроз входит кто-то на сетевом пути.
3. Отключить шифрование - Encrypt=False. Не делайте этого через интернет. Пакет входа всё равно шифруется, но каждый последующий запрос и каждая строка идут открытым текстом.

Если вы выбираете второй вариант, имя, по которому вы подключаетесь, должно быть покрыто сертификатом. Подключение по IP-адресу к серверу, в сертификате которого указано sql.example.com, проваливает проверку, даже если сертификат в остальном безупречен: в .NET с ошибкой «The target principal name is incorrect», в Java - с несовпадением имени хоста. Подключайтесь по имени или скажите драйверу, какое имя ожидать (HostNameInCertificate в .NET и ODBC, hostNameInCertificate в JDBC). Сертификату также должна доверять машина, на которой работает приложение, а не только ваш ноутбук: образ контейнера с минимальным набором CA может отвергнуть сертификат, который принимает ваш десктоп.

Microsoft.Data.SqlClient 5.0 и новее также принимают Encrypt=Strict, который использует TDS 8.0 и согласует TLS раньше всего остального. Он требует SQL Server 2022 и всегда проверяет сертификат, так что для самоподписанных конфигураций не подходит.

.NET: Microsoft.Data.SqlClient и EF Core#

Server=tcp:db.example.net,14330;Database=app;User ID=app_user;Password=...;Encrypt=True;TrustServerCertificate=True;Application Name=orders-api

Префикс tcp: принудительно задаёт TCP и необязателен. Application Name видно в sys.dm_exec_sessions и в Query Store, благодаря чему вопрос «какое приложение выполняет этот запрос» решается одной строкой. Другие ключевые слова, которые стоит знать:

В ASP.NET Core положите строку в конфигурацию как ConnectionStrings:Default, а в продакшене передавайте её переменной окружения ConnectionStrings__Default. EF Core читает её через UseSqlServer(builder.Configuration.GetConnectionString("Default")). EF Core 7 и новее используют Microsoft.Data.SqlClient 5, так что обновление приложения с EF Core 6 - часто именно тот момент, когда впервые появляется ошибка сертификата.

Пулы заслуживают отдельного предложения. SqlClient держит по одному пулу на каждую отдельную строку подключения в процессе, так что две строки, различающиеся лишь порядком ключевых слов или Application Name, создают два пула. Соберите строку один раз и переиспользуйте её. Сто соединений на пул - намного больше, чем нужно небольшому SQL Server; если несколько экземпляров приложения делят один сервер Express, уменьшите Max Pool Size, чтобы их суммарное число оставалось разумным, и воспринимайте «timeout expired while obtaining a connection from the pool» как признак слишком долго удерживаемых соединений, а не слишком маленького пула. Почему так, объясняет статья Пулы соединений и лимиты.

Собирайте строки подключения в коде через SqlConnectionStringBuilder, а не конкатенацией строк; он правильно экранирует значения:

var csb = new SqlConnectionStringBuilder{    DataSource = "tcp:db.example.net,14330",    InitialCatalog = "app",    UserID = "app_user",    Password = Environment.GetEnvironmentVariable("DB_PASSWORD"),    Encrypt = SqlConnectionEncryptOption.Mandatory,    TrustServerCertificate = true};await using var conn = new SqlConnection(csb.ConnectionString);

Если в вашем коде всё ещё using System.Data.SqlClient;, смените пакет и пространство имён на Microsoft.Data.SqlClient. Старый пакет устарел и давно перестал получать новые возможности. Пулы и настройка провайдера EF Core подробнее разобраны в статье .NET с PostgreSQL, MySQL или SQL Server.

Java: драйвер JDBC#

jdbc:sqlserver://db.example.net:14330;databaseName=app;user=app_user;password=...;encrypt=true;trustServerCertificate=true;applicationName=orders-service

JDBC - единственный драйвер Microsoft, который ставит перед портом двоеточие, потому что формат URL пришёл из соглашений Java. Запятая там - ошибка. Свойства идут после хоста через точку с запятой. Координаты Maven - com.microsoft.sqlserver:mssql-jdbc; выберите вариант артефакта под вашу версию Java (строка версии заканчивается на .jre11, .jre17 и так далее).

В Spring Boot:

spring.datasource.url=jdbc:sqlserver://db.example.net:14330;databaseName=app;encrypt=true;trustServerCertificate=truespring.datasource.username=app_userspring.datasource.password=${DB_PASSWORD}spring.datasource.hikari.maximum-pool-size=10

HikariCP, пул по умолчанию в Spring Boot, по умолчанию использует 10 соединений - разумный размер для небольшого сервера. Драйвер 10.2 сделал encrypt по умолчанию равным true, так что приложение на Spring, обновлённое через эту версию, начинает падать с PKIX path building failed - так Java говорит, что сертификату не доверяют.

ODBC: Driver 18, PHP и всё остальное#

ODBC - общий знаменатель: на нём стоят расширения PHP sqlsrv и pdo_sqlsrv, pyodbc в Python, R, Excel и многие инструменты отчётности.

Driver={ODBC Driver 18 for SQL Server};Server=tcp:db.example.net,14330;Database=app;UID=app_user;PWD=...;Encrypt=yes;TrustServerCertificate=yes

Значение Driver должно в точности совпадать с именем установленного драйвера, включая фигурные скобки. В Debian и Ubuntu пакет Microsoft называется msodbcsql18, ставится из репозитория пакетов Microsoft и просит принять лицензию (ACCEPT_EULA=Y в скриптах). Driver 18 шифрует по умолчанию; Driver 17 этого не делал, поэтому переход с 17 на 18 ломает подключения, в которых шифрование вообще не упоминалось.

Значения, содержащие ; или начинающиеся с {, берутся в фигурные скобки, а каждая } удваивается: PWD={pa;ss}}word} для пароля pa;ss}word.

PHP с PDO:

$pdo = new PDO(    "sqlsrv:Server=db.example.net,14330;Database=app;TrustServerCertificate=1",    "app_user",    getenv("DB_PASSWORD"),    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

Python: pyodbc, SQLAlchemy и Django#

pyodbc принимает строку ODBC напрямую:

import osimport pyodbcconn = pyodbc.connect(    "Driver={ODBC Driver 18 for SQL Server};"    "Server=tcp:db.example.net,14330;Database=app;"    f"UID=app_user;PWD={os.environ['DB_PASSWORD']};"    "Encrypt=yes;TrustServerCertificate=yes")

SQLAlchemy использует URL, поэтому порт там идёт после двоеточия, а диалект сам переводит его в форму с запятой для ODBC. Имя драйвера и опции передаются в строке запроса в URL-кодировке:

from sqlalchemy import create_enginefrom sqlalchemy.engine import URLurl = URL.create(    "mssql+pyodbc",    username="app_user",    password=os.environ["DB_PASSWORD"],    host="db.example.net",    port=14330,    database="app",    query={"driver": "ODBC Driver 18 for SQL Server", "TrustServerCertificate": "yes"},)engine = create_engine(url, pool_size=5, max_overflow=5, pool_pre_ping=True)

URL.create сам экранирует специальные символы в пароле, а в написанных вручную URL с этим часто ошибаются. pool_pre_ping=True проверяет соединение из пула перед использованием, так что соединение, которое сеть закрыла за ночь, не роняет первый утренний запрос.

Django использует бэкенд mssql-django, который тоже работает поверх pyodbc:

DATABASES = {    "default": {        "ENGINE": "mssql",        "NAME": "app",        "USER": "app_user",        "PASSWORD": os.environ["DB_PASSWORD"],        "HOST": "db.example.net",        "PORT": "14330",        "OPTIONS": {            "driver": "ODBC Driver 18 for SQL Server",            "extra_params": "TrustServerCertificate=yes",        },    }}

Есть ещё pymssql, который общается с SQL Server через библиотеку FreeTDS, а не через ODBC-драйвер Microsoft. Ему не нужна отдельная установка драйвера, что делает его привлекательным в компактных контейнерах, но его поведение в части шифрования и TLS зависит от того, как FreeTDS собран и настроен, так что проверьте, действительно ли соединение зашифровано, прежде чем полагаться на него через интернет. Для большинства проектов pyodbc с Driver 18 - лучше поддерживаемый путь, потому что он использует тот же драйвер, который Microsoft поддерживает для всех остальных языков.

Какую бы библиотеку вы ни использовали, держите пул маленьким. Веб-приложение на Python с четырьмя рабочими процессами, у каждого из которых пул SQLAlchemy на пять соединений плюс пять сверх лимита, само по себе может открыть сорок соединений. Для одного приложения на собственном сервере это нормально, а когда три приложения делят небольшой экземпляр Express - слишком много.

Node.js: mssql и tedious#

Обычный выбор - пакет mssql; он оборачивает драйвер tedious, написанный на чистом JavaScript, и добавляет пул.

import sql from "mssql";const pool = await sql.connect({  server: "db.example.net",  port: 14330,  database: "app",  user: "app_user",  password: process.env.DB_PASSWORD,  options: { encrypt: true, trustServerCertificate: true },  pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },});const result = await pool.request()  .input("id", sql.Int, 42)  .query("SELECT name FROM dbo.Customers WHERE id = @id");

Хост и порт здесь - отдельные свойства, так что вопроса о запятой или двоеточии не возникает. Показанные настройки пула - значения пакета по умолчанию. Используйте параметры .input() для каждого значения, пришедшего от пользователя; сборка SQL из строк - это то, как происходят инъекции на любом языке. mssql также принимает строку подключения в стиле ADO.NET - sql.connect("Server=db.example.net,14330;Database=app;..."), - что удобно, когда одна и та же строка используется совместно с сервисом на .NET.

Как держать строки подключения вне кода#

Строка подключения с паролем внутри - это учётные данные. Где бы она ни лежала, кто-то может её прочитать.

• Переменные окружения - базовый уровень. Их читает каждый рантайм выше, а в панели хостинга они задаются на сервере, а не коммитятся в репозиторий. Паттерны разобраны в статье Переменные окружения и секреты.
• Никогда их не коммитьте. Ни в appsettings.json, ни в application.properties, ни в .env. Добавьте файлы с локальными секретами в .gitignore до первого коммита, потому что удалить пароль из истории Git намного сложнее, чем никогда его туда не добавлять.
• Один логин на приложение, только с нужными ему правами, чтобы одна утёкшая строка раскрывала данные одного приложения, а не весь сервер.
• Меняйте пароль после утечки. Смените пароль через ALTER LOGIN, обновите переменную окружения, перезапустите. Пул держит существующие соединения открытыми, пока они не будут переработаны, так что перезапускайте, а не ждите.

Проверяйте новую строку подключения, прежде чем класть её в приложение, чтобы отлаживать по одной вещи за раз. sqlcmd принимает те же части в командной строке - sqlcmd -S tcp:db.example.net,14330 -U app_user -d app -C подключается, запрашивает пароль, а -C доверяет сертификату сервера, - и успешный SELECT DB_NAME(); доказывает, что хост, порт, учётные данные и база правильные. Если это работает, а приложение нет, проблема в том, как приложение собирает или читает свою строку, - обычно это переменная окружения, которая задана не там, где вы думаете.

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

Решение проблем по сообщениям об ошибках#

`The certificate chain was issued by an authority that is not trusted` (.NET, ODBC) или `PKIX path building failed` (Java) - шифрование включено, сертификат самоподписанный. Добавьте настройку доверия для вашего драйвера.

`A network-related or instance-specific error occurred` или `Login timeout expired` - драйвер не достучался до сервера. Проверьте разделитель порта (запятая для .NET и ODBC, двоеточие для JDBC), номер порта и доступен ли порт оттуда, где работает приложение.

`Login failed for user 'app_user'` (ошибка 18456) - неверный пароль, неверное имя логина или у логина нет доступа к базе, указанной в строке. Подключитесь с теми же учётными данными в SSMS, чтобы отделить проблему в коде от проблемы с учётными данными; как это сделать, разобрано в статье Подключение через SSMS.

`Cannot open database "app" requested by the login. The login failed.` - логин существует, но в этой базе для него нет пользователя, или имя базы неверное. Создайте в базе пользователя для этого логина.

`Data source name not found and no default driver specified` (ODBC) - имя в Driver не совпадает ни с одним установленным драйвером. В Linux их список выводит odbcinst -q -d.

`Keyword not supported: 'port'` (.NET) - в SqlClient нет ключевого слова Port. Укажите порт после запятой в Server.

FAQ#

Почему SQL Server использует запятую перед портом?

Это синтаксис, который клиентские библиотеки SQL Server использовали всегда, и он перешёл во все драйверы Microsoft, кроме JDBC. Двоеточие читается как часть имени хоста. Библиотеки на основе URL, такие как SQLAlchemy, принимают двоеточие и сами его преобразуют.

Безопасен ли TrustServerCertificate=True?

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

Какой порт у SQL Server по умолчанию?

1433 по TCP. Серверы на хостинге часто используют другой порт, так что всегда указывайте выданный вам, после запятой.

Нужен ли ODBC Driver 18, или 17 достаточно?

Driver 17 всё ещё работает, но актуален 18, и исправления получает он. При обновлении добавьте TrustServerCertificate=yes или доверенный сертификат, потому что 18 шифрует по умолчанию, а 17 этого не делал.

Можно ли использовать Windows-аутентификацию с SQL Server на хостинге?

Обычно нет. Windows-аутентификации нужно, чтобы клиент и сервер были в доменах, доверяющих друг другу. Сервер на хостинге, особенно на Linux, использует SQL-аутентификацию с логином и паролем.