Connecting to SQL Server through Powershell

Being a newbie to powershell, ive been researching and testing different connection options for powershell scripts. Ive come across the following ways:

      • Invoke-sqlcmd Windows Authentication
      • Invoke-Sqlcmd SQL Authentication
      • Invoke-Sqlcmd with Authentication Prompt
      • SQLConnection.connectionstring property (.NET)
      • SMO Connection with Authentication prompt
      • SMO Connection with hardcoded details

Invoke-sqlcmd Windows Authentication

Takes current windows connection details to try to do pass through authentication onto SQL Server

#Uses windows Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName } # print results $results | Format-Table -autosize

Invoke-Sqlcmd SQL Authentication

#Uses sql Authentication to traverse through instance list and return db count $filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" $results = @() foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" ` -ServerInstance $instanceName ` -U "MyUsername" -P "password" } # print results $results | Format-Table -autosize

Invoke-Sqlcmd with Authentication prompt

This option uses the get-credentials cmdlet to bring up a prompt whereby connection details can be entered. These are then passed through to a connection method.

$filelocation = "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training" $instanceNameList = Get-Content "$filelocation\ServerList.txt" #resets the variable $results = @() # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential #the backslash is regular expression to remove \ (prompt will give it as default for domain) $userName = $credential.UserName -replace("\\","") #getnetworkcredential gives the passport unencrypted $pass = $credential.GetNetworkCredential().password $db = "master" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -U $userName -P $pass -Database $db -ServerInstance $instancename ` -Query "select SERVERPROPERTY('ServerName') as Server, count(*) as 'DB Count' from sys.databases" } # print results $results | format-table -Autosize

When the script is run, a popup is presented to the user:

image

If the credentials are correct, then the script returns how many databases are in each server in the instancelist.

SQLConnection.ConnectionString Property

The connection string and the query are fed into a SQLClient.SQLDataAdapter and the output formatted

foreach ($svr in get-content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\serverlist.txt") { $con = "server=$svr;database=master;Integrated Security=sspi" $cmd = "SELECT SERVERPROPERTY('ServerName') AS Instance, SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP" $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con) $dt = new-object System.Data.DataTable $da.fill($dt) | out-null $dt | Format-Table -autosize }

SMO Connection with Authentication Prompt

# load assemblies [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, ` Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91") # connect to SQL Server named instance # use server\instancename $serverName = "Server\INSTANCE" $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName" # login using SQL authentication, which means we supply the username # and password $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential $userName = $credential.UserName -replace("\\","") $server.ConnectionContext.set_Login($userName) $server.ConnectionContext.set_SecurePassword($credential.Password) # clear the screen cls # list connection string Write-Host "--------------------------------------------------------" Write-Host "Connection String : " Write-Host $server.ConnectionContext.ConnectionString Write-Host "--------------------------------------------------------"

Advertisements

Powershell: Invoke-Sqlcmd command not recognized

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, 
script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.

When trying to run the Invoke-sqlcmd command within the powershell utility, an error is returned suggesting the command is not recognized.

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "mysqlserver"

image

This happens because the Invoke-sqlcmd cmdlet is not included as part of PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). Before using invoke-sqlcmd you should install SSMS or the SQL Server 2008 Feature Pack

When sqlps has been installed, you may then load invoke-sqlcmd into powershell.exe by using the below script from Michiel Wories blog http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx

Im still a novice at powershell so Ive ended up building the initiliaze into all my basic scripts using dot-source

# Use Dot-Source to Call the “Initialize-SQLPSEnvironment” Script. .{."\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\Initialize-SqlPsEnvironment.ps1"} $instanceNameList = Get-Content "\\shared\home\Mohsin.AliPatel0 - DBA8 - Powershell\Powershell_training\ServerList.txt" foreach($instanceName in $instanceNameList) { $results += Invoke-Sqlcmd -Query "select SERVERPROPERTY('ServerName') As ServerName, Name, crdate from sysdatabases" -ServerInstance $instanceName } # print results $results

The above script calls the initialize script, and then does a basic SQL Query