SQL Server-ის connection string-ები ყველა დრაივერისთვის

SQL Server-ის connection string-ს ხუთი რამ სჭირდება: ჰოსტი და პორტი, ბაზა, login, პაროლი და შიფრაციის პარამეტრი, რომელიც სერვერის სერტიფიკატს შეესაბამება. პორტზე ცდება ხალხის უმეტესობა - .NET და ODBC მას მძიმის შემდეგ წერს (db.example.net,14330), ხოლო JDBC და URL-ის სტილის სტრიქონები ორწერტილს იყენებს. შიფრაციაზე ცდება დანარჩენი: Microsoft-ის ყველა მიმდინარე დრაივერი ნაგულისხმევად შიფრავს და სერვერის სერტიფიკატს ამოწმებს, ამიტომ self-signed სერტიფიკატიან სერვერს სჭირდება TrustServerCertificate=True (ან დრაივერის შესაბამისი ჩანაწერი), სანამ სანდოს არ დააყენებ. ეს პოსტი თითოეული გავრცელებული დრაივერისთვის მომუშავე სტრიქონს იძლევა, ასევე საკვანძო სიტყვებს, რომელთა ცოდნაც ღირს, და შეცდომებს, რომლებსაც თითოეული შეცდომა იწვევს.

ნაწილები, რომლებიც ყველა connection string-ს აქვს#

დრაივერის მიუხედავად, ერთი და იგივე ინფორმაცია შედის. იცვლება მხოლოდ ჩანაწერი.

.NET-ში Server ასევე იღებს Data Source-ს, Address-სა და Addr-ს, ხოლო Database - Initial Catalog-საც. ეს სინონიმებია; ძველი დოკუმენტაცია გრძელ ფორმებს იყენებს.

ქვემოთ მოცემული მაგალითები იყენებს db.example.net-ს, პორტ 14330-ს და login-ს სახელად app_user. ჩაანაცვლე ისინი შენი მნიშვნელობებით. RE:NODE-ზე SQL Server-ის გეგმა აჩვენებს, რომელი ჰოსტი და პორტი გამოიყენო; სერვერს მოჰყვება sa login და შენთვის შექმნილი ბაზა, ხოლო აპლიკაცია საკუთარი login-ით უნდა დაუკავშირდეს და არა sa-თი - SQL Server-ის login-ები, user-ები და როლები მის შესაქმნელ სკრიპტს შეიცავს.

შიფრაციის ნაგულისხმევები, დრაივერების მიხედვით#

ეს ცხრილი "ძველ დრაივერთან მუშაობდა" ტიპის საჩივრების უმეტესობას ხსნის:

როცა შიფრაცია ჩართულია და სერტიფიკატის შემოწმება გამორთული არ არის, დრაივერი ამოწმებს, რომ სერვერის სერტიფიკატი სანდო ორგანომდე მიდის და რომ მისი სახელი ემთხვევა ჰოსტს, რომელსაც დაუკავშირდი. SQL Server გაშვებისას self-signed სერტიფიკატს აგენერირებს, თუ სხვა არ მისცეს, ამიტომ ახალი სერვერი ამ შემოწმებას ვერ გადის. შენი ვარიანტები:

1. ენდე სერტიფიკატს - TrustServerCertificate=True. კავშირი მაინც დაშიფრულია; სერვერის ვინაობა არ მოწმდება. ეს ჩვეული პარამეტრია ჰოსტირებული სერვერისთვის self-signed სერტიფიკატით.
2. დააყენე სანდო სერტიფიკატი სერვერზე, იმ სახელისთვის, რომლითაც უკავშირდები, და შემოწმება ჩართული დატოვე. სწორი გამოსავალი, როცა ქსელის გზაზე მყოფი ვინმე შენი საფრთხის მოდელის ნაწილია.
3. გამორთე შიფრაცია - Encrypt=False. ინტერნეტით ეს ნუ გააკეთე. Login-ის პაკეტი მაინც დაშიფრულია, მაგრამ ყოველი query და ყოველი row ამის შემდეგ ღია ტექსტით მოგზაურობს.

თუ მეორე ვარიანტს აირჩევ, სახელი, რომლითაც უკავშირდები, სერტიფიკატით დაფარული უნდა იყოს. IP მისამართით დაკავშირება სერვერთან, რომლის სერტიფიკატზეც sql.example.com წერია, შემოწმებას ვერ გადის, მიუხედავად იმისა, რომ სერტიფიკატი სხვა მხრივ იდეალურია - .NET-ში შეტყობინებით "The target principal name is incorrect", Java-ში კი ჰოსტის სახელის შეუსაბამობით. დაუკავშირდი სახელით, ან უთხარი დრაივერს, რომელი სახელი მოელოდოს (HostNameInCertificate .NET-სა და ODBC-ში, hostNameInCertificate JDBC-ში). სერტიფიკატს ასევე უნდა ენდობოდეს მანქანა, რომელზეც აპლიკაცია მუშაობს, და არა მხოლოდ შენი ლეპტოპი - კონტეინერის image-ს მინიმალური CA bundle-ით შეუძლია უარყოს სერტიფიკატი, რომელსაც შენი desktop იღებს.

Microsoft.Data.SqlClient 5.0 და შემდეგი ასევე იღებს Encrypt=Strict-ს, რომელიც TDS 8.0-ს იყენებს და TLS-ს ყველაფერზე ადრე ათანხმებს. მას SQL Server 2022 სჭირდება და სერტიფიკატს ყოველთვის ამოწმებს, ამიტომ self-signed მოწყობისთვის არ არის.

.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-ში, რაც კითხვას "რომელი აპლიკაცია უშვებს ამ query-ს" ერთხაზიან პასუხად აქცევს. სხვა საკვანძო სიტყვები, რომელთა ცოდნაც ღირს:

ASP.NET Core-ში სტრიქონი კონფიგურაციაში ჩადე როგორც ConnectionStrings:Default და production-ში მიაწოდე გარემოს ცვლადით სახელად ConnectionStrings__Default. EF Core მას კითხულობს UseSqlServer(builder.Configuration.GetConnectionString("Default"))-ით. EF Core 7 და შემდეგი Microsoft.Data.SqlClient 5-ს იყენებს, ამიტომ აპლიკაციის EF Core 6-დან განახლება ხშირად ის მომენტია, როცა სერტიფიკატის შეცდომა პირველად ჩნდება.

Pooling ცალკე წინადადებას იმსახურებს. SqlClient ინახავს ერთ pool-ს თითო განსხვავებულ connection string-ზე თითო პროცესში, ამიტომ ორი სტრიქონი, რომლებიც მხოლოდ საკვანძო სიტყვების თანმიმდევრობით ან Application Name-ით განსხვავდება, ორ pool-ს ქმნის. ააწყე სტრიქონი ერთხელ და ხელახლა გამოიყენე. ასი კავშირი თითო pool-ზე ბევრად მეტია, ვიდრე პატარა SQL Server-ს სჭირდება; თუ აპლიკაციის რამდენიმე ინსტანსი ერთ Express სერვერს იზიარებს, შეამცირე Max Pool Size, რომ მათი ჯამი გონივრული დარჩეს, და შეტყობინება "timeout expired while obtaining a connection from the pool" მიიღე როგორც ნიშანი, რომ კავშირებს ზედმეტად დიდხანს იჭერ, და არა იმისა, რომ pool ძალიან პატარაა. Connection pool-ები და ლიმიტები განმარტავს, რატომ.

Connection string-ები კოდში ააწყე SqlConnectionStringBuilder-ით და არა სტრიქონების შეერთებით; ის მნიშვნელობებს სწორად escape-ავს:

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;, შეცვალე პაკეტი და namespace Microsoft.Data.SqlClient-ზე. ძველი პაკეტი მოძველებულია და ახალი შესაძლებლობების მიღება დიდი ხნის წინ შეწყვიტა. .NET და PostgreSQL, MySQL ან SQL Server pooling-სა და EF Core provider-ის მოწყობას უფრო ღრმად განიხილავს.

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; აირჩიე artifact-ის ვარიანტი, რომელიც შენს 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-ის ნაგულისხმევი pool, ნაგულისხმევად 10 კავშირს იყენებს, რაც პატარა სერვერისთვის გონივრული ზომაა. დრაივერ 10.2-მა encrypt-ის ნაგულისხმევი true-ზე შეცვალა, ამიტომ Spring აპლიკაცია, რომელიც ამ ვერსიაზე გადავიდა, იწყებს ჩავარდნას შეცდომით PKIX path building failed - Java-ს ხერხი თქვას, რომ სერტიფიკატი სანდო არ არის.

ODBC: Driver 18, PHP და ყველაფერი დანარჩენი#

ODBC საერთო მნიშვნელია: PHP-ის sqlsrv და pdo_sqlsrv extension-ები, Python-ის pyodbc, 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-სთვის მძიმიან ფორმად გარდაქმნის. დრაივერის სახელი და ოფციები query string-ში იწერება, 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 პაროლში სპეციალურ სიმბოლოებს შენ მაგივრად escape-ავს, რასაც ხელით დაწერილი URL-ები ხშირად არასწორად აკეთებს. pool_pre_ping=True pool-ში არსებულ კავშირს გამოყენებამდე ამოწმებს, ამიტომ კავშირი, რომელიც ქსელმა ღამით დახურა, დილის პირველ მოთხოვნას არ ჩააგდებს.

Django იყენებს mssql-django backend-ს, რომელიც ასევე 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 ბიბლიოთეკით ესაუბრება და არა Microsoft-ის ODBC დრაივერით. მას ცალკე დრაივერის დაყენება არ სჭირდება, რაც მას მსუბუქ კონტეინერებში მიმზიდველს ხდის, მაგრამ მისი შიფრაციისა და TLS-ის ქცევა დამოკიდებულია იმაზე, როგორ აიწყო და კონფიგურირდა FreeTDS, ამიტომ ინტერნეტით მასზე დაყრდნობამდე შეამოწმე, მართლა დაშიფრულია თუ არა კავშირი. პროექტების უმეტესობისთვის pyodbc Driver 18-ით უკეთ მხარდაჭერილი გზაა, რადგან ის იმავე დრაივერს იყენებს, რომელსაც Microsoft ყველა სხვა ენისთვის ავითარებს.

რომელი ბიბლიოთეკაც არ უნდა გამოიყენო, pool პატარა შეინარჩუნე. Python ვებ-აპლიკაციას, რომელიც ოთხ worker პროცესს უშვებს, თითოეულს SQLAlchemy pool-ით ხუთი პლუს ხუთი overflow, თავისით ორმოცი კავშირის გახსნა შეუძლია. ეს კარგია ერთი აპლიკაციისთვის საკუთარ სერვერზე და ზედმეტია, როცა სამი აპლიკაცია ერთ პატარა Express ინსტანსს იზიარებს.

Node.js: mssql და tedious#

mssql პაკეტი ჩვეული არჩევანია; ის სუფთა JavaScript-ის tedious დრაივერს ფუთავს და pooling-ს ამატებს.

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");

ჰოსტი და პორტი აქ ცალკე თვისებებია, ამიტომ მძიმისა და ორწერტილის კითხვა არ ჩნდება. ნაჩვენები pool-ის პარამეტრები პაკეტის ნაგულისხმევებია. მომხმარებლისგან მიღებული ყოველი მნიშვნელობისთვის გამოიყენე .input() პარამეტრები; SQL-ის სტრიქონებით აწყობა ისაა, საიდანაც injection ნებისმიერ ენაში იწყება. mssql ასევე იღებს ADO.NET-ის სტილის connection string-ს - sql.connect("Server=db.example.net,14330;Database=app;...") - რაც მოსახერხებელია, როცა იგივე სტრიქონი .NET სერვისთან არის გაზიარებული.

Connection string-ების კოდის გარეთ შენახვა#

Connection string, რომელშიც პაროლია, credential-ია. სადაც არ უნდა ცხოვრობდეს, ვინმეს მისი წაკითხვა შეუძლია.

• გარემოს ცვლადები საბაზისო დონეა. ზემოთ ჩამოთვლილი ყველა runtime მათ კითხულობს, და ჰოსტინგის პანელზე ისინი სერვერზე ყენდება და არა რეპოზიტორიაში commit-დება. გარემოს ცვლადები და საიდუმლოებები შაბლონებს განიხილავს.
• არასოდეს გააკეთო მათი commit. არც appsettings.json-ში, არც application.properties-ში, არც .env-ში. ფაილები, რომლებიც ადგილობრივ საიდუმლოებებს შეიცავს, .gitignore-ში პირველ commit-მდე დაამატე, რადგან Git-ის ისტორიიდან პაროლის ამოღება ბევრად რთულია, ვიდრე მისი არასოდეს დამატება.
• ერთი login თითო აპლიკაციაზე, მხოლოდ საჭირო უფლებებით, რომ ერთმა გაჟონილმა სტრიქონმა ერთი აპლიკაციის მონაცემები გამოაჩინოს და არა მთელი სერვერი.
• გაჟონვის შემდეგ შეცვალე. შეცვალე პაროლი ALTER LOGIN-ით, განაახლე გარემოს ცვლადი, გადატვირთე. Pool არსებულ კავშირებს ღიად ინახავს, სანამ ისინი არ განახლდება, ამიტომ გადატვირთე და ნუ დაელოდები.

ახალი connection string შეამოწმე, სანამ აპლიკაციაში ჩასვამ, რომ ერთდროულად ერთ რამეს ასწორებდე. sqlcmd იმავე ნაწილებს ბრძანების ხაზზე იღებს - sqlcmd -S tcp:db.example.net,14330 -U app_user -d app -C უკავშირდება, პაროლს გეკითხება, ხოლო -C სერვერის სერტიფიკატს ენდობა - და წარმატებული SELECT DB_NAME(); ამტკიცებს, რომ ჰოსტი, პორტი, credential-ები და ბაზა სწორია. თუ ეს მუშაობს, აპლიკაცია კი არა, პრობლემა იმაშია, როგორ აწყობს ან კითხულობს აპლიკაცია თავის სტრიქონს - ჩვეულებრივ, გარემოს ცვლადი, რომელიც იქ არ არის დაყენებული, სადაც გგონია.

ბაზის უსაფრთხოების ჩეკლისტი ჩამოთვლის დანარჩენს, რაც ინტერნეტზე გამოტანილი ბაზის გარშემო უნდა იყოს.

პრობლემების მოგვარება შეცდომის შეტყობინებით#

`The certificate chain was issued by an authority that is not trusted` (.NET, ODBC) ან `PKIX path building failed` (Java) - შიფრაცია ჩართულია, სერტიფიკატი self-signed-ია. დაამატე შენი დრაივერის ნდობის პარამეტრი.

`A network-related or instance-specific error occurred` ან `Login timeout expired` - დრაივერმა სერვერს ვერ მიაღწია. შეამოწმე პორტის გამყოფი (მძიმე .NET-სა და ODBC-სთვის, ორწერტილი JDBC-სთვის), პორტის ნომერი და მისაწვდომია თუ არა პორტი იქიდან, სადაც აპლიკაცია მუშაობს.

`Login failed for user 'app_user'` (შეცდომა 18456) - არასწორი პაროლი, არასწორი login-ის სახელი, ან login-ს არ აქვს წვდომა სტრიქონში დასახელებულ ბაზაზე. დაუკავშირდი იმავე credential-ებით SSMS-ში, რომ კოდის პრობლემა credential-ის პრობლემისგან გამიჯნო; დაკავშირება SSMS-ით ამას ნაბიჯ-ნაბიჯ გადის.

`Cannot open database "app" requested by the login. The login failed.` - login არსებობს, მაგრამ ამ ბაზაში user არ აქვს, ან ბაზის სახელი არასწორია. შექმენი user ბაზაში ამ login-ისთვის.

`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?

ის კავშირს დაშიფრულს ტოვებს და სერვერის ვინაობის შემოწმებას გამოტოვებს. ეს პასიური მოსმენისგან იცავს, მაგრამ არა ქსელის გზაზე მდგომი აქტიური თავდამსხმელისგან, რომელიც საკუთარ სერტიფიკატს წარადგენს. ჰოსტირებული სერვერისთვის self-signed სერტიფიკატით ეს პრაქტიკული არჩევანია; სანდო სერტიფიკატი შემოწმებით უფრო ძლიერია.

რა არის SQL Server-ის ნაგულისხმევი პორტი?

1433 TCP-ზე. ჰოსტირებული სერვერები ხშირად სხვა პორტს იყენებს, ამიტომ ყოველთვის ის გამოიყენე, რომელიც მოგცეს, მძიმის შემდეგ ჩაწერილი.

მჭირდება ODBC Driver 18 თუ 17-იც კარგია?

Driver 17 ჯერ კიდევ მუშაობს, მაგრამ 18 მიმდინარეა და შესწორებებს იღებს. განახლებისას დაამატე TrustServerCertificate=yes ან სანდო სერტიფიკატი, რადგან 18 ნაგულისხმევად შიფრავს, 17 კი არ შიფრავდა.

შემიძლია Windows ავთენტიფიკაცია გამოვიყენო ჰოსტირებულ SQL Server-თან?

ჩვეულებრივ არა. Windows ავთენტიფიკაციას სჭირდება, რომ კლიენტი და სერვერი ისეთ დომენში იყოს, რომლებიც ერთმანეთს ენდობა. ჰოსტირებული სერვერი, განსაკუთრებით Linux-ზე, SQL ავთენტიფიკაციას იყენებს login-ითა და პაროლით.