This project is read-only.

Powershell SQL Server Connection Test

Overview

SqlConnectionTest.ps1 is a command-line Powershell utility script to test SQL Server network connectivity and connection strings independently of application code. This facilitates troubleshooting client connection problems even if tools like SQL Server Management Studio and TELNET are not available on the client machine.

SqlClient, ODBC, and OLE DB connection strings can be tested. ProviderName must be specified to test an ODBC or OLE DB connection string. SqlClient (.Net Provider for SQL Server) is assumed if connection string is not specified.

Usage:

PowerShell SqlConnectionTest.ps1 [-HostName <host-name>] [-Port <Port-number>] [-InstanceName <instance-name>] [-ConnectionString <connection-string>] [-ProviderName <provider-name>] [-TestHost] [-TestPort] [-TestSqlServerBrowser] [-TestConnectionString] [-TestSqlServerServiceStatus]

Parameters:

-TestHost (optional, default false): Perform host TCP/IP connectivity checks. HostName must be supplied. Test TCP/IP connectivty by performing the following actions:
  1. resolve the specified HostName or IP address using GetHostEntry to validate the host name or IP address is valid
  2. list IP address(es) returned by GetHostEntry (if Verbose is specified)
  3. ping using the specified HostName or IP address to test ICMP connectivity

-TestPort (optional, default false): Perform a TCP socket connection test. HostName must be supplied. TCP socket connectivity is tested by performing the following actions:
  1. if Port is specified, attempt a TCP socket connection to the specified Port on HostName
  2. if Port is not specified and InstanceName is specified, retrive port for specified InstanceName using a SQL Server Brower query, and then attempt a TCP socket connection to the returned named instance port on HostName
  3. if neither Port nor InstanceName is specified, attempt a TCP socket connection to the default port 1433 on HostName
-TestSqlServerBrowser (optional, default false): Perform a SQL Server Browser service query. HostName must be specified. Query SQL Server Browser service to retrieve information for named instances by performing the following actions:
  1. if InstanceName is specified, get named instance information for specified instance by querying the SQL Browser service on UDP port 1434 of the specified HostName
  2. if InstanceName is not specified, get information for all named instances by querying the SQL Browser service on UDP port 1434 of the specified HostName
-TestSqlServerServiceStatus (optional, default false): Query status of SQL Server service on specified HostName to verify services is Running. The queried service name is "MSSQLSERVER" (default instance) if InstanceName is not specified, or "MSSQL$<InstanceName>" when InstanceName is specified.
This test requires appropriate permissions to view SQL Server services.

-TestConnectionString (optional, default false): Perform a connection string test. Test SQL Server connection and query by performing the following actions:
  1. Open a connection to SQL Server using the specified connection string and provider. The connection will be made using either SqlClient, ODBC or OLEDB according to the ProviderName specification.
Note that connection string keywords vary by provider. See usage examples for sample connection strings.
  1. Execute query "SELECT @@SERVERNAME;" and display result
  2. Close the connection
-HostName (optional, no default): Required for all tests except TestConectionString. Value may be either host name or IP address.

-Port (optional, no default): Required for TestPort, unless InstanceName is specified (which isses port returned by SQL Server Browser).

-InstanceName (optional, no default): Used in the following tests:

TestSqlServerBrowser to return information for a specific named instance

TestPort to determine named instance port number when Port parameter is not specified

-ConnectionString (optional, no default): Required for TestConnectionString.

-ProviderName (optional, default "SqlClient"): Name of provider associated with ConnectionString. Used only when ConnectionString is specified. Valid values are SqlClient, ODBC, or OLEDB.

-Verbose (optional, default false): Include informational messages for each test.

Usage Examples:

Resolve host name, list IP address(es), and ping host:
Powershell -File .\SqlConnectionTest.ps1 -HostName someserver -verbose

Resolve IP address, list IP address(es), and ping host:
Powershell -File .\SqlConnectionTest.ps1 -HostName 10.11.12.13 -verbose

Resolve host name, list IP address(es), ping host, and verify Port connectivity to default 1433 Port:
Powershell -File .\SqlConnectionTest.ps1 -TestHost -TestPort -HostName someserver -Port 1433 -verbose

Resolve host name, list IP address(es), ping host, list named instance information, and verify Port connectivity to named instance Port:
Powershell -File .\SqlConnectionTest.ps1 -HostName someserver -InstanceName SQLEXPRESS -verbose

Resolve host name, list IP address(es), ping host, and get default instance SQL Server service status:
Powershell -File .\SqlConnectionTest.ps1 -HostName someserver -GetSqlServerServiceStatus -verbose

Resolve host name, list IP address(es), ping host, list named instance information, verify Port connectivity to named instance Port, and get named instance SQL Server service status:
Powershell -File .\SqlConnectionTest.ps1 -HostName someserver -InstanceName SQLEXPRESS -GetSqlServerServiceStatus -verbose

Test SqlClient connection to default database with Windows authentication:
Powershell -File .\SqlConnectionTest.ps1 -ConnectionString "Data Source=someserver;Integrated Security=SSPI" -verbose

Test SqlClient connection to specific database with Windows authentication:
Powershell -File .\SqlConnectionTest.ps1 -ConnectionString "Data Source=someserver;Integrated Security=SSPI;Initial Catalog=MyDatabase" -verbose

Test SqlClient connection to specific database with SQL authentication:
Powershell -File .\SqlConnectionTest.ps1 -ConnectionString "Data Source=someserver;User Id=MyLogin;Password=MyLoginP@assw0rd;Initial Catalog=MyDatabase" -verbose

Test ODBC connection string using legacy WDAC SQL Server ODBC driver:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "Driver={SQL Server};Server=someserver;Trusted_Connection=Yes" -verbose

Test ODBC connection string using SQL Server 2005 Native Client ODBC driver:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "Driver={SQL Server Native Client};Server=someserver;Trusted_Connection=Yes" -verbose

Test ODBC connection string using SQL Server 2008 Native Client ODBC driver:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "Driver={SQL Server Native Client 10.0};Server=someserver;Trusted_Connection=Yes" -verbose

Test ODBC connection string using SQL Server 2012 Native Client ODBC driver:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "Driver={SQL Server Native Client 11.0};Server=someserver;Trusted_Connection=Yes" -verbose

Test ODBC connection string using SQL Server ODBC Driver 11:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "Driver={ODBC Driver 11 for SQL Server};Server=someserver;Uid=MyLogin;Pwd=MyLoginP@assw0rd" -verbose

Test ODBC connection string using a DSN with Windows authentication:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "DSN=SomeServerODBCDataSourceName" -verbose

Test ODBC connection string using a DSN with SQL authentication:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName ODBC -ConnectionString "DSN=SomeServerODBCDataSourceName;Uid=MyLogin;Pwd=MyLoginP@assw0rd" -verbose

Test OLE DB connection string using legacy SQLOLEDB provider:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName OLEDB -ConnectionString "Provider=SQLOLEDB;Data Source=someserver;Integrated Security=SSPI" -verbose

Test OLE DB connection string using SQL Server 2005 Native client provider:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName OLEDB -ConnectionString "Provider=SQLNCLI.1;Data Source=someserver;Integrated Security=SSPI" -verbose

Test OLE DB connection string using SQL Server 2008 Native client provider:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName OLEDB -ConnectionString "Provider=SQLNCLI10.1;Data Source=someserver;Integrated Security=SSPI" -verbose

Test OLE DB connection string using SQL Server 2012 Native client provider:
Powershell -File .\SqlConnectionTest.ps1 -ProviderName OLEDB -ConnectionString "Provider=SQLNCLI11.1;Data Source=someserver;Integrated Security=SSPI" -verbose

Last edited Oct 3, 2014 at 1:21 PM by guzmanda, version 4