Rollback

SQL 2005-ig alapból a felinstallált SQL Serveren bekerült a sysadminok közé a lokális OS adminok csoportja, BUILTIN\Administrators néven. Ez sokakat zavart, meg nem volt elég secure, úgyhogy az SQL 2008 install során megkérdezni, hogy ki legyen sa a szerveren, nem adja hozzá a lokáladminokat helyből. Ez egy elég ismert feature.

Azt azonban kevesebben tudják, hogy ha single user módban indul el a szerver (parancssorban sqlservr –m –c, az SQL Server bináris könyvtárából lehet indítani, ezt a service-nél nézhetjük meg), akkor a lokáladminok mégiscsak sa-k lesznek, függetlenül a „normális” jogosultságuktól. Ez kiváló failsafe megoldás lehet, például egy túlszigorított környezetben, ahol az átnevezett és letiltott sa-nak elveszítették a jelszavát, és senki más nem volt sysadmin. Ilyenkor nekiállhatunk kiütni a jelszavakat, vagy hozzáadhatunk bárkit új sysadminnak. Vagy akármi más.

Az SQL Server 2008 előtti változatai úgy installálódtak, hogy a lokális adminisztrátor csoport alapból be volt téve az SQL sysadmin role-ba. Időnként, főleg nagy cégeknél, ez nem kívánatos mellékhatásokkal jár, például olyan emberek és accountok kapnak automatikusan jogot az SQL-hez, akiknek semmi közük hozzá egyébként. Így történt, hogy egyszer régen, nagyon régen engem is utolért ez a feladat: válasszam le a Windows adminokat a SQL sysadminokról. Habár ez egy régi történet, többen azóta is emlegetik, úgy elcsesztem, úgyhogy a legutóbbi felhánytorgatáskor úgy döntöttem, megosztom a nagyérdeművel, tanuljatok az én hibámból.

Alapvetően egy éles rendszeren végzett változtatással álltam szemközt, úgyhogy a teszt SQL clusteren megcsináltam a változtatást, felírtam, hogy mit csináltam, és nekiálltam az éles rendszernek is. A feladat mindössze annyi volt, hogy a kivett BUILTIN\Administrators helyett be kellett raknom a DOMAIN\MyTeam csoportot sa-nak, és kész. Hogyan lehet ilyet letesztelni? Mivel clusterről beszélünk, én végigbillentem a SQL-t az összes node-ra, hogy lássam, hogy mindenhol el tud indulni rendesen az új beállításokkal, illetve tudok is hozzá kapcsolódni (ez utóbbira sqlcmd-t használok). Ha az OS-t piszkálom, akkor node reboot után megy ugyanez. Éles négy node-os cluster esetén ez kissé időigényes, és a fél perc helyett négy perc szolgáltatáskiesést okoz, de esküszöm, hogy megéri.

Szóval megcsináltam a változtatást az éles rendszeren is, megnéztem, minden működött, mindenki be tudott lépni, úgyhogy le is zártam az ügyet, és foglalkoztam a többi dologgal. Aztán egy pár hét múlva éjféltájban felhívott az ügyeletesünk, hogy a fent említett cluster nem indul el. Kérdeztem, hogy mi a tünet, és azt mondta, hogy felhívták azzal, hogy piros a monitor, megnézte, a cluster admin szerint el volt dőlve a szolgáltatás, megpróbálta újraindítani, el is indult minden egy időre, aztán egy-két perc után megint elfrakkolt, ezt eljátszotta párszor, azóta meg failed állapotban van. Rutinos üzemeltetőként végigpörgött a fejemben, hogy mi mindent csináltunk a szerverrel az elmúlt időben, és a tünetek alapján rögtön tudtam, hogy mi a baj. Azért megkértem ,hogy nézze meg az errorlogot, amire elmondta az azóta híressé vált mondatot: „valami júzer be akar jelentkezni itt, és nem sikerül neki – de ez most mindegy, amíg nem fut a cluster rendesen”.

Itt egy kicsit röhögtem, és elmondtam neki, hogy a valami júzer a cluster service-t futtató account, és úgy működik a dolog, hogy a cluster service elindítja a clusterezett resource-okat, így például az SQL Server service-t, és pollozza őket, hogy működnek-e. Az SQL Server pollozása pedig úgy néz ki, hogy belogol a cluster service account, és időnként egy select @@servername query-t bedob. Ha ez sikerül, örül, ha nem, akkor egy megadott idő (default 180 másodperc, ha jól rémlik így a vonaton) után betegnek nyilvánítja a SQL Server service-t, és megállítja. Kiderült, hogy egy korábbi bohóckodás miatt a teszt clusteren már hozzá volt adva a cluster service külön SQL accountként, úgyhogy ott nem is volt semmi hiba. Az élesen csak az ütött be, amikor leterhelték a clustert, és megállt a SQL, majd el akart megint indulni, addig ugyanis nem próbált meg belogolni újra a cluster service account, így nem zavarta, hogy dobtam (vagyis droptam) a loginját. Amikor újraindult a SQL, kiválóan működött minden – 180 másodpercig, amikor is a sok hiábavaló próbálkozás után a cluster service betegnek nyilvánította az egyébként makkegészséges szervert, és lelőtte. A baj az volt, hogy megfelelő számú próbálkozás után már el sem jutottunk odáig, hogy elinduljon a SQL és beléphessünk megjavítani. Úgyhogy jött mindenki barátja, a single user mód: sqlservr -c -m, amiben sqlcmd-vel belépve kiválóan meg lehetett pákázni. Ilyenkor persze a SQL tényleg a cluster node-on fut, nem virtual serverként, tehát nem úgy lehet hozzá csatlakozni, mint máskor, hanem (local) vagy . vagy hosztnév. Gyorsan csak ennyit mondtunk:

create login ’DOMAIN\_clusterserviceuser’ from windows

Figyelembe véve, hogy ő a szerver nevén kívül nem kérdez semmit, nagyon boldog a public role-lal, nem kell neki több jog. El is indult, örültünk is.

Tanulságok:

• A teszt és éles rendszereket szinkronban kell tartani.
• A változtatások után mindig meg kell győződni arról, hogy mindent életben hagytunk: újraindítani az érintett service-eket, stb.

Egy nagy kedvenc karikatúrám:

Magyar(nak tűnő) szöveg:
(1) -Halló, a fia iskolájából telefonálok. Van egy kis számítógépes problémánk.
(2) – Ó, csak nem elrontott valamit?
- Tulajdonképpen igen.
(3) – Tényleg Robert’); DROP TABLE Students; — a fia neve?
- Ó igen, kis Bobby Tables-nek hívjuk itthon.
(4) – Nos, elveszítettük a diákjaink idei adatait. Most remélem örül.
- És remélem, hogy megtanulták, hogy megvizsgálják az adatbázis inputokat.

Aki esetleg nem tudná, hogy mi az az SQL injection, annak pár sorban:
Continue reading ‘Az SQL injection’ »

(English version here)
Időnként megesik, hogy valaki nem tud belogolni egy SQL Serverbe, mert elgépeli a jelszavát, nincs joga, stb. Ez nem olyan nagy gond egészen addig, amíg tudjuk, hogy miért nem tud belogolni. De mi van, ha valaki azt mondja, hogy nem sikerült neki, 101%, hogy jó jelszóval próbálkozik, jó szerverre, jó felhasználónévvel, jó adatbázisba, minden jó, csak éppen nem tud belépni? A legegyszerűbb megoldás a nagy kedvencem, az errorlog elolvasása, már megint. Amennyiben a szerveren be van állítva a sikertelen belépési kísérletek naplózása (úgy emlékszem ez a default), akkor az errorlogba is bekerül az az üzenet, amit a kliensnek küld a szerver:

Msg 18456, Level 14, State 1, Server DEMOSQL1, Line 1
Login failed for user ‘kiscsillag’

Illetve igazából nem is ez kerül a logba, hanem valami jobb, de mielőtt ebbe belemegyek, értelmezzük az első sorát az üzenetnek, vagyis az error három numerikus argumentumát: Az első az error number, ez a hiba egyedi azonosítója, a gépek mindig jobban szeretik a számokat, mint a dumát. A második a level vagy severity, azaz mennyire gáz az adott hiba. Minél nagyobb a szám, annál rosszabb a helyzet, severity 20 és 25 között már az is kérdéses, hogy rendesen működik-e a szerver. Az utolsó elem a state, ami egy érdekes állat. Arról adhatunk itt információt, hogy honnan jött az error – például ha van egy általunk definiált hiba, amit több különböző helyen is használunk, akkor a state értékét használhatjuk annak a jelzésére, hogy honnan jött a hiba. És most nézzük azt az errorlogot!

2009-07-27 14:02:02.21 Logon     Error: 18456, Severity: 14, State: 8.
2009-07-27 14:02:02.21 Logon     Login failed for user 'kiscsillag'. [CLIENT: 10.10.10.1]

A különbség jól látható: az a bizonyos state érték eléggé más itt, mint a kliensoldalon volt, és ez nem a véletlen műve. Az SQL Server, ahogyan azt jó rendszertől elvárjuk, nem mond többet a kliensnek, mint amennyi szükséges, nehogy ezzel segítsen egy esetleges támadást. Viszont aki tényleg segítségre szorul, az megkeresheti a sysadminokat, akik a logban láthatják, hogy miért is nem sikerült az a login. A state kódok pedig a következőek:

Van néhány kód, ami nem olyan triviális, hogy mit is jelent, néhányról magam sem tudom :), úgyhogy néhány jellemzőt kiveséznék:

A state 16 (vagy 38/40 SQL 2008-ban) egyik leggyakoribb oka az auto close-os adatbázis. Azaz az SQL szerver becsukja az adatbázist, ha nem használták már egy ideje, és megint megnyitja, ha kell valakinek. Ez egy kiváló ötlet az MSDE-hez meg egyéb asztali gépes világba, de egyébként mindenkinek melegen ajánlom, hogy nézze meg, hogy van-e auto close-os adatbázisa, és ha van, kapcsolja ki ezt az opciót, mert csak szívás van vele. Hogy mi? Például a state 16: éppen be akar lépni a user, erre az SQL elkezdi megnyitni a DB-t neki, de amíg nem sikerül teljesen megnyitnia, addig ugye nem tudja beléptetni az adatbázisába a usert, és elbukik a login. Újrapróbálva valószínűleg jó lesz, mert addigra már kinyitja. A másik gagyi benne (és erről lehet messziről kiszúrni ezeket az adatbázisokat), hogy teleszemeteli az errorlogot “Starting up database XYZ” üzenetekkel.

A 11-12 tipikusan a Windows loginok esete: az SQL Server kiválóan azonosítja a felhasználót, de nincs neki megfelelő login létrehozva az SQL szerveren, úgyhogy emberünk kívül marad.

A blog alapját a SQL Protocol team blogja és a nyomában kialakuló diskurzus adta, mivel nem sikerül összeszedniük az összes kódot egy táblába, és ez engem baromira zavart – úgyhogy összeszedtem.

(Magyarul itt)

Many people faced the upleasant message below while trying to connect to SQL Server with SQL authentication: “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection” The most typical reason for this is that the server is configured to allow only Windows logins, not SQL logins. The cure is pretty simple, open up server properties and change the authentication option from Windows only to SQL and Windows – and there you go.

Recently, I met the error message in a different situation. A few guys couldn’t connect to a server with Windows integrated authentication. They got the error message above. Others could connect without any issue. I tried to figure out what was going on: my first candidate was MTU size but as the authentication token size was the same size (actually, it was bigger for those who had no problem), I ruled this out. Then I checked if they could connect to a file share on the machine. This action failed and I found this in the security eventlog:

Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 534
Date: 8/3/2008
Time: 3:04:30 PM
User: NT AUTHORITY\SYSTEM
Computer: SQL10
Description:
Logon Failure:
Reason: The user has not been granted the requested
logon type at this machine
User Name: erik
Domain: MYDOMAIN
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: ERIKPC

So what the heck does this mean? I asked my old pal Google, who said he read about it once, and people say it’s nothing else than network logon failure. So I decided to check the local security policy. And I found that in the User Rights Assignment node the Access this computer from the network privilege was granted only the local Administrators group. At that point I banged my head into the wall why I hadn’t found the pattern sooner and also extended this privilege to the users group as well. After a policy refresh, it worked.

Lookink back, it was pretty simple. As the peons users were not authorized to access the computer via the network, the OS tore and dropped their token so the SQL Server didn’t get anything from their identity. Once again I learnt something new.