PowerShell and ADO.NET Connection String Settings for SQL Server

By: Tim Smith   |   Updated: 2015-08-28   |   Comments (2)   |   Related: > PowerShell

Problem

The following tip addresses some common questions from PowerShell developers about connection strings, errors with strings, and alternative development approaches to using connection strings.

Solution

The following will show issues you may face when connecting to SQL Server and solutions to these problems.

Connection Timeout Errors

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

In a few development cases, timeout errors can indicate that you've passed in the wrong server, database, credential information and adjusting the timeout setting may not have an impact. If all of the values are correct, you will want to look at your timeout settings. In connection strings you can specify a timeout, where 0 equals infinite, the default for System.Data.SqlClient.SqlConnection is 15 seconds, and the default for System.Data.SqlClient.SqlCommand is 30 seconds.

In the below two lines of PowerShell, we specify a connection timeout of 0, meaning that the script will run as long as it takes to complete. If it takes ten hours to bulk copy a table, or backup a database, the script will run that length of time. This doesn't mean that the script will run if another error occurs, such as the server is restarted during a long transaction. In addition, when running a command, specify the same length of time for the timeout.

$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OURSERVER\OURINSTANCE;Initial Catalog=OurDatabase;Connection Timeout=0;Integrated Security=true"

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandText = "SELECT * FROM OurTable"
$cmd.CommandTimeout = 0

Consider on a case-by-case whether you want to do this or not; some situations call for very short timeouts, or moderate timeouts. For instance:

1. When running a heartbeat against a server, we generally want our heartbeats short. If we can't get a response within a few seconds, that may indicate a problem.
2. When running simulated transactions, we want our timeout set at the lowest time allowed for a real transaction (or possibly less). This means that if one of our transaction times is set to run in 30 seconds, we should mirror that for a simulated transaction.
3. I prefer alerts on long running queries, especially maintenance, over setting timeout lengths, but some developers do set a limited time on maintenance and this is an option. Just consider what could happen if the transaction aborts; as an example, run a backup database transaction on a large database in PowerShell, and set both the connection string and command timeout to 1 second to observe it abort.

When you absolutely don't want a transaction to fail due to a timeout, specify 0.

Using full connection strings from configuration tables and files

We can pass in full connection strings from either configuration files or configuration tables. In the below example, instead of parameters with the server name, username, password, etc., we pass in the full connection string and the function executes.

Function Execute-SQL {
    Param(
        [string]$sconstring
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "$sconstring"
    
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandText = "CREATE TABLE ##HelloWorld (ID INT)"
        $cmd.CommandTimeout = 0
    
        $scon.Open()
        $cmd.ExecuteNonQuery()
        $scon.Dispose()
        $cmd.Dispose()
    }
}

Execute-SQL -sconstring "Data Source=OURSERVER\OURINSTANCE;Initial Catalog=OurDatabase;Connection Timeout=0;Integrated Security=true"

If we had an XML configuration file with our connection string stored in the "scon" node, we could store that in a variable and call our function with it:

### Extracting the connection string from the scon node:
[xml]$data = Get-Content "C:\scon.config"
$sconstring = $data.connections.scon
Execute-SQL -sconstring $sconstring

Specifying a port number in connection strings

In some cases, you may need to explicitly add the port number in the connection string. In the below connection string calling the Execute-SQL function, we add the port number in the connection string. One quick way to save time if you ever get a connection error on an initial connection is to test adding the default port.

### Using the above Execute-SQL function
Execute-SQL -sconstring "Data Source=OURSERVER\OURINSTANCE,1433;Initial Catalog=OurDatabase;Connection Timeout=0;Integrated Security=true"

Azure IP connection failures

When connecting to an Azure database, if you get an error with a returned IP, cross check Azure's IP list to verify that the IP you're connecting from is on the approved list. In addition, msdb, model and tempdb are not available, so connection strings with those databases will fail.

An alternative to using credentials in connection strings

If we're in an environment with one domain and username and we need to connect to a server with different credentials, but still use integrated security, we can use PowerShell's Start-Process, passing in the location to PowerShell and the parameter -Credential with our domain and username. We'll see a box, like the below image, asking us to enter our password. .

### Note that this is calling the application PowerShell ISE
Start-Process "C:\Windows\System32\WindowsPowerShell\v1.0\powershell_ise.exe" -Credential "OurDomain\OurUser" -NoNewWindow

We can still use ";Integrated Security=true;" in our connection strings over ";User ID=OurDomain\OurUser;Password=OurPassword;" though the latter is still a valid solution.

Next Steps

• Since clients and environments differ, consider which of these solutions work best with which environment.
• When considering a length for a timeout, developers can initially time a first try if they don't expect that the transaction will grow and if they're unaware what the timeout length currently is for the application.
• Check out these other PowerShell tips for SQL Server

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips