connection-string-syntax.md

title	Connection string syntax
description	Learn about syntax of connection strings in the Microsoft SqlClient Data Provider for SQL Server. The syntax for each provider is documented in its ConnectionString property.
author	David-Engel
ms.author	davidengel
ms.date	04/19/2023
ms.service	sql
ms.subservice	connectivity
ms.topic	concept-article

Connection string syntax

[!INCLUDE dotnet-all]

[!INCLUDEDriver_ADONET_Download]

The xref:Microsoft.Data.SqlClient has a Connection object that inherits from xref:System.Data.Common.DbConnection and a provider-specific xref:System.Data.Common.DbConnection.ConnectionString%2A property. The specific connection string syntax for the SqlClient provider is documented in its ConnectionString property. For more information on connection string syntax, see xref:Microsoft.Data.SqlClient.SqlConnection.ConnectionString%2A.

Connection string builders

Microsoft SqlClient Data Provider for SQL Server introduced the following connection string builder.

• xref:Microsoft.Data.SqlClient.SqlConnectionStringBuilder

The connection string builders allow you to construct syntactically valid connection strings at run time, so you don't 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 following table shows the Windows Authentication syntax used with the Microsoft SqlClient Data Provider for SQL Server.

Provider	Syntax
SqlClient	Integrated Security=true; -- or -- Integrated Security=SSPI;

SqlClient connection strings

The syntax for a xref:Microsoft.Data.SqlClient.SqlConnection connection string is documented in the xref:Microsoft.Data.SqlClient.SqlConnection.ConnectionString%2A?displayProperty=nameWithType property. You can use the xref:Microsoft.Data.SqlClient.SqlConnection.ConnectionString%2A property to get or set a connection string for a SQL Server database. The connection string keywords also map to properties in the xref:Microsoft.Data.SqlClient.SqlConnectionStringBuilder.

Important

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.

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;Encrypt=True;"  
"Persist Security Info=False;Integrated Security=SSPI;  
    database=AdventureWorks;server=(local);Encrypt=True;"  
"Persist Security Info=False;Trusted_Connection=True;  
    database=AdventureWorks;server=(local);Encrypt=True;"  

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.

"Persist Security Info=False;User ID=<user name>;Password=<password>;Initial Catalog=AdventureWorks;Server=MySqlServer;Encrypt=True;"  

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

Note

Windows authentication takes precedence over SQL Server logins. 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.

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 xref:Microsoft.Data.SqlClient.SqlConnectionStringBuilder.DataSource%2A property of the SqlConnectionStringBuilder to the instance name when building a connection string. The xref:Microsoft.Data.SqlClient.SqlConnection.DataSource%2A property of a xref:Microsoft.Data.SqlClient.SqlConnection object is read-only.

Type system version changes

The Type System Version keyword in a xref:Microsoft.Data.SqlClient.SqlConnection.ConnectionString%2A?displayProperty=nameWithType specifies the client-side representation of SQL Server types. For more information about the Type System Version keyword, see xref:Microsoft.Data.SqlClient.SqlConnection.ConnectionString%2A?displayProperty=nameWithType.

Connect and Attach 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.

Use TrustServerCertificate

The TrustServerCertificate keyword is used when connecting to a SQL Server instance. When TrustServerCertificate is set to true, the transport layer uses TLS/SSL to encrypt the channel and bypass walking the certificate chain to validate trust.

• In versions prior to Microsoft.Data.SqlClient 2.0, this setting is ignored when Encrypt is set to False and the server certificate isn't validated.
• Starting in version 2.0 of Microsoft.Data.SqlClient, even if Encrypt is set False, setting controls whether certificate validation is performed when the server forces encryption.
• Starting in version 5.0 of Microsoft.Data.SqlClient, this setting is ignored when Encrypt is set to Strict. The server certificate is always validated in Strict mode.

For more information, see Encryption and certificate validation.

"TrustServerCertificate=true;"

HostNameInCertificate

Starting in version 5.0 of Microsoft.Data.SqlClient, HostNameInCertificate is a new connection option. Server certificate validation ensures that the Common Name (CN) or Subject Alternate Name (SAN) in the certificate matches the server name being connected to. In some cases, like DNS aliases, the server name might not match the CN or SAN. The HostNameInCertificate value can be used to specify a different, expected CN or SAN in the server certificate.

"HostNameInCertificate=myserver.example.com"

ServerCertificate

Starting in version 5.1 of Microsoft.Data.SqlClient, ServerCertificate is a new connection option. The default value of the ServerCertificate connection setting is an empty string. When Encrypt is set to Mandatory or Strict, ServerCertificate can be used to specify a path on the file system to a certificate file to match against the server's TLS certificate. For the certificate to be valid, the certificate specified must be an exact match. The accepted certificate formats are PEM, DER, and CER. Here's an example:

"Data Source=...;Encrypt=Strict;ServerCertificate=C:\certificates\server.cer"

Enable encryption

To enable encryption when a certificate hasn't been provisioned on the server, the Trust Server Certificate connection property must be set to True. In this case, encryption uses a self-signed server certificate without validation since no verifiable certificate has been provisioned on the server.

Application settings can't 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 hasn't been provisioned. However, if TrustServerCertificate isn't enabled in the client configuration, a provisioned server certificate is still required.

The following table describes all cases.

Encrypt connection string/attribute	Trust Server Certificate connection string/attribute	Result
No/Optional	Ignored	No encryption occurs.
Yes/Mandatory	No	Encryption occurs only if there's a verifiable server certificate, otherwise the connection attempt fails.
Yes/Mandatory	Yes	Encryption always occurs, but may use a self-signed server certificate.
Strict1	Ignored	Encryption always occurs and must use a verifiable server certificate, otherwise the connection attempt fails.

¹ Strict encryption is only available starting with Microsoft.Data.SqlClient version 5.0.

For more information, including behavior in previous versions, see Encryption and certificate validation.

See also

Connection strings
Encryption and certificate validation
Connecting to a data source
Microsoft ADO.NET for SQL Server