連接字串語法Connection String Syntax

每個 .NET Framework 資料提供者都擁有一個 Connection 物件,繼承自 DbConnection 以及提供者特定的 ConnectionString 屬性。Each .NET Framework data provider has a Connection object that inherits from DbConnection as well as a provider-specific ConnectionString property. 每個提供者的特定連接字串語法會記錄在其 ConnectionString 屬性中。The specific connection string syntax for each provider is documented in its ConnectionString property. 下表列出 .NET Framework 中包含的四個資料提供者。The following table lists the four data providers that are included in the .NET Framework.

.NET Framework Data Provider - .NET Framework 資料提供者.NET Framework data provider 描述Description
System.Data.SqlClient 提供 Microsoft SQL Server 的資料存取。Provides data access for Microsoft SQL Server. 如需連接字串語法的詳細資訊,請參閱 ConnectionStringFor more information on connection string syntax, see ConnectionString.
System.Data.OleDb 為使用 OLE DB 所公開的資料來源提供資料存取。Provides data access for data sources exposed using OLE DB. 如需連接字串語法的詳細資訊,請參閱 ConnectionStringFor more information on connection string syntax, see ConnectionString.
System.Data.Odbc 為使用 ODBC 所公開的資料來源提供資料存取。Provides data access for data sources exposed using ODBC. 如需連接字串語法的詳細資訊,請參閱 ConnectionStringFor more information on connection string syntax, see ConnectionString.
System.Data.OracleClient 提供 Oracle 8.1.7 (含) 以後版本的資料存取。Provides data access for Oracle version 8.1.7 or later. 如需連接字串語法的詳細資訊,請參閱 ConnectionStringFor more information on connection string syntax, see ConnectionString.

連接字串產生器Connection String Builders

ADO.NET 2.0 針對 .NET Framework 資料提供者導入了下列連接字串產生器 (Builder)。ADO.NET 2.0 introduced the following connection string builders for the .NET Framework data providers.

連接字串產生器可讓您在執行階段建構語法有效的連接字串,所以您不需要在程式碼中手動串連連接字串值。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 驗證Windows Authentication

建議您使用「Windows 驗證」(有時稱為「整合式安全性」)連接到支援它的資料來源。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. 下列表格說明搭配 .NET Framework 資料提供者使用的「Windows 驗證」語法。The following table shows the Windows Authentication syntax used with the .NET Framework data providers.

ProviderProvider 語法Syntax
SqlClient Integrated Security=true;

-- or --

Integrated Security=SSPI;
OleDb Integrated Security=SSPI;
Odbc Trusted_Connection=yes;
OracleClient Integrated Security=yes;

注意

Integrated Security=true 提供者搭配使用時,OleDb 會擲回例外狀況。Integrated Security=true throws an exception when used with the OleDb provider.

SqlClient 連接字串SqlClient Connection Strings

SqlConnection 連接字串的語法列於 SqlConnection.ConnectionString 屬性中。The syntax for a SqlConnection connection string is documented in the SqlConnection.ConnectionString property. 您可以使用 ConnectionString 屬性來取得或設定 SQL Server 資料庫的連接字串。You can use the ConnectionString property to get or set a connection string for a SQL Server database. 如果您需要連接至舊版的 SQL Server,則必須使用 .NET Framework Data Provider for OleDb (System.Data.OleDb)。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). 大多數的連接字串關鍵字也可對應至 SqlConnectionStringBuilder 中的屬性。Most connection string keywords also map to properties in the SqlConnectionStringBuilder.

重要

Persist Security Info 關鍵字的預設設定為 falseThe default setting for the Persist Security Info keyword is false. 如果將其設為 trueyes,則在開啟連接後,可透過連接獲得機密資訊,包含使用者 ID 和密碼。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. 保持 Persist Security Info 設定為 [false],以確保不受信任的來源不能存取機密的連接字串資訊。Keep Persist Security Info set to false to ensure that an untrusted source does not have access to sensitive connection string information.

使用 SqlClient 的 Windows 驗證Windows authentication with SqlClient

下列每一種形式的語法都使用 Windows 驗證連接到本機伺服器上的AdventureWorks資料庫。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)"  

使用 SqlClient SQL Server 驗證SQL Server authentication with SqlClient

連接至 SQL Server 時慣用「Windows 驗證」。Windows Authentication is preferred for connecting to SQL Server. 不過,如果需要「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"  

當您連接到 Azure SQL Database 或 Azure SQL 資料倉儲並以 user@servername格式提供登入時,請確定登入中的 servername 值符合針對 Server=提供的值。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=.

注意

Windows 驗證的優先順序高於 SQL Server 登入。Windows authentication takes precedence over SQL Server logins. 如果您同時指定 Integrated Security=true 以及使用者名稱和密碼,系統就會忽略使用者名稱和密碼,而使用 Windows 驗證。If you specify both Integrated Security=true as well as a user name and password, the user name and password will be ignored and Windows authentication will be used.

連接到 SQL Server 的已命名實例Connect to a named instance of SQL Server

若要連接到 SQL Server 的已命名實例,請使用伺服器 name\instance 名稱語法。To connect to a named instance of SQL Server, use the server name\instance name syntax.

"Data Source=MySqlServer\MSSQL1;"  

您也可以在建立連接字串時,將 DataSourceSqlConnectionStringBuilder 屬性設定為執行個體名稱。You can also set the DataSource property of the SqlConnectionStringBuilder to the instance name when building a connection string. DataSource 物件的 SqlConnection 屬性是唯讀的。The DataSource property of a SqlConnection object is read-only.

型別系統版本變更Type System Version Changes

SqlConnection.ConnectionString 中的 Type System Version 關鍵字會指定 SQL Server 類型的用戶端標記法。The Type System Version keyword in a SqlConnection.ConnectionString specifies the client-side representation of SQL Server types. 如需 SqlConnection.ConnectionString 關鍵字的詳細資訊,請參閱 Type System VersionSee SqlConnection.ConnectionString for more information about the Type System Version keyword.

連接和附加至 SQL Server Express 使用者執行個體Connecting and Attaching to SQL Server Express User Instances

使用者執行個體是 SQL Server Express 中的功能。User instances are a feature in SQL Server Express. 透過使用者執行個體,在最低權限的本機 Windows 帳戶上執行的使用者不需要系統管理員權限,即可附加及執行 SQL Server 資料庫。They allow a user running on a least-privileged local Windows account to attach and run a SQL Server database without requiring administrative privileges. 使用者執行個體會使用使用者的 Windows 認證執行,而不是以服務方式執行。A user instance executes with the user's Windows credentials, not as a service.

如需使用使用者實例的詳細資訊,請參閱SQL Server Express 使用者實例For more information on working with user instances, see SQL Server Express User Instances.

使用 TrustServerCertificateUsing TrustServerCertificate

只有在使用有效憑證連接到 SQL Server 實例時,TrustServerCertificate 關鍵字才有效。The TrustServerCertificate keyword is valid only when connecting to a SQL Server instance with a valid certificate. TrustServerCertificate 設定為 true 時,傳輸層 (Transport Layer) 會使用 SSL 來加密通道,而略過逐一檢查憑證鏈結以驗證信任的作業。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;"

注意

如果 TrustServerCertificate 是設定為 true 且加密功能已開啟,則即使 Encrypt 在連接字串中是設定為 false,仍將使用伺服器上指定的加密等級,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

若要在伺服器上尚未布建憑證時啟用加密,必須在 SQL Server 組態管理員中設定 [強制通訊協定加密] 和 [信任伺服器憑證] 選項。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.

應用程式設定無法降低 SQL Server 中設定的安全性層級,但可以選擇性地進行加強。Application settings cannot reduce the level of security configured in SQL Server, but can optionally strengthen it. 應用程式可以藉由將 TrustServerCertificateEncrypt 關鍵字設定為 true來要求加密,以確保即使伺服器憑證尚未布建,以及尚未設定強制通訊協定加密,還是會進行加密。適用于用戶端。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. 不過,如果用戶端組態中未啟用 TrustServerCertificate,則仍需要提供伺服器憑證。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/AN/A 否 (預設值)No (default) 略過Ignored 未發生任何加密。No encryption occurs.
No N/AN/A [是]Yes 否 (預設值)No (default) 加密只有在有可驗證的伺服器憑證時才會發生,否則連接嘗試就會失敗。Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.
No N/AN/A [是]Yes [是]Yes 一律會進行加密,但可能會使用自我簽署的伺服器憑證。Encryption always occurs, but may use a self-signed server certificate.
[是]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 always occurs, but may use a self-signed server certificate.
[是]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 always occurs, but may use a self-signed server certificate.

如需詳細資訊,請參閱使用加密而不進行驗證For more information, see Using Encryption Without Validation.

OleDb 連接字串OleDb Connection Strings

ConnectionStringOleDbConnection 屬性可讓您取得或設定 OLE DB 資料來源 (例如 Microsoft Access) 的連接字串。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. 您也可以使用 OleDb 類別 (Class),在執行階段建立 OleDbConnectionStringBuilder 連接字串。You can also create an OleDb connection string at run time by using the OleDbConnectionStringBuilder class.

OleDb 連接字串語法OleDb Connection String Syntax

您必須指定 OleDbConnection 連接字串的提供者名稱。You must specify a provider name for an OleDbConnection connection string. 下列連接字串會使用 Jet 提供者連接至 Microsoft Access 資料庫。The following connection string connects to a Microsoft Access database using the Jet provider. 請注意,如果資料庫未受保護 (預設值),則 User IDPassword 關鍵字是選擇性項目。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=;

如果您使用使用者層級安全性保護 Jet 資料庫的安全,則必須提供工作群組資訊檔 (.mdw) 的位置。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=*****;  

重要

您可以在通用資料連結(UDL)檔案中提供OleDbConnection的連接資訊;不過,您應該避免這樣做。It is possible to supply connection information for an OleDbConnection in a Universal Data Link (UDL) file; however you should avoid doing so. UDL 檔並未加密,並且會以純文字的格式公開連接字串資訊。UDL files are not encrypted, and expose connection string information in clear text. 因為對您的應用程式而言,UDL 檔是外部的檔案型資源,所以您無法使用 .NET Framework 來保護該檔案。Because a UDL file is an external file-based resource to your application, it cannot be secured using the .NET Framework. UDL 檔案不支援SqlClientUDL files are not supported for SqlClient.

使用 DataDirectory 連接至 Access/JetUsing DataDirectory to Connect to Access/Jet

DataDirectory 並不是由 SqlClient 所獨佔,DataDirectory is not exclusive to SqlClient. 也可以搭配 System.Data.OleDbSystem.Data.Odbc .NET data 提供者使用。It can also be used with the System.Data.OleDb and System.Data.Odbc .NET data providers. 下列範例 OleDbConnection 字串所示範的語法,是連接到位於應用程式的 app_data 資料夾中的 Northwind.mdb 所需。The following sample OleDbConnection string demonstrates the syntax required to connect to the Northwind.mdb located in the application's app_data folder. 系統資料庫 (System.mdw) 也是儲存於該位置。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;"  

重要

如果 Access/Jet 資料庫未受保護,則不需要在連接字串中指定系統資料庫的位置。Specifying the location of the system database in the connection string is not required if the Access/Jet database is unsecured. 安全性預設為關閉,且所有連接的使用者都是使用空白密碼的內建 Admin 使用者。Security is off by default, with all users connecting as the built-in Admin user with a blank password. 即使已正確地實作使用者層級的安全性,Jet 資料庫仍很容易受到攻擊。Even when user-level security is correctly implemented, a Jet database remains vulnerable to attack. 因此,不建議在 Access/Jet 資料庫中儲存機密資訊,因為其檔案架構的安全性配置原本就有弱點。Therefore, storing sensitive information in an Access/Jet database is not recommended because of the inherent weakness of its file-based security scheme.

連接至 ExcelConnecting to Excel

Microsoft Jet 提供者可用於連接至 Excel 活頁簿。The Microsoft Jet provider is used to connect to an Excel workbook. 在下列連接字串中,由 Extended Properties 關鍵字設定 Excel 特定的屬性。In the following connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" 表示第一個資料列包含資料行名稱,而非資料;"IMEX=1;" 表示驅動程式會將「混合」資料行一律讀取為文字。"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""  

請注意,Extended Properties 所需的雙引號字元還必須以雙引號括住。Note that the double quotation character required for the Extended Properties must also be enclosed in double quotation marks.

Data Shape 提供者連接字串語法Data Shape Provider Connection String Syntax

使用 Microsoft Data Shape 提供者時,使用 ProviderData Provider 這兩個關鍵字。Use both the Provider and the Data Provider keywords when using the Microsoft Data Shape provider. 下列範例使用 Shape 提供者連接至 SQL Server 的本機執行個體。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 連接字串Odbc Connection Strings

ConnectionStringOdbcConnection 屬性可讓您取得或設定 OLE DB 資料來源的連接字串。The ConnectionString property of a OdbcConnection allows you to get or set a connection string for an OLE DB data source. Odbc 連接字串也受到 OdbcConnectionStringBuilder 的支援。Odbc connection strings are also supported by the OdbcConnectionStringBuilder.

下列連接字串使用 Microsoft Text Driver。The following connection string uses the Microsoft Text Driver.

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

使用 DataDirectory 連接至 Visual FoxProUsing DataDirectory to Connect to Visual FoxPro

下列的 OdbcConnection 連接字串範例示範如何使用 DataDirectory 連接到 Microsoft 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 連接字串Oracle Connection Strings

ConnectionStringOracleConnection 屬性可讓您取得或設定 OLE DB 資料來源的連接字串。The ConnectionString property of a OracleConnection allows you to get or set a connection string for an OLE DB data source. Oracle 連接字串也受到 OracleConnectionStringBuilder 的支援。Oracle connection strings are also supported by the OracleConnectionStringBuilder .

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

如需 ODBC 連接字串語法的詳細資訊,請參閱 ConnectionStringFor more information on ODBC connection string syntax, see ConnectionString.

請參閱See also