Je bent hier: ITFAQ.nl » Hoe kan ik? » Met PowerShell verbinding maken met een SQL Server-database

Met PowerShell verbinding maken met een SQL Server-database

Door /

Wil je vanuit PowerShell een query uitvoeren op een SQL Server-database? Met Invoke-Sqlcmd uit de SqlServer-module doe je dat met één opdracht. In dit artikel lees je hoe je de module installeert, verbinding maakt met Windows- of SQL Server-authenticatie, hoe je de connectiestring van een Umbraco-website uit IIS hergebruikt, en welke andere manieren er zijn om vanuit PowerShell met SQL Server te verbinden.

De SqlServer-module installeren

Invoke-Sqlcmd zit in de PowerShell-module SqlServer. Die installeer je vanuit de PowerShell Gallery:

Install-Module -Name SqlServer -Scope CurrentUser

Met -Scope CurrentUser heb je geen beheerdersrechten nodig: de module wordt alleen voor jouw account geïnstalleerd.

Verbinding maken en een query uitvoeren

Op de SQL Server zelf, en als je bent ingelogd met een Windows-account dat toegang heeft tot SQL Server, is één regel voldoende. De punt (.) bij -ServerInstance staat voor de standaardinstantie op de lokale computer:

Invoke-Sqlcmd -ServerInstance . -Encrypt Optional -Query "select name from sys.databases"
# Invoke-Sqlcmd -ServerInstance . -Encrypt Optional -Query "select name from sys.databases where name not in ('master','tempdb','model','msdb', 'admin')"

De eerste opdracht toont alle databases op de server. De tweede, uitgecommentarieerde, regel laat de systeemdatabases weg.

Inloggen met een SQL Server-gebruiker

Maak je verbinding met een SQL Server-gebruiker in plaats van je Windows-account, geef dan ook de servernaam, de database en de inloggegevens mee. Met Get-Credential voorkom je dat het wachtwoord in je script of in je PowerShell-geschiedenis terechtkomt:

$cred = Get-Credential
Invoke-Sqlcmd -ServerInstance sql.example.com -Database voorbeeld -Credential $cred -Encrypt Optional -Query "SELECT @@VERSION"

Waarom -Encrypt Optional?

Sinds versie 22 van de SqlServer-module versleutelt Invoke-Sqlcmd de verbinding standaard. Heeft je SQL Server geen certificaat dat jouw computer vertrouwt, dan krijg je een certificaatfout en komt er geen verbinding. Met -Encrypt Optional mag de verbinding ook onversleuteld zijn. Met -TrustServerCertificate blijft de verbinding versleuteld, maar wordt het certificaat niet gecontroleerd.

Gebruik -Encrypt Optional alleen binnen je eigen, vertrouwde netwerk. Het veiligst is een geldig certificaat op de SQL Server, zodat de verbinding altijd versleuteld is. Lees ook hoe je de SQL Server-verbinding van Umbraco beveiligt met SSL.

De connectiestring van een Umbraco-website uit IIS gebruiken

Beheer je meerdere Umbraco-websites in IIS, dan staan de databasegegevens al in de umbracoDbDSN-connectiestring in het web.config-bestand van iedere website. Het onderstaande script loopt alle websites in IIS langs, leest bij de websites waarvan de naam eindigt op Website die connectiestring uit, en voert er een query mee uit:

Import-Module WebAdministration

Get-Website | % {
    Write-Host "found" $_.Name
    if ($_.Name -like '*Website') {
        $connectionString = (get-WebConfiguration "IIS:\Sites\$($_.Name)" -filter "connectionStrings/add[@name='umbracoDbDSN']")

        $sb = New-Object System.Data.Common.DbConnectionStringBuilder
        $sb.set_ConnectionString($connectionString.connectionString)

        $result = (Invoke-Sqlcmd -ServerInstance $sb.server -Database $sb.database -Query "SELECT * FROM [TABLE]" -Username $sb['user id'] -Password $sb.password -Encrypt Optional)
        if ($result) {
            Write-Warning ($result | Format-Table | Out-String)
        }
        else {
            Write-Host "no bad results"
        }
    }
}

Een paar aantekeningen bij het script:

  • Vervang [TABLE] door je eigen query. Het script toont een waarschuwing met het resultaat als de query rijen teruggeeft, en anders “no bad results”. Zo kun je bijvoorbeeld alle websites in één keer controleren op een bepaalde instelling of ongewenste inhoud.
  • System.Data.Common.DbConnectionStringBuilder splitst de connectiestring in losse onderdelen, zodat je server, database, user id en password kunt meegeven aan Invoke-Sqlcmd.
  • De module WebAdministration heeft beheerdersrechten nodig om de IIS-configuratie te lezen. Start PowerShell dus als beheerder, of controleer in je script of het als administrator wordt uitgevoerd.

Zonder module: .NET SqlClient

Mag of kun je geen modules installeren, bijvoorbeeld op een afgeschermde server? Dan gebruik je de SqlClient-klassen uit .NET rechtstreeks. System.Data.SqlClient is standaard beschikbaar in Windows PowerShell 5.1 en in PowerShell 7:

$connectionString = "Server=sql.example.com;Database=voorbeeld;Integrated Security=True;Encrypt=True;TrustServerCertificate=True"
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$command = $connection.CreateCommand()
$command.CommandText = "SELECT name FROM sys.databases"

$table = New-Object System.Data.DataTable
$connection.Open()
$table.Load($command.ExecuteReader())
$connection.Close()

$table

Met Integrated Security=True log je in met je Windows-account. Gebruik je een SQL Server-gebruiker, vervang dat dan door User ID=gebruiker;Password=wachtwoord. Ook hier bepalen Encrypt en TrustServerCertificate hoe de verbinding wordt versleuteld, net als bij Invoke-Sqlcmd. Microsoft ontwikkelt System.Data.SqlClient niet meer verder; de opvolger is Microsoft.Data.SqlClient, die onder andere met de SqlServer-module wordt meegeleverd.

Nog meer mogelijkheden: dbatools, ODBC en OLE DB

Beheer je regelmatig SQL Server-instanties, kijk dan eens naar de communitymodule dbatools. Met Invoke-DbaQuery voer je net zo eenvoudig een query uit als met Invoke-Sqlcmd, en daarnaast bevat dbatools honderden cmdlets voor bijvoorbeeld back-ups, logins en migraties:

Install-Module -Name dbatools -Scope CurrentUser
Invoke-DbaQuery -SqlInstance sql.example.com -Database voorbeeld -Query "SELECT @@VERSION"

Werk je al met ODBC- of OLE DB-connectiestrings, bijvoorbeeld vanuit een oudere (classic ASP-)website? Dan kun je die in PowerShell ook gebruiken, met System.Data.Odbc.OdbcConnection of System.Data.OleDb.OleDbConnection. Welke connectiestring je daarvoor nodig hebt, lees je in ODBC en OLE DB connectiestrings voor SQL en MySQL databases.

Conclusie

Met Invoke-Sqlcmd voer je vanuit PowerShell eenvoudig queries uit op SQL Server, met je Windows-account of een SQL Server-gebruiker. Krijg je een certificaatfout, kijk dan naar -Encrypt Optional of -TrustServerCertificate. En door de connectiestrings uit IIS te hergebruiken, controleer je in één keer de databases van al je Umbraco-websites. Kun je geen modules installeren, dan heb je met .NET SqlClient alles al aan boord.

Gerelateerde berichten

Scroll naar boven