Připojení ke vzdálenému SQL Serveru přes SSMS

Chceš-li připojit SQL Server Management Studio k hostovanému SQL Serveru, otevři Connect to Server a nastav čtyři věci: Server name je host a port oddělené čárkou (db.example.net,14330 - dvojtečka nefunguje), Authentication je SQL Server Authentication, Login a Password jsou přihlašovací údaje, které jsi dostal (na čerstvém serveru často sa), a u šifrování nech Mandatory a zaškrtni Trust server certificate, pokud server nemá certifikát od důvěryhodné autority. Tím je vyřízeno téměř každé první připojení. Zbytek článku vysvětluje každé pole, volby, které stojí za změnu, co dělat po připojení a přesné chybové hlášky pro každý způsob selhání.

Co potřebuješ, než začneš#

Čtyři informace a jeden program:

• Host - název hostitele nebo IP adresa.
• Port - výchozí port SQL Serveru je 1433, ale hostované servery často používají jiný, protože si adresu sdílí mnoho serverů. Použij port, který jsi dostal, ne výchozí.
• Login - jméno SQL loginu. Na novém serveru to je obvykle sa, vestavěný systémový administrátor.
• Password - heslo k tomuto loginu.
• SSMS - SQL Server Management Studio, bezplatný nástroj pro správu od Microsoftu. Běží jen na Windows.

Novější verze SSMS (verze 20 a vyšší) zavedly možnosti šifrování popsané níže. Pokud je tvoje kopie starší, aktualizuj ji; staré verze používají starší ovladač s jinými výchozími hodnotami a chybí jim roky oprav. Na macOS nebo Linuxu použij Visual Studio Code s rozšířením MSSQL od Microsoftu, DBeaver nebo příkazový řádek sqlcmd - každé pole v tomto článku má tam svůj ekvivalent. Azure Data Studio, které bývalo multiplatformní odpovědí, Microsoft ukončil, takže v něm nezačínej novou práci.

Než začneš vinit SSMS, zkontroluj, že port je z tvého počítače dosažitelný. V PowerShellu:

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

TcpTestSucceeded : True znamená, že síťová cesta je otevřená a cokoli selže potom, je nastavení v SSMS nebo přihlašovací údaje. False znamená, že v cestě stojí firewall - tvůj, tvé sítě nebo serveru - a nic v SSMS to nespraví. Některé firemní a školní sítě blokují odchozí provoz na neobvyklé porty; zkus to z jiného připojení, abys to vyloučil.

Dialog Connect to Server, pole po poli#

Čárka je nejčastější jednotlivá chyba. Nástroje SQL Serveru zdědily syntaxi host,port z původních klientských knihoven a používá ji všechno v ekosystému Microsoftu - SSMS, sqlcmd, připojovací řetězce ADO.NET, ODBC. Napíšeš-li db.example.net:14330, klient bere celý řetězec jako název hostitele, nepodaří se mu ho přeložit nebo zkusí pojmenované roury a dá ti síťovou chybu, která o dvojtečce neříká nic. Tvar se zpětným lomítkem, host\INSTANCE, je pro pojmenované instance zjišťované službou SQL Server Browser na UDP 1434, kterou hostované servery nevystavují; s explicitním portem ho nikdy nepotřebuješ.

Předpona tcp: (tcp:db.example.net,14330) říká klientovi, aby použil TCP a nic jiného. Je volitelná a brání klientovi zkoušet nejdřív jiné protokoly, když je něco špatně nastavené, díky čemuž jsou chyby srozumitelnější.

Na RE:NODE jsou tarify SQL Serveru připravené tak, aby ti stačil tento dialog: server má vlastní heslo sa a je pro tebe vytvořena databáze nastavená jako výchozí pro sa, takže SSMS se otevře rovnou v ní. Jméno serveru je host a port zobrazené u tarifu, s čárkou.

Šifrování a Trust server certificate#

SSMS 20 nahradilo staré zaškrtávátko „Encrypt connection“ nastavením Encryption se třemi hodnotami a přešlo na ovladač, který šifruje ve výchozím stavu:

S Mandatory - výchozím nastavením - klient kontroluje certifikát serveru tak, jak prohlížeč kontroluje web: musí navazovat na autoritu, které stroj důvěřuje, a jeho jméno se musí shodovat s názvem serveru, který jsi zadal. SQL Server, který certifikát nedostal, si při startu vygeneruje self-signed, kterému žádný stroj nedůvěřuje, takže připojení selže s tímto:

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.)

Zaškrtnutí Trust server certificate ponechá připojení šifrované a přeskočí ověření. To je běžné nastavení pro hostovaný server se self-signed certifikátem a je to výrazně lepší než vypnout šifrování: tvoje heslo i data stále putují internetem šifrovaně. Vzdáváš se ochrany před někým, kdo připojení zachytí a předloží vlastní certifikát, což vyžaduje, aby seděl v síti na cestě mezi tebou a serverem.

Pokud má server certifikát od důvěryhodné autority pro jméno jako sql.example.com, připoj se pod tímto jménem a nech Trust server certificate nezaškrtnuté, abys dostal plné ověření. Host name in certificate v nastavení připojení řeší případ, kdy se připojuješ pod jedním jménem a certifikát nese jiné.

Režim Strict certifikát ověřuje a není pro self-signed sestavy; u serveru, který předkládá self-signed certifikát, použij Mandatory s Trust server certificate.

Volby, které stojí za nastavení v Options#

Tlačítko Options >> otevře další tři záložky. Ty, na kterých u vzdáleného serveru záleží:

• Connect to database (Connection Properties) - <default> použije výchozí databázi loginu. Napsání názvu databáze sem otevře rovnou ji. Nastavení na master je záchranná cesta pro jednu konkrétní chybu, popsanou níže v řešení potíží.
• Network protocol - <default> je v pořádku; u vzdáleného hostitele se tak či tak používá TCP/IP.
• Connection time-out - zvyš ho, pokud se připojuješ přes pomalou nebo vzdálenou linku a při přihlášení vidíš timeouty.
• Execution time-out - 0 znamená, že dotazy ze strany klienta nikdy nevyprší, což je výchozí nastavení SSMS a to, co chceš pro dlouhé udržovací skripty.
• Use custom color - obarví stavový řádek pro toto připojení. Produkční servery dej červeně. Nestojí to nic a už to mnoho lidí uchránilo před spuštěním testovacího skriptu ve špatném okně.

SSMS umí ukládat připojení s hesly do tvého profilu ve Windows. Na vlastním stroji pohodlné, na sdíleném špatný nápad, a uložené heslo k sa je plný klíč k serveru.

První kroky po připojení#

Otevři okno dotazu (Ctrl+N nebo New Query) a ověř, kde jsi:

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

Na hostovaném serveru Express to ukáže Express Edition (64-bit), číslo verze 16.0.x pro SQL Server 2022 a databázi, ve které jsi skončil. Rozbalovací seznam databází na panelu nástrojů i USE [dbname]; databáze přepínají.

Pak, než napíšeš aplikační kód proti sa, vytvoř pro aplikaci vyhrazený login a uživatele jen s oprávněními, která potřebuje. sa může zahodit každou databázi na serveru a připojovací řetězec s ním žije v konfiguračních souborech, proměnných prostředí a na noteboocích vývojářů. Přesný skript najdeš v loginy, uživatelé a role v SQL Serveru. Jakmile existuje, používej sa pro administraci ze SSMS a k ničemu jinému.

Object Explorer vlevo ukazuje databáze, tabulky, pohledy a bezpečnostní principály. Pravý klik na tabulku nabídne Select Top 1000 Rows a Edit Top 200 Rows, což je v pořádku na nahlédnutí a špatné pro hromadné změny - na cokoli, co se dotkne víc než hrstky řádků, použij T-SQL uvnitř transakce, kterou můžeš vrátit:

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

Přesun dat dovnitř a ven přes SSMS#

SSMS má na to několik nástrojů a správný záleží na tom, kam data jdou:

• Generate Scripts (pravý klik na databázi, Tasks) zapíše schéma a volitelně data jako T-SQL. Dobré pro malé databáze a pro přesuny mezi verzemi, protože skript běží na každé verzi, která jeho syntaxi podporuje.
• Import Flat File načte CSV do nové tabulky s náhledem datových typů sloupců. Rychlé pro jednorázové importy.
• Export Data-tier Application vytvoří .bacpac, balíček schématu a dat, který lze importovat do jiného SQL Serveru nebo Azure SQL Database.
• Back Up and Restore pracuje s nativními soubory .bak, ale cesta k souboru v těchto dialozích je cesta na serveru, ne na tvém PC. Chceš-li obnovit .bak, který máš lokálně, musí se nejdřív nahrát na server, do adresáře, který proces SQL Serveru umí číst.

Na cokoli velkého nebo opakovatelného jsou nástroje příkazového řádku lepší než průvodci: sqlcmd pro skripty a bcp pro hromadné kopírování, popsané v sqlcmd a bcp. Přesun celé databáze z jiného hostingu je samostatný postup s pravidly pro verze v migrace databáze na hosting SQL Serveru.

Řešení chyb připojení#

`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)` - klient se k serveru přes TCP nikdy nedostal. Zkontroluj dvojtečku místo čárky, špatný port nebo firewall. Test-NetConnection shora ti řekne, co z toho to je.

`(provider: TCP Provider, error: 0 - No such host is known.)` - název hostitele se nepřekládá. Překlep, nebo záznam DNS, který ještě neexistuje.

`(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)` - použil jsi host\INSTANCE a klient hledá službu SQL Server Browser. Použij místo toho host,port.

`The certificate chain was issued by an authority that is not trusted.` - šifrování je Mandatory a certifikát je self-signed. Zaškrtni Trust server certificate.

`The target principal name is incorrect.` - certifikát je platný, ale vystavený na jiné jméno, než jsi zadal. Připoj se pod jménem z certifikátu, nastav Host name in certificate nebo certifikátu důvěřuj.

`Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)` - špatné heslo, špatné jméno loginu, nebo je login zakázaný. Hláška záměrně neříká, co z toho; chybový log serveru zaznamenává číslo stavu, které to říká. Heslo raději přepiš, než abys ho vkládal, protože vložená hesla často nesou mezeru na konci.

`Cannot open user default database. Login failed. (Error: 4064)` - výchozí databáze loginu byla zahozena, přejmenována nebo je offline. Tohle potká lidi, kteří uklízejí: na serveru, kde je výchozí databází sa ta vytvořená pro tebe, znamená její zahození nebo přejmenování, že se sa už nemůže přihlásit normální cestou. Oprava: otevři Options >> Connection Properties, do Connect to database napiš master, připoj se a spusť:

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

Připojení funguje, pak po nečinnosti spadne. Něco na síťové cestě zavírá nečinná spojení TCP. Připoj se znovu a dlouho běžící práci drž raději ve skriptech než v nečinných oknech.

FAQ#

Proč SSMS potřebuje před portem čárku místo dvojtečky?

Protože klientské knihovny SQL Serveru definují jméno serveru jako host,port a vždy to tak bylo. Tvar s dvojtečkou, který používá většina jiných databází a adres URL, se čte jako součást názvu hostitele. Výjimkou je JDBC: jeho adresy URL používají dvojtečku.

Je bezpečné zaškrtnout Trust server certificate?

Připojení zůstává šifrované, takže hesla a data nejsou při přenosu čitelná. Přeskakuje se důkaz totožnosti serveru, který chrání před útočníkem umístěným na síťové cestě. U hostovaného serveru se self-signed certifikátem je to standardní nastavení; důvěryhodný certifikát a plné ověření použij, když to vyžaduje model hrozeb.

Mohu SSMS používat na Macu?

Ne, SSMS je jen pro Windows. Použij Visual Studio Code s rozšířením MSSQL, DBeaver nebo sqlcmd. V každém z nich platí stejné jméno serveru, login a nastavení šifrování.

Mám pro aplikaci používat login sa?

Ne. sa používej pro administraci ze SSMS a pro každou aplikaci vytvoř samostatný login jen s oprávněními, která potřebuje. Pokud přihlašovací údaje aplikace unikne, škoda je omezená na to, co ten login smí.

Proč se z domova připojím, ale z práce ne?

Tvoje pracovní síť blokuje odchozí spojení na port serveru. Potvrď to spuštěním Test-NetConnection na obou místech a pak buď požádej o povolení portu, nebo se připoj odjinud.