Connection String Syntax

Each .NET Framework data provider has a Connection object that inherits from DbConnection as well as a provider-specific ConnectionString property. The specific connection string syntax for each provider is documented in its ConnectionString property. The following table lists the four data providers that are included in the .NET Framework.

Connection String Builders

ADO.NET 2.0 introduced the following connection string builders for the .NET Framework data providers.

• SqlConnectionStringBuilder

• OleDbConnectionStringBuilder

• OdbcConnectionStringBuilder

• OracleConnectionStringBuilder

The connection string builders allow you to construct syntactically valid connection strings at run time, so you do not have to manually concatenate connection string values in your code. For more information, see Connection String Builders.

Windows Authentication

We recommend using Windows Authentication (sometimes referred to as integrated security) to connect to data sources that support it. The syntax employed in the connection string varies by provider. The following table shows the Windows Authentication syntax used with the .NET Framework data providers.

SqlClient Connection Strings

The syntax for a SqlConnection connection string is documented in the SqlConnection.ConnectionString property. You can use the ConnectionString property to get or set a connection string for a SQL Server database. If you need to connect to an earlier version of SQL Server, you must use the .NET Framework Data Provider for OleDb (System.Data.OleDb). Most connection string keywords also map to properties in the SqlConnectionStringBuilder.

Windows authentication with SqlClient

Each of the following forms of syntax uses Windows Authentication to connect to the AdventureWorks database on a local server.

"Persist Security Info=False;Integrated Security=true;  
    Initial Catalog=AdventureWorks;Server=MSSQL1"  
"Persist Security Info=False;Integrated Security=SSPI;  
    database=AdventureWorks;server=(local)"  
"Persist Security Info=False;Trusted_Connection=True;  
    database=AdventureWorks;server=(local)"  

SQL Server authentication with SqlClient

Windows Authentication is preferred for connecting to SQL Server. However, if SQL Server Authentication is required, use the following syntax to specify a user name and password. In this example, asterisks are used to represent a valid user name and password.

"Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=AdventureWorks;Server=MySqlServer"  

When you connect to Azure SQL Database or to Azure SQL Data Warehouse and provide a login in the format user@servername, make sure that the servername value in the login matches the value provided for Server=.

Connect to a named instance of SQL Server

To connect to a named instance of SQL Server, use the server name\instance name syntax.

"Data Source=MySqlServer\\MSSQL1;"  

You can also set the DataSource property of the SqlConnectionStringBuilder to the instance name when building a connection string. The DataSource property of a SqlConnection object is read-only.

Type System Version Changes

The Type System Version keyword in a SqlConnection.ConnectionString specifies the client-side representation of SQL Server types. See SqlConnection.ConnectionString for more information about the Type System Version keyword.

Connecting and Attaching to SQL Server Express User Instances

User instances are a feature in SQL Server Express. They allow a user running on a least-privileged local Windows account to attach and run a SQL Server database without requiring administrative privileges. A user instance executes with the user's Windows credentials, not as a service.

For more information on working with user instances, see SQL Server Express User Instances.

Using TrustServerCertificate

The TrustServerCertificate keyword is valid only when connecting to a SQL Server instance with a valid certificate. When TrustServerCertificate is set to true, the transport layer will use SSL to encrypt the channel and bypass walking the certificate chain to validate trust.

"TrustServerCertificate=true;"

Enabling Encryption

To enable encryption when a certificate has not been provisioned on the server, the Force Protocol Encryption and the Trust Server Certificate options must be set in SQL Server Configuration Manager. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server.

Application settings cannot reduce the level of security configured in SQL Server, but can optionally strengthen it. An application can request encryption by setting the TrustServerCertificate and Encrypt keywords to true, guaranteeing that encryption takes place even when a server certificate has not been provisioned and Force Protocol Encryption has not been configured for the client. However, if TrustServerCertificate is not enabled in the client configuration, a provisioned server certificate is still required.

The following table describes all cases.

For more information, see Using Encryption Without Validation.

OleDb Connection Strings

The ConnectionString property of a OleDbConnection allows you to get or set a connection string for an OLE DB data source, such as Microsoft Access. You can also create an OleDb connection string at run time by using the OleDbConnectionStringBuilder class.

OleDb Connection String Syntax

You must specify a provider name for an OleDbConnection connection string. The following connection string connects to a Microsoft Access database using the Jet provider. Note that the User ID and Password keywords are optional if the database is unsecured (the default).

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\Northwind.mdb;User ID=Admin;Password=;

If the Jet database is secured using user-level security, you must provide the location of the workgroup information file (.mdw). The workgroup information file is used to validate the credentials presented in the connection string.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Northwind.mdb;Jet OLEDB:System Database=d:\NorthwindSystem.mdw;User ID=*****;Password=*****;  

Using DataDirectory to Connect to Access/Jet

DataDirectory is not exclusive to SqlClient. It can also be used with the System.Data.OleDb and System.Data.Odbc .NET data providers. The following sample OleDbConnection string demonstrates the syntax required to connect to the Northwind.mdb located in the application's app_data folder. The system database (System.mdw) is also stored in that location.

"Provider=Microsoft.Jet.OLEDB.4.0;  
Data Source=|DataDirectory|\Northwind.mdb;  
Jet OLEDB:System Database=|DataDirectory|\System.mdw;"  

Connecting to Excel

The Microsoft Jet provider is used to connect to an Excel workbook. In the following connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" indicates that the first row contains column names, not data, and "IMEX=1;" tells the driver to always read "intermixed" data columns as text.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""  

Note that the double quotation character required for the Extended Properties must also be enclosed in double quotation marks.

Data Shape Provider Connection String Syntax

Use both the Provider and the Data Provider keywords when using the Microsoft Data Shape provider. The following example uses the Shape provider to connect to a local instance of SQL Server.

"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=(local);Initial Catalog=pubs;Integrated Security=SSPI;"

Odbc Connection Strings

The ConnectionString property of a OdbcConnection allows you to get or set a connection string for an OLE DB data source. Odbc connection strings are also supported by the OdbcConnectionStringBuilder.

The following connection string uses the Microsoft Text Driver.

Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=d:\bin  

Using DataDirectory to Connect to Visual FoxPro

The following OdbcConnection connection string sample demonstrates using DataDirectory to connect to a Microsoft Visual FoxPro file.

"Driver={Microsoft Visual FoxPro Driver};  
SourceDB=|DataDirectory|\MyData.DBC;SourceType=DBC;"  

Oracle Connection Strings

The ConnectionString property of a OracleConnection allows you to get or set a connection string for an OLE DB data source. Oracle connection strings are also supported by the OracleConnectionStringBuilder .

Data Source=Oracle9i;User ID=*****;Password=*****;  

For more information on ODBC connection string syntax, see ConnectionString.

See also

• Connection Strings
• Connecting to a Data Source
• ADO.NET Overview