Powershell and SQL Server – 2- Connect to SQL Server Instance

There are different connection options for powershell scripts to connect to SQL Server

  • Invoke-sqlcmd Windows Authentication
  • Invoke-Sqlcmd SQL Authentication
  • Invoke-Sqlcmd with User Login Authentication Prompt
  • SQLConnection.connectionstring property
  • SMO Connection

Invoke-sqlcmd Windows Authentication

if (-not (Get-Module SQLPS)) 
    {
    Import-Module SQLPS
    } 
$result=Invoke-Sqlcmd -ServerInstance LENOVO-PC\SQL2016 -Database master -Query "select @@servername as ServerName" -QueryTimeout 0
$result

Invoke-Sqlcmd SQL Authentication

if (-not (Get-Module SQLPS)) 
    {
    Import-Module SQLPS
    } 

#Get list of Database's on a sql instance

$results=Invoke-Sqlcmd -ServerInstance LENOVO-PC\SQL2016 -Database master -Query "select name from sys.sysdatabases" -Username test1 -Password 123 -QueryTimeout 0
$results.name

Invoke-Sqlcmd with User Login Authentication Prompt

if (-not (Get-Module SQLPS)) 
    {Import-Module SQLPS} 

$credential = Get-Credential
$userName = $credential.UserName

#getnetworkcredential gives the passport unencrypted
$pass = $credential.GetNetworkCredential().password 

$result=Invoke-Sqlcmd -ServerInstance $servername -Database master -Query "select name from sys.sysdatabases" -Username $userName -Password $pass -QueryTimeout 0
$result.name

powershell_1

SQLConnection.connectionstring property

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=LENOVO-PC\SQL2016;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SET NOCOUNT ON ; select name from sys.sysdatabases; SET NOCOUNT OFF;"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

SMO Connection

$serverName="LENOVO-PC\SQL2016"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$server.Databases |Format-Table -AutoSize

Leave a Reply

Your email address will not be published. Required fields are marked *