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

One Response to Connecting to SQL Server through Powershell

  1. Pingback: Powershell – Retrieve SQL Server Info for all my Servers « Mohsin's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: