Connect to SQL Server VIA PowerShell

Use the following code below to connect to SQL Server 2008 R2.  This will text connectivity over SSPI/Integrated Authentication.  You can also define the port number and display the results in the powershell window.  Remember to press “Enter” once the count of records is returned.

This is very helpful when troubleshooting connection issues, it can help you rule out issues with Domains/Trust/Permissions/Kerberos/SPN’s etc…
———-start of first powershell script ————
$Table = new-object System.Data.DataTable $sqlConn = new-object System.Data.SqlClient.SqlConnection(“Data Source=sqlsrv1;Initial Catalog=db1;Integrated Security=True”) $adapter = new-object System.Data.SqlClient.SqlDataAdapter(“Select top 2 * from  sys.tables”,$sqlConn) $adapter.Fill($Table) write-output $table
———-end of first powershell script ————

 

———-start of second powershell script ————
$Table = new-object System.Data.DataTable $sqlConn = new-object System.Data.SqlClient.SqlConnection(“Data Source=sql-01,443000;Initial Catalog=DB1;Integrated Security=True”) $adapter = new-object System.Data.SqlClient.SqlDataAdapter(“Select top 2 * from  sys.columns”,$sqlConn) $adapter.Fill($Table) write-output $table
———-end of first powershell script ————

 

Leave a Reply