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:

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 "--------------------------------------------------------"

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"

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