SQL Server Connection Strings Reference Guide

Problem

I need to connect to SQL Server from a .NET application and wanted to know what options there are for connection strings.

Solution

This tutorial, will show different ways to connect to Microsoft SQL Server using different connection string properties in .NET.

In ADO.NET you can create connections several ways like SqlClient, OleDB and ODBC. This tutorial, shows different combinations of connections using these options including the syntax to retrieve data using SqlClient, OLEDB and ODBC.

SqlClient Database Connection String Examples for SQL Server

Make a database connection using SqlClient as follows:

Server – is the SQL Server instance name. For a named instance you need to specify the serverName\instanceName. You can use a period (.) for a local SQL Server. For a specific port, you need to specify the server name with a comma and the port.
Database – SQL Server database name.
For SQL Server Authentication
- User Id is the SQL Server login
- Password is the login password
For Windows Authentication
- Use Trusted_Connection=True

Server=ServerName;Database=MSSQLTipsDB;User Id=Username;Password=Password;

Use SqlClient to connect to localhost using Windows Authentication:

Server=.;Database=MSSQLTipsDB;Trusted_Connection=True;

SqlClient to connect to named instance using a port number on localhost using Windows Authentication:

Server=.\instancename,51688;Database=MSSQLTipsDB;Trusted_Connection=True;

SqlClient to connect to SQL Server Express on localhost using Windows Authentication:

Server=.\SQLExpress;Database=MSSQLTipsDB;Trusted_Connection=True;

ODBC Database Connection String Examples for SQL Server

This is how to connect using ODBC.

For an ODBC database connection, use the ODBC driver for SQL Server as follows:

Driver – this is the driver to connect to SQL Server ODBC Driver 17 for SQL Server
Server – is the SQL Server name. For a named instance specify the servername\instance name. You can use a period (.) for a local SQL Server. For a specific port, you need to specify the server name, a comma and the port.
Database – is the name of the SQL Server database.
Failover_Partner – this is database mirroring failover
DSN – use if you setup a DSN with the connection information
For SQL Server Authentication
- UID is the SQL Server login
- PWD is the login password
For Windows Authentication
- Use Trusted_Connection=yes

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Database=MSSQLTipsDB;UID=Username;PWD=Password;

Use ODBC to connect using a Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Database=MSSQLTipsDB;Trusted_Connection=yes;

ODBC to connect to named instance using Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName\InstanceName;Database=MSSQLTipsDB;Trusted_Connection=yes;

Use ODBC with Windows Authentication and specifying a failover server:

Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Failover_Partner=FailoverServerName;Database=MSSQLTipsDB;Trusted_Connection=yes;

ODBC to connect using a DSN and using Windows Authentication:

Driver={ODBC Driver 17 for SQL Server};Dsn=DsnName;Trusted_Connection=yes;

OLEDB Database Connection String Examples for SQL Server

Here are examples for an OLEDB database connection. You need to specify that the provider is MSOLEDBSQL which is the OLE DB provider for SQL Server.

Provider– specify the OLEDB provider which is MSOLEDBSQL
Server – is the SQL Server name. For a named instance specify the servername\instance name. You can use a period (.) for a local SQL Server. For a specific port, you need to specify the server name, a comma and the port.
Database – is the name of the SQL Server database.
MultiSubnetFailover
Failover Partner
Encrypt
Connect Timeout
For SQL Server Authentication
- UID is the SQL Server login
- PWD is the login password
For Windows Authentication
- Use Integrated Security=SSPI

OLEDB to connect using Windows Authentication:

Provider=MSOLEDBSQL;Server=ServerName;Database=MSSQLTipsDB;Integrated Security=SSPI;

Use OLEDB to connect to an Availability Group using Windows Authentication:

Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,55001;MultiSubnetFailover=Yes;Database=MSSQLTipsDB;Integrated Security=SSPI;Connect Timeout=30;

Provider=MSOLEDBSQL;Server=ServerName;Database=MSSQLTipsDB;UID=Username;PWD=Password;Encrypt=yes;

OLEDB to connect using Windows Authentication for database mirroring:

Provider=MSOLEDBSQL;Data Source=ServerName;Failover Partner=MirrorServerName;Database=MSSQLTipsDB;Integrated Security=SSPI;

Code Samples

We created three examples that do the same thing except use different connection strings. They get data from the Sales.Currency table, from the Adventureworks database on the database server and retrieves data from the CurrencyCode and Name columns.

SQLClient Example

This example is using SqlClient:

using SQLClientusing System;
using System.Data.SqlClient;
 
namespace LanguageDetectionExample
{
    class Program
    {
         static void Main(string[] args)
        {
            try
            {
                 SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
                //Connect to the local server using Windows Authentication to the Adventureworks //database 
                conn.ConnectionString = "Server=.;Database=AdventureWorks2019;Trusted_Connection=True;";
 
                using (SqlConnection connection = new SqlConnection(conn.ConnectionString))
                {
                    //Query used in the code
                    String sql = "SELECT CurrencyCode,Name from Sales.Currency";
                    //Connect to Azure SQL using the connection
                    using (SqlCommand sqlcommand = new SqlCommand(sql, connection))
                    {
                        //Open the connection
                        connection.Open();
                        //Execute the reader function to read the information
                        using (SqlDataReader reader = sqlcommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                 Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }
                }
            }
            //If it fails write the error message exception
            catch (SqlException e)
            {
 
                //Write the error message
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
 
    }
}

ODBC Example

This is an example using an ODBC.

using System;
using System.Data.Odbc;
 
namespace odbc_sample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create the connection to SQL Server to the database adventureworks 2019 with windows authentication
            string connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=AdventureWorks2019;Trusted_Connection=yes;";
            //Create the query to the table in Adventureworks 2019
            string query = "SELECT CurrencyCode,Name from Sales.Currency";
 
            // Create the odbc connection
            OdbcConnection connection = new OdbcConnection(connectionString);
 
            // Create command object to invoke the query
            OdbcCommand cmd = new OdbcCommand(query);
 
            //Send the connection
            cmd.Connection = connection;
 
            // Open the onnection
            connection.Open();
 
            // Read the data
            OdbcDataReader reader = cmd.ExecuteReader();
 
            // Read the reader and display the columns of the Sales.Currency table
 
            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
            }
 
            // Close reader
            reader.Close();
            // Close the connection
            connection.Close();
        }
    }
}

OLEDB Example

This is an example using OLEDB.

using System;
using System.Data.OleDb;
 
namespace oledbreader_example
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize value
            string connetionString = null;
            //Define connection
            OleDbConnection connection;
            //Create oledbcommand variable
            OleDbCommand cmd;
            string query = null;
            OleDbDataReader reader;
            //Create the connection string to SQL Server using the Adventureworks2019 database and Windows authentication
            connetionString = "Provider=MSOLEDBSQL;Server=.;Database=adventureworks2019;Integrated Security=SSPI;";
            //Query the Sales.Currency table from the adventureworks 2019 database
            query = "SELECT CurrencyCode,Name from Sales.Currency";
            //create a connection with the string
            connection = new OleDbConnection(connetionString);
            try
            {
                //Open the connection
                connection.Open();
                //send the query and connection
                cmd = new OleDbCommand(query, connection);
                //Read the data
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    //Write the values of CurrencyCode and Name
                    Console.WriteLine("\t{0}\t{1}\n", reader.GetString(0), reader.GetString(1));
                }
                //Close the reader and connection and dispose the cmd
                reader.Close();
                cmd.Dispose();
                connection.Close();
            }
            //Use catch to handle errors
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }
    }
}

Next Steps

Learn more about Microsoft Visual Studio with .NET and SQL Server with these links:

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018

4 Comments

Patrick Murphy
January 8, 2025 / 10:59 am Reply
Additional connection strings; https://www.connectionstrings.com/
Patrick Murphy
January 8, 2025 / 10:57 am Reply
A nice summary but it left out the equally standard jdbc connection strings used by client such as SQL Developer and DBeaver.
KS
May 5, 2022 / 1:27 am Reply
Would appreciate if connection string for powershell is also included along with script.
Phil Grayson
April 30, 2022 / 4:46 am Reply
We have a connection string generator for. NET & JDBC. Hopefully it’s helpful.
https://www.aireforge.com/tools/sql-server-connection-string-generator