Learn more about SQL Server tools

   
   




































Latest from MSSQLTips














PowerShell and ADO.NET Connection String Settings for SQL Server

MSSQLTips author Tim Smith By:   |   Read Comments   |   Related Tips: More > 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.

Azure IP connection failures

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

An alternative to using credentials in connection strings

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


Last Update: 8/28/2015





About the author
MSSQLTips author Tim Smith
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources


print tip Print  
Become a paid author


Learn more about SQL Server tools








Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.