SQL Server Connection Strings Reference Guide

By: Daniel Calbimonte | Updated: 2022-04-28 | Comments (2) | Related: > Database Configurations

Problem

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

Solution

In this tutorial, we 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. In this tutorial, we will show different combinations of connections using these three options. We will also show the syntax to retrieve data using SqlClient, OLEDB and ODBC.

SqlClient Database Connection String Examples for SQL Server

To make a database connection using SqlClient, we have to provide the following:

Server - is the SQL Server instance name. If it is an instance you need to specify the serverName\instanceName. You can use a period (.) for a local SQL Server. If you use a 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

SqlClient to connect using a SQL Server login:

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

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

The following shows how to connect using ODBC.

For an ODBC database connection, you use the ODBC driver for SQL Server. You need to specify the following:

Driver - this is the driver to connect to SQL Server ODBC Driver 17 for SQL Server
Server - is the SQL Server name. If it is an instance you need to specify the servername\instance name. You can use a period (.) for a local SQL Server. If you use a 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 - this is used 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

ODBC to connect using a SQL Server login:

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

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;

ODBC to connect to using 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

Finally, we have examples for an OLEDB database connection. You need to specify that the provider is MSOLEDBSQL which is the OLE DB provider for SQL Server. Then you need to specify the following:

Provider- specify the OLEDB provider which is MSOLEDBSQL
Server - is the SQL Server name. If it is an instance you need to specify the servername\instance name. You can use a period (.) for a local SQL Server. If you use a 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;

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;

OLEDB to connect using a SQL Server login and encrypt connection:

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

The following 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

If you want to learn more information about Microsoft Visual Studio with .NET and SQL Server, refer to these links:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

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

View all my tips

Article Last Updated: 2022-04-28

SQL Server Connection Strings using SqlClient, OLDEDB and ODBC