Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell and ADO.NET Connection String Settings for SQL Server


By:   |   Last Updated: 2015-08-28   |   Comments (2)   |   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 Updated: 2015-08-28


get scripts

next tip button



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




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

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 11, 2015 - 3:30:39 PM - Dan Back To Top

Just for clarification, the connection timeout value in the connection string is how long to wait for a connection. It doesn't have anything to do with how long a particular command will run before timing out. That is, as you have listed, the CommandTimeout value in the SqlCommand object.


Monday, August 31, 2015 - 2:04:23 AM - alzdba Back To Top

of course, one of the most important connection string attribute your supporting DBA ( or alike ) will look for is :

Application Name

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx


Learn more about SQL Server tools