Query SQL Server from PowerShell without the SQL module or snapin

There are several different ways to query a SQL Server from PowerShell but most often you’ll find that they’re dependent on the SQL PowerShell module or snapin.

To eliminate that dependency, you can query a SQL Server from PowerShell using the .NET framework. There are several different options to accomplish this including using a DataReader or a DataSet and there are plenty of tutorials on those topics already on the Internet so there’s no reason to duplicate that information here. Most of the tutorials are developer focused, but you should be able to translate them to PowerShell easy enough.

The PowerShell function shown in the following example uses a DataReader along with a DataTable to retrieve the information from SQL Server and display it in PowerShell. One Transact-SQL (TSQL) select statement can be piped into this function or provided via parameter input. The function also includes comment based help and error handling. Either a trusted database connection or SQL authentication can be used.


For more information and options on querying SQL Server using the .NET framework, see the DataAdapters and DataReaders, DataTable Class, and DataSet Class topics on MSDN.

The Invoke-MrSqlDataReader PowerShell function shown in this blog article can be downloaded as part of my MrSQL PowerShell module on GitHub.



  1. Michael West

    Cool article Mike. I’ll have to take your script for spin.

    I wrote something similar not long ago. https://github.com/michaellwest/PowerShell-Modules/blob/master/CorpApps/Invoke-SqlCommand.ps1

  2. Bryan Cafferky

    Nice function. Works great! Thanks.

  3. Pedro, O Moleiro


    Nice Post, Very Simple.

    I changed your code in order to save the output to XML. This feature can answer to a lot for problems related to the size of the output.

    It’s just a little change, i will keep you as the only author.
    Many thanks.

    Pedro, O Moleiro

    function Invoke-MrSqlXmlDataReader {

    param (
    [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
    [Parameter( Mandatory=$false)]
    [string]$XmlOutputFileName = [System.IO.Path]::GetTempFileName()+”.xml”

    BEGIN {
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    if (-not($PSBoundParameters.Credential)) {
    $connectionString = “Server=$ServerInstance;Database=$Database;Integrated Security=True;”
    else {
    $connectionString = “Server=$ServerInstance;Database=$Database;Integrated Security=False;”
    $userid= $Credential.UserName -replace ‘^.*\\|@.*$’
    ($password = $credential.Password).MakeReadOnly()
    $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password)
    $connection.Credential = $sqlCred
    $connection.ConnectionString = $connectionString
    $ErrorActionPreference = ‘Stop’
    try {
    Write-Verbose -Message “Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened.”
    catch {
    Write-Error -Message “An error has occurred. Error details: $($_.Exception.Message)”
    $ErrorActionPreference = ‘Continue’
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $ErrorActionPreference = ‘Stop’
    try {
    $result = $command.ExecuteXmlReader()
    catch {
    Write-Error -Message “An error has occured. Error Details: $($_.Exception.Message)”
    $ErrorActionPreference = ‘Continue’
    if ($result) {
    if($XmlOutputFileName) {
    Write-Output “Creating file “+ $XmlOutputFileName + “with the output”
    $encoding = [System.Text.Encoding]::UTF8
    $writer = New-Object System.Xml.XmlTextWriter( $XmlOutputFileName, $encoding )
    $writer.WriteNode($result, ‘true’)
    Write $result.ReadOuterXml()
    END {

  4. Mike

    Nice! is there a way to make multiple queries by using one connection? I have tried to use the following script but it didn’t work (it returned the first query only):

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = “Server=localhost;Database=master;Integrated Security=True”
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = @”
    select host_name()
    select @@version
    $SqlCmd.Connection = $SqlConnection
    $results = $SqlCmd.ExecuteReader()
    while ($results.Read())

    Many thanks

  5. Marc Rice

    Love this function, but I’m having problems using it with the credential parameter. I can run the command successfully from a alternate user PowerShell prompt, but not using the same credentials with get-credential and the credential parameter. I get access denied errors even though it works from the runas prompt. Any ideas?


Leave a Reply

%d bloggers like this: