Export (0) Print
Expand All

Configuring Provider-Specific Connection Strings 

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.

.NET Framework data provider Description

System.Data.SqlClient

Provides data access for Microsoft SQL Server version 7.0 or later. For more information on connection string syntax, see ConnectionString.

System.Data.OleDb

Provides data access for data sources exposed using OLE DB. For more information on connection string syntax, see ConnectionString.

System.Data.Odbc

Provides data access for data sources exposed using ODBC. For more information on connection string syntax, see ConnectionString.

System.Data.OracleClient

Provides data access for Oracle version 8.1.7 or later. For more information on connection string syntax, see ConnectionString.

ADO.NET 2.0 introduces new connection string builders for each .NET Framework data provider, which eliminates guesswork when creating syntactically valid connection strings. For more information, see Building Connection Strings.

Specifying 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.

Provider Syntax

SqlClient

Integrated Security=true;

-- or --

Integrated Security=SSPI;

OleDb

Integrated Security=SSPI;

Odbc

Trusted_Connection=yes;

OracleClient

Integrated Security=yes;

NoteNote

Integrated Security=true throws an exception when used with the OleDb provider.

Working with SqlClient Connection Strings

The ConnectionString property of a SqlConnection allows you to get or set a connection string for a SQL Server 7.0 or later database. If you need to connect to an earlier version of SQL Server, you must use the .NET Data Provider for OleDb. Most connection string keywords also map to properties in the SqlConnectionStringBuilder.

Each of the following forms of syntax will use 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)"

Using SQL Server Logins

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"
Security noteSecurity Note

The default setting for the Persist Security Info keyword is false. Setting it to true or yes allows security-sensitive information, including the user ID and password, to be obtained from the connection after the connection has been opened. Keep Persist Security Info set to false to ensure that an untrusted source does not have access to sensitive connection string information.

To connect to a named instance of SQL Server 2000 or later, 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.

Setting the Network Library

The network library can be used to establish a connection to an instance of SQL Server. In this example, the network library is Win32 Winsock TCP/IP (dbmssocn), and 1433 is the port being used.

Network Library=dbmssocn;Data Source=000.000.000.000,1433;

SQL Server allows you to use the following network libraries when establishing a connection.

Name Library

dbnmpntw

Win32 Named Pipes

dbmssocn

Win32 Winsock TCP/IP

dbmsspxn

Win32 SPX/IPX

dbmsvinn

Win32 Banyan Vines

dbmsrpcn

Win32 Multiprotocol (Windows RPC)

Specifying a SQL Server Version Using the Type System Version Keywords

The Type System Version keywords are used to specify an earlier version of SQL Server for applications running in a SQL Server 2005 instance. This avoids possible problems with incompatible types that may cause the application to break. For example, use the following connection string fragment when connecting to a SQL Server 2005 instance if you want the application to work only with the SQL Server 2000 type system.

"Type System Version=SQL Server 2000;" 

The type system version can also be specified using the TypeSystemVersion property of a SqlConnectionStringBuilder.

Possible values are described in the following table.

Value Description

Latest

The default. Uses the latest version that this client-server pair can handle. The version used will automatically move forward as the client and server components are upgraded.

SQL Server 2005

Uses the SQL Server 2005 type system. No conversions are made for the current version of ADO.NET.

SQL Server 2000

Uses the SQL Server 2000 type system. The following comparisons will be performed when connecting to a SQL Server 2005 instance:

XML to NTEXT

UDT to VARBINARY

VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) to TEXT, NEXT, and IMAGE, respectively.

Attaching to SQL Server Express User Instances

User instances are a new feature available in SQL Server 2005 Express Edition only. 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.

User instances are also supported by the SqlConnectionStringBuilder UserInstance and AttachDBFilename properties. For more information on working with user instances, see Working with User Instances.

The AttachDbFileName connection string keyword is used to attach the primary database file (.mdf), which must include the full path name. AttachDbFileName also corresponds to the "extended properties" and "initial file name" keys within a SqlConnection connection string. This example uses an absolute path to the location of the primary data file.

"AttachDbFileName=c:\data\Northwind.mdf;Integrated Security=true;Initial Catalog=Northwind;" 

Using the DataDirectory Substitution String

AttachDbFileName has been extended in ADO.NET 2.0 with the introduction of the |DataDirectory| (enclosed in pipe symbols) substitution string. DataDirectory is used in conjunction with AttachDbFileName to indicate a relative path to a data file, allowing developers to create connection strings that are based on a relative path to the data source instead of being required to specify a full path.

The physical location that DataDirectory points to depends on the type of application. In this example, the Northwind.mdf file to be attached is located in the application's \app_data folder.

Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;
AttachDBFilename=|DataDirectory|\app_data\Northwind.mdf;
Initial Catalog=Northwind;

When DataDirectory is used, the resulting file path cannot be higher in the directory structure than the directory pointed to by the substitution string. For example, if the fully expanded DataDirectory is C:\AppDirectory\app_data, then the sample connection string shown above works because it is below c:\AppDirectory. However, attempting to specify DataDirectory as |DataDirectory|\..\data will result in an error because \data is not a subdirectory of \AppDirectory.

If the connection string has an improperly formatted substitution string, an ArgumentException will be thrown.

NoteNote

System.Data.SqlClient resolves the substitution strings into full paths against the local computer file system. Therefore, remote server, HTTP, and UNC path names are not supported. An exception is thrown when the connection is opened if the server is not located on the local computer.

Using TrustServerCertificate

The TrustServerCertificate keyword is new in ADO.NET 2.0 and valid only when connecting to a SQL Server 2005 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;" 
NoteNote

If TrustServerCertificate is set to true and encryption is turned on, the encryption level specified on the server will be used even if Encrypt is set to false in the connection string. The connection will fail otherwise.

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.

Force Protocol Encryption client setting Trust Server Certificate client setting Encrypt/Use Encryption for Data connection string/attribute Trust Server Certificate connection string/attribute Result

No

N/A

No (default)

Ignored

No encryption occurs.

No

N/A

Yes

No (default)

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

No

N/A

Yes

Yes

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

Yes

No

Ignored

Ignored

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

Yes

Yes

No (default)

Ignored

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

Yes

Yes

Yes

No (default)

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

Yes

Yes

Yes

Yes

Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.

For more information, see "Encryption Hierarchy" and "Using Encryption Without Validation" in SQL Server 2005 Books Online.

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 or SQL Server 6.5 or earlier. Use a SqlConnection for SQL Server 7.0 or later. OleDb connection strings are also supported by 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 UserID 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, you must provide the location of the workgroup information file.

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

Use the sqloledb keyword for SQL Server 6.5 or earlier.

Provider=sqloledb;Data Source=MySqlServer;Initial Catalog=pubs;User Id=*****;Password=*****;
Security noteSecurity Note

It is possible to supply connection information for an OleDbConnection in a Universal Data Link (UDL) file; however you should avoid doing so. UDL files are not encrypted, and expose connection string information in clear text. Because a UDL file is an external file-based resource to your application, it cannot be secured using the .NET Framework.

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;"
Security noteSecurity Note

Specifying the location of the system database in the connection string is not required if the Access/Jet database is unsecured. Security is off by default, with all users connecting as the built-in Admin user with a blank password. Even when user-level security is correctly implemented, a Jet database remains vulnerable to attack. Therefore, storing sensitive information in an Access/Jet database is not recommended because of the inherent weakness of its file-based security scheme.

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

Community Additions

ADD
Show:
© 2014 Microsoft