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