Using Connection String Keywords
The format of a connection string is a semicolon-delimited list of key/value parameter pairs:
keyword1=value; keyword2=value
When the connection string is validated by the data source, spaces are ignored and keywords are not case sensitive. However, values may be, depending on the case sensitivity of the data source. To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotation marks.
Keyword Syntax
Valid connection string syntax varies by provider, and has evolved over the years from earlier APIs like ODBC. The .NET Framework Data Provider for SQL Server incorporates many elements from older syntax and is generally more forgiving with common connection string syntax. There are frequently synonyms for connection string syntax elements that are all equally valid, although syntax and spelling errors can be unforgiving because the connection string is not parsed until run time. For example, "Integrated Security=true" is valid, whereas "IntegratedSecurity=true" will cause a run-time error.
To address this problem, ADO.NET 2.0 introduces new connection string builders for each .NET Framework data provider, which eliminates the guesswork when creating syntactically valid connection strings. Keywords are exposed as properties, so the following code fragment, where builder represents a SqlConnectionStringBuilder, would correctly create valid keyword syntax.
Dim builder As New SqlConnectionStringBuilder()
builder.IntegratedSecurity = True
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.IntegratedSecurity = true;
For more information, see Building Connection Strings.
The following sections discuss some of the connection string keywords and their implications. For more information on additional connection string keywords, see ConnectionString, ConnectionString, ConnectionString, and ConnectionString.
Using Integrated Security
We recommend that you use Windows Authentication, commonly referred to as integrated security, to connect to server databases. The syntax employed in the connection string varies by provider. The following table shows the syntax used with the .NET Framework data providers.
| Provider | Syntax |
|---|---|
SqlClient |
|
OleDb |
|
Odbc |
|
OracleClient |
|
Note
Integrated Security=true; is preferred when using SqlClient to connect to SQL Server. SSPI is supported for backwards compatibility.
Note
Integrated Security=true throws an exception when used with the OleDb provider.
Persist Security Info
The default setting for the Persist Security Info keyword in a connection string is false.
Persist Security Info=false;
Setting it to true or yes will allow security-sensitive information, including the user ID and password, to be obtained from the connection after the connection has been opened. If you must supply a user ID and password when making a connection, you are most protected if that information is used to open the connection and then discarded, as is the case when Persist Security Info is set to false or no. This is especially important if you are supplying an open connection to an untrusted source or persisting connection information to disk. Keeping Persist Security Info as false helps ensure that the untrusted source does not have access to the security-sensitive information for your connection and also helps ensure that no security-sensitive information is persisted to disk with your connection string information.
AttachDbFileName
The AttachDbFileName connection string keyword represents the name of the primary database file, including the full path name of an attachable database. It also corresponds to the "extended properties" and "initial file name" keys within a SqlConnection connection string. The SqlConnectionStringBuilder also supports AttachDbFileName as a property.
AttachDbFileName has been extended in ADO.NET 2.0 with the introduction of special substitution tokens which can be replaced at run time with an absolute path before being sent to the data source. The path supplied to the database file may be absolute or relative by using the DataDirectory substitution string, which is discussed in the following section.
This sample SqlConnection connection string uses an absolute path to the location of the Northwind.mdf SQL Server database file. The database name must be specified with the keyword Database (or one of its aliases such as Initial Catalog).
"AttachDbFileName=c:\data\Northwind.mdf;Integrated Security=true;Database=Northwind;"
Note
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. Exceptions caused by the server not being the local computer are thrown when the connection is opened.
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 used in a connection string, it indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust. When set to true, SSL is used to encrypt the channel. The default is false.
"TrustServerCertificate=true;"
Note
If TrustServerCertificate is set to true and encryption is turned on, then 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.
Type System Version
The Type System Version keywords in a connection string are supported only in a SqlConnection connection string. It is used to specify a down-level version of SQL Server for applications written against that version.
"Type System Version=SQL Server 2000;"
The type system version can also be specified using the TypeSystemVersion property of a SqlConnectionStringBuilder. Explicitly specifying the type system version an application requires avoids possible problems with incompatible types when running against a newer version of SQL Server. For example, an application that was originally written for a SQL Server 2000 database could break when running against SQL Server 2005. Specifying the Type System Version as SQL Server 2000 causes the SQL Server 2000 type system to be used.
Possible values are:
| Value | Description |
|---|---|
Latest |
The default. Uses the latest version than this client-server pair can handle. This 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. |
Using DataDirectory in a Connection String
|DataDirectory| (enclosed in pipe symbols) is a substitution string or substitution token that can be used in conjunction with AttachDbFileName in connection strings to indicate a relative path to a data source. It allows 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 actual location DataDirectory points to varies depending on the type of application. The following sample connection string assumes a SqlConnection in an ASP.NET or Windows Forms application.
"AttachDbFileName=|DataDirectory|\app_data\Northwind.mdf;integrated security=true;database=Northwind"
In this case, the Northwind.mdf file to be attached is located in the application's \app_data folder. 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 improperly formatted substitution string, an ArgumentException will be thrown.
Using DataDirectory to connect to a User Instance
You can set User Instance = true in a SqlConnection connection string to connect to a user instance of SQL Server 2005 Express, as shown in the following connection string.
Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;AttachDBFilename=|DataDirectory|\InstanceDB.mdf;
Initial Catalog=InstanceDB;
User instances are a new feature available in the SQL Server 2005 Express edition only. They allow a user running on a least-privileged Windows account to attach and run a SQL Server database without requiring administrative privileges. For more information, see Working with User Instances.
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 an Access database (Northwind.mdb) that is stored 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;"
.gif) Security Note |
|---|
Specifying the location of the system database in the connection string is not required if the Access database is unsecured. Access security is off by default, with all users connecting as the built-in Admin user with a blank password. Storing sensitive information in an Access database is not recommended because of the inherent weakness of its file-based security scheme. Implementing Access security is a multistep process that is effective only for deterrence and has proven to be easily bypassed. |
Using DataDirectory to connect to 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;"