SqlClient Connection Strings
The syntax for a SqlConnection connection string is documented in the ConnectionString property. You can use the ConnectionString property 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 Framework Data Provider for OleDb (System.Data.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)"
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"
.gif) Security Note: |
|---|
The default setting for the
PersistSecurity 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 PersistSecurity 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, 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 functionality available to a client application is dependent on the version of SQL Server and the compatibility level of the database. The Type System Version keywords in a SqlConnection..::.ConnectionString can be used to specify the client-side representation of SQL Server types. Explicitly setting the type system version that the client application was written for avoids potential problems that could cause an application to break if a different version of SQL Server is used. For example, UDT columns are represented as a byte[] array if you specify SQL Server 2000. If you specify SQL Server 2005, they are represented as managed types.
The available Type System Version values are described in the following table.
Value
|
Description
|
|---|
Latest
|
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. This is the default setting in ADO.NET.
|
SQL Server 2000
|
Uses the SQL Server 2000 type system.
|
SQL Server 2005
|
Uses the SQL Server 2005 type system.
|
SQL Server 2008
|
Uses the SQL Server 2008 type system. Datetime values are processed based on the type system version and the default language specified on the server.
|
For compatibility with features introduced in SQL Server 2008, you can explicitly supply the Type System Version in the connection string by using one of the following.
Type System Version= SQL Server 2008;
Type System Version=Latest;