SQL Connection Strings tips

This article intends to give some useful tips on usage details of the SQL connection strings.

What are SQL connection strings?

The connection string is an expression that contains the parameters required for the applications to connect a database server. In terms of SQL Server, connection strings include the server instance, database name, authentication details, and some other settings to communicate with the database server.

How to connect SQL Server using a connection string

We can use the following connection string for the SQL Server authentication. In this type of connection string, we require to set the user name and password.

Server=ServerName;Database=DatabaseName;User Id=UserName;Password=UserPassword;

This usage type may cause vulnerabilities in our application thus using windows authentication can provide more security in our applications. The following connection string will connect the database using windows authentication.

Server=ServerName;Database=DatabaseName;Trusted_Connection=True;

With help of the following C# code, this is how we can see the usage of a connection string in an application.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;

namespace TestConnectionString

{

class Program

{

static void Main(string[] args)

{

using (SqlConnection conn = new SqlConnection("Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass;"))

{

conn.Open();

Console.WriteLine("Connection is just opened");

System.Threading.Thread.Sleep(10000);

conn.Close();

}

We can monitor this connection details to use dm_exec_sessions view in SQL Server.

SELECT

session_id

,program_name,

last_request_start_time,

last_request_end_time,

language,

date_format ,(select name from sys.databases where database_id =11) as ConnectedDatabase

FROM sys.dm_exec_sessions

where login_name ='SuperHero'

and is_user_process=1

How to change language in SQL connection strings

SQL Server allows changing the language setting for the sessions. So, this option will change the system messages and datetime formats in the session. We can change the language option in the connection string using the Language attribute. In the following SQL connection string, we will change the language default language with Polish.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass;Language=Polish;

How to change application name in SQL connection string

Application Name helps to easily identify which application is connecting to SQL Server otherwise it can take more effort to identify owners of the applications. We can change the Application Name to give a name in the connection string.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp

How to change client workstation name in SQL connection strings

In a connection string, we can change the connecting machine name to use the Workstation ID attribute. In the following connection string, we will set the machine name as SuperNova.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp;Workstation ID=SuperNova

SQL connection string and connection pooling

Generating a new connection is a bit resource-intensive task for the SQL engine. Therefore, SQL Server uses the connection pooling mechanism to get rid of this laborious task. The default setting of the connection pool is true, therefore, we don’t need to change anything to enable this option. However, we can explicitly set this option in the connection string.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp;Workstation ID=SuperNova;Pooling=true

The connection pool can be likened to a connection cache because in this working concept SQL SQL Server keeps ready to use in a pool instead of destroying the connections that their tasks have finished up. When a user requests a new connection with a matching connection string, the pooler looks for an available connection in the pool that corresponds to that connection string. If the pool worker finds a connection according to matching criteria it returns this ready connection to this request. When the application sends a signal to close the connection, instead of closing the connection, the pool worker sends it to the connection pool. In the C# code, we will request 7 new connections and execute a very simple query.

When we run this console application it will generate 2 connection pools. The first 3 connections will place in a connection pool and the second 2 connections will place another pool.

This case can observe using performance counters of the .NET Framework Data Provider for SQL Server in PerfMon. The parameter NumberOfActiveConnectionPools shows the number of connection pools and NumberOfPooledConnections shows how many connections are managed in these pools.

The reason for the generation of two separate connection pools is the application name difference in the connection strings. Connections string dissimilarities cause to generate separate connection pools. Such as different database names, authentication types, and other differences cause to generate different connection pools. Connection pools are not destroyed until the active process ends or the connection lifetime is exceeded. We can set connection lifetime in the connection string and it determines how long a connection will be kept in the connection pool.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp;Workstation ID=SuperNova;Pooling=true; Connection Lifetime=100

In our sample application, we have a sixth and seventh connection at the end of the code, their connection request will be given from the connection pool. In order to monitor all these complex processes, we can use an extended event. We can capture the login/logout events and we also will capture the rpc_completed event. After starting the application the extended event screen will be captured as below:

As we stated the first 5 connection creates a new connection and because of the different application names, SQL server creates two connection pool. When the sixth connection requests a connection, this connection is given from the first connection pool because their connection strings are similar. Also, this case is valid for the seventh connection. The seventh connection is given from the second connection pool. If the is_cached value indicates true, it means that the connection is given from the connection pool and this value seems to be correct for the sixth and seventh connections. We are seeing that the sp_reset_connection procedure is executed after the logout event. This procedure is called between logout and login event because it resets the state of the connection.

Conclusion

In this article, we have explored some details about the SQL connection string. As we have learned, different connection string settings can change the connection behaviors.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec