Rechten op uitsluitend een secondary database

Posted on by

In log shipping omgevingen bestaat regelmatig de wens om een (service-)account
uitsluitend leesrechten te geven op de secondary database, terwijl dit account
geen toegang mag hebben tot de primary database.
Typische use-cases zijn rapportages, exports of monitoring die bewust buiten
de productieomgeving worden gehouden.

SQL Server Log Shipping leent zich hier goed voor, omdat een log shipping secondary
geen automatische failover kent. Een eventuele promotie van de secondary
naar primary gebeurt altijd handmatig en bewust, wat deze inrichting
goed beheersbaar maakt.

Uitgangspunten

  • Log Shipping is een Disaster Recovery mechanisme, geen High Availability oplossing
  • De secondary database staat in STANDBY / READ ONLY modus
  • Er vindt geen automatische rolwissel plaats tussen primary en secondary
  • Deze inrichting is bedoeld voor leesgeoriënteerde, niet-kritische workloads

Technische achtergrond

Bij SQL Server Log Shipping gelden de volgende technische eigenschappen:

  • Database-objecten en database-rechten worden doorgezet via transaction log restores
  • Logins zijn server-level objecten en worden niet meegenomen in log shipping
  • Toegang wordt bepaald door:
    • de status van de login (enabled / disabled)
    • de aanwezigheid van een database user

Dit onderscheid maakt het mogelijk om toegang functioneel te beperken tot uitsluitend
de secondary database.

De inrichting

Stap 1 – Maak een Active Directory service account aan

Gebruik één vast AD-account dat bedoeld is voor rapportage- of leesdoeleinden.

Stap 2 – Maak de login aan op beide SQL Servers

Maak dezelfde login aan op:

  • De primary SQL Server instance
  • De secondary SQL Server instance

Omdat het om een AD-account gaat, is de SID op beide servers gelijk.

Stap 3 – Maak een database user aan in de primary database

  • Koppel de database user aan de login
  • Geef uitsluitend db_datareader rechten

Deze database-rechten worden via log shipping automatisch doorgezet naar
de secondary database.

Stap 4 – Disable de login op de primary SQL Server

Door de login te disablen op de primary SQL Server:

  • kan het account niet verbinden met de primary instance
  • blijven de database-rechten wel bestaan

Stap 5 – Laat de login enabled op de secondary SQL Server

Op de secondary SQL Server blijft de login enabled, waardoor:

  • verbinding met de secondary database mogelijk is
  • alleen leesacties kunnen worden uitgevoerd

Resultaat

  • Het account heeft leesrechten op de secondary database
  • Het account kan niet verbinden met de primary database
  • Rapportage- en leesprocessen zijn gescheiden van productie
  • De inrichting blijft overzichtelijk en beheersbaar

Beheer en aandachtspunten

Wanneer een log shipping secondary handmatig wordt gepromoveerd tot primary
(bijvoorbeeld in een DR-situatie), is het belangrijk om:

  • de status van de login opnieuw te beoordelen
  • expliciet te bepalen of het account toegang mag behouden

Omdat dit een bewuste beheeractie is, kan dit eenvoudig worden opgenomen
in bestaande DR-procedures.

Conclusie

Binnen een log shipping omgeving is het technisch en operationeel goed
verdedigbaar om een account uitsluitend toegang te geven tot de secondary database
door het combineren van database-rechten met een gecontroleerde login-status.

Deze oplossing past bij de aard van log shipping als DR- en reporting-mechanisme
en biedt een praktische en veilige scheiding tussen productie en leesworkloads,
zonder de complexiteit van Always On Availability Groups.

Belangrijke security update voor Microsoft SQL Server

Posted on by

Microsoft heeft twee belangrijke kwetsbaarheden in de beveiliging binnen Microsoft SQL Server gevonden en deze twee issues met een security update gedicht. Het gaat onder andere om een security update binnen de SQL Server Agent, waarbij met behulp van SQL Injectie een aanvaller sysadmin-rechten op de database server zou kunnen verkrijgen.

Er is bovendien met deze security update een lek binnen linked servers gedicht. SQLTeam.NL adviseert uit performance overwegingen nooit linked servers te gebruiken en data op andere manieren binnen data platformen te verplaatsen; bijvoorbeeld door gebruik van SSIS packages te maken.

De kwetsbaarheden zijn in deze link (in het Engels) beschreven. Het artikel bevat ook de links naar alle downloads voor elke versie van SQL Server. Ik adviseer deze update zo spoedig mogelijk in acceptatie te testen en vervolgens in productie te nemen.

SQL Server query’s op secondaries zijn langzaam

Posted on by

Tijdens één van mijn leertrajecten op SQL Server onderzocht ik het gedrag van query’s op READ ONLY secondaries binnen Always-On clusters. Wat opviel was dat query’s op deze database langzamer draaiden; dus, dezelfde query draait op de primary sneller. Nu gebruik ik in het dagelijks werkleven meer secondary logshipomgevingen, waardoor mijn werk niet direct wordt geraakt, maar als SQL Server Enthousiast werd mijn interesse behoorlijk geprikkeld.

Bij mijn zoektocht op internet, kwam ik gelukkig weer bij Microsoft uit. Dit artikel verklaarde precies de exacte oorzaak van mijn testsituatie.

Hoe zie ik dat het fout gaat?

  • De query voert een index scan uit op een groot gedeelte van een tabel, die een clustered row-store index hebben.
  • De query maakt gebruik van de NOLOCK query hintDe dynamic management view sys.dm_db_index_physical_stats laat een significante fragmentatie van de index zien
  • Loskoppelen van de secondary uit het Always-On Cluster lost de performance kwestie op

Hoe zit dit nu? Wat is de oorzaak?
Op secondary databases binnen een always-on cluster wordt snapshot isolation bij query’s afgewdongen. De optie NOLOCK wordt daarbij genegeerd. Dit zorgt ervoor dat de index scan op volgorde van de index key door SQL Server wordt uitgevoerd Als de clustered index significant gefragmenteerd is, valt SQL Server terug op het lezen van één pagina per IO request. Op de primary database (waar snapshot isolation niet wordt afgedwongen) zal SQL Server nog steeds terugvallen op het lezen van meerdere pagina’s per IO request.

De oplossing?
Om dit probleem voor te blijven, is het verstandig de clustered index van de betrokken tabellen opnieuw op te bouwen. De nieuw opgebouwde indexen zullen dan via de Always-On naar de secondaries worden overgezet, waardoor de query’s net zo snel als op de primary database worden.

De Featured Image komt van deze pagina.

ASUS TUF Gaming AX4200 verbindt niet of selectief

Posted on by

Er is door mij heel lang gezocht naar een oplossing van verbindingsproblemen tussen een Lenovo Tinkpad P52 en een ASUS TUF Gaming AX4200. De Lenovo Thinkpad heeft een WiFi adapter die draait onder Intel(R) Wi-Fi 6 AX200 160Mhz driver. De notebook maakt wél verbinding met andere WiFi netwerken.

Na vele opties te hebben onderzocht inclusief wijzigingen maken in de instellingen van de driver, bleek de oplossing in de router te liggen. Niet zo verwonderlijk ook, want de notebook verbinden met andere WiFi punten lukte wel. Wat werkte, was het volgende

  • Maak verbinding met de ASUS router
  • Login met de credentials van de administrator
  • Scroll aan de linkerkant in het menu naar Wireless onder Advanced
  • Scroll naar Channel bandwidth onder 5GHz (!!)
  • Vink Enable 160MHz aan
  • Scroll naar de onderkant van de pagina en klik op [Apply]

De notebook zou nu met 5GHz met de router kunnen verbinden. Let op: verbinding met 2.4GHz lukte bij mij nog steeds niet.

Afbeelding [copyright by Asus]

Windows 11 Notepad werkt niet in context menu

Posted on by

Stel u wenst XML documenten te openen met de nieuwe Notepad van Windows 11, maar… u krijgt deze op geen enkele manier via ‘Openen met…’ (in het Engels ‘Open with…’) in uw context menu (met rechtermuis klik). De app wordt niet gevonden en handmatig toevoegen geeft allerlei foutmeldingen. Wat voor mij werkte, was de app te herstellen (in het Engels ‘Repair’). Hoe?

  • Open Instellingen (in het Engels Settings)
  • Klik in het menu op Apps
  • Klik op Installed apps
  • Zoek de app Notepad op
  • Klik op de drie puntjes rechts naast de grootte van de app
  • Selecteer Advanced options
  • Klik op de knop Repair
Hierna zal Notepad waarschijnlijk in uw context menu beschikbaar zijn, wanneer u in verkenner ‘Openen met…’ selecteert.

Wijzig Analysis Server van Multidimensional naar Tabular Model of andersom

Posted on by

Jarenlang zijn multi-dimensionale kubussen de standaard geweest. Uw organisatie is er nu klaar voor om tabular models in te zetten. Daarvoor moet de analysis server worden omgezet van een multidimensional instance naar een tabular instance. Hoe werkt dat?

Microsoft beschrijft in deze link dat het omzetten van de DeploymentMode property in het bestand msmdsrv.ini niet wordt ondersteund. In principe zal Microsoft SQL Server Analysis Service opnieuw op de server moeten worden geïnstalleerd, nadat de multidimensional instance is verwijderd, maar…

Het omzetten van de DeploymentMode property in het bestand msmdsrv.ini werkt wél. Voor een test- of acceptatieomgeving of om tabular models tijdelijk te testen is het omzetten van deze eigenschap dus een prima oplossing die veel tijd scheelt. Hoe werkt het precies?

  • Ga met verkenner op de analysis server naar de folder C:\Program Files\Microsoft SQL Server\[uw versie]\OLAP\Config
  • Maak een kopie van het bestand msmdsrv.ini naar bijvoorbeeld de temp-folder
  • Open het bestand in de temp-folder
  • Zoek in het bestand naar DeploymentMode
  • Wijzig de waarde naar behoefte: 0=Multidimensional en 2=Tabular
  • Sla het bestand op
  • Maak een backup van het originele bestand in de SSAS folder
  • Kopieer vervolgens het aangepaste bestand uit de temp-folder naar de SSAS-folder toe
  • Herstart de analysis server
BELANGRIJK: Het is dus nadrukkelijk niet de bedoeling deze werkwijze op een productie-server te gebruiken. Microsoft ondersteunt deze werkwijze gewoon niet.

Fouten in SQL Server Agent taken worden niet via e-mail gestuurd

Posted on by

Wanneer zich fouten voordoen in SQL Agent taken, is het fijn wanneer deze via e-mail worden gedeeld. Bij u blijkt dat een uitdaging en werkt het niet.

  • De SQL Server heeft voldoende rechten om e-mail via de mail-server te kunnen sturen
  • Er is een mail account in Database Mail binnen de SQL Server aangemaakt
  • Er is een profiel voor het mail account binnen Database Mail aangemaakt
  • Een testbericht vanuit Database Mail van de SQL Server komt ook gewoon aan
… en helaas: er komen geen mailberichten van de SQL Server Agent binnen.

De eerste plaats waar u kijkt, is zonder uitzondering de logbestanden! In dit geval dus het actuele logbestand van de SQL Server Agent. De kans is vrij groot dat u in de log de volgende regel tegenkomt: [476] Database Mail is not enabled for agent notifications. Cannot send e-mail to [E-mail ontvangers].

Database Mail moet nog aan de SQL Server Agent worden gekoppeld en dat gaat als volgt:

  • Start SQL Server Management Studio
  • Maak verbinding met de betrokken SQL Server
  • Rechtermuisklik op SQL Server Agent
  • Klik op Properties
  • Klik links in het geopende venster op Alert System
  • Vink Enable mail profile aan
  • Selecteer het juiste Mail system
  • Selecteer de het juiste Mail profile
  • Klik op [OK] om de nieuwe instellingen te bevestigen
Klaar!

Foutmeldingen in transactie logshipping

Posted on by

Ik kom regelmatig situaties tegen waarbij logshipdatabases (=secondaries) niet meer via logshipping wordt bijgewerkt. Meestal zien we dan in de geschiedenis van de LSRestore-job in de SQL Agent van de database server waar de logshipdatabase op draait een melding die aangeeft dat transactielogs niet meer kunnen worden ingelezen omdat ze ‘out-of-sequence’ of ‘too recent’ zijn. Deze situatie wordt meestal door één van onderstaande scenario’s veroorzaakt:

  • Er is een FULL backup van de database gemaakt zonder de optie COPY_ONLY.
  • Het recovery model is aangepast. Het aanpassen van het recovery model wordt in de SQL Server gelogd als Setting database option RECOVERY to SIMPLE for database ‘[naam van de database]’ (zonder blokhaken).
  • Er heeft een RESTORE plaats gevonden waarbij de database in NonRecovery is gezet
  • Er zijn transactielogs van de brondatabase verwijderd of naar een andere locatie gekopieerd. Wanneer deze transactielogs nog ergens zijn te vinden, kan worden volstaan met het plaatsen van de transactielogs in de folder van waaruit de LSRestore-job de transactielogs inleest. Voor de veiligheid worden deze transactielogs uit de bronfolder gekopieerd en niet verplaatst.

De gebruikte query om inzicht te krijgen in uitgevoerde restore jobs is

SELECT
       rh.destination_database_name AS [Database],
       CASE
             WHEN rh.restore_type = 'D' THEN 'Database'
             WHEN rh.restore_type = 'F' THEN 'File'
             WHEN rh.restore_type = 'I' THEN 'Differential'
             WHEN rh.restore_type = 'L' THEN 'Log'
       ELSE rh.restore_type END AS [Restore Type],
       rh.restore_date AS [Restore Date],
       bmf.physical_device_name AS [Source], 
       rf.destination_phys_name AS [Restore File],
       rh.user_name AS [Restored By]
FROM msdb.dbo.restorehistory rh
INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
-- WHERE destination_database_name = '[naam van de database]' -- zonder blokhaken
ORDER BY rh.restore_date DESC
GO

De gebruikte query voor inzicht in gemaakte backups is:

SELECT
       s.database_name,
       m.physical_device_name,
       CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
       CAST(DATEDIFF(second, s.backup_start_date,
       s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
       s.backup_start_date,
       CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
       CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
       CASE s.[type] WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Differential'
       WHEN 'L' THEN 'Transaction Log'
       END AS BackupType,
       s.server_name,
       s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date;

In de meeste gevallen dient de logshipdatabse opnieuw te worden opgebouwd. Alleen wanneer de melding ‘too recent’ zich voordoet en de transactielogs die ontbreken zijn nog beschikbaar, kan de logshipdatabase met de ontbrekende transactielogs worden hersteld.

Inzicht in active directory van een domein

Posted on by

Om inzicht te krijgen in de active directory structuur of AD groups van een domein binnen Windows 10 wordt het volgende gedaan:

  • Open de Windows Verkenner
  • Selecteer netwerk (in het Engels network) aan de linkerkant
  • Klik in de knoppenbalk op “Search Active Directory”

Om inzicht te krijgen in de active directory structuur of AD groups van een domein binnen Windows 11 wordt het volgende gedaan:

  • Open Windows Tools
  • Selecteer netwerk (in het Engels network) aan de linkerkant
  • Klik in de knoppenbalk op “Search Active Directory”

Vervolgens kan de AD groep worden opgezocht om te kijken welke gebruikers er in die AD groep zitten.