Setting the Connection Properties

DownloadDownload JDBC Driver

The connection string properties can be specified in various ways:

  • As name=value properties in the connection URL when you connect by using the DriverManager class.
  • As name=value properties in the Properties parameter of the Connect method in the DriverManager class.
  • As values in the appropriate setter method of the data source of the driver. For example:

    datasource.setServerName(value)  
    datasource.setDatabaseName(value)  
    

Remarks

Property names are case-insensitive, and duplicate property names are resolved in the following order:

  1. API arguments (such as user and password)
  2. Property collection.
  3. Last instance in the connection string.

Also, unknown values are allowed for the property names, and their values are not validated by the JDBC driver for case sensitivity.

Synonyms are allowed and are resolved in order, just as duplicate property names.

The following table lists all the currently available connection string properties for the JDBC driver.

Property
Type
Default
Description
accessToken

String

null
Use this property to connect to a SQL database using an access token. accessToken can't be set using the connection URL.
applicationIntent

String

ReadWrite
Declares the application workload type when connecting to a server.

Possible values are ReadOnly and ReadWrite.

For more information, see JDBC Driver Support for High Availability, Disaster Recovery.
applicationName

String
[<=128 char]

null
The application name, or " Microsoft JDBC Driver for SQL Server" if no name is provided.

Used to identify the specific application in various SQL Server profiling and logging tools.
authentication

String

NotSpecified
Beginning with Microsoft JDBC Driver 6.0 for SQL Server, this optional property indicates which SQL authentication method to use for connection. Possible values are ActiveDirectoryIntegrated, ActiveDirectoryPassword, SqlPassword, and the default NotSpecified.

Use ActiveDirectoryIntegrated to connect to a SQL Database using integrated Windows authentication.

Use ActiveDirectoryPassword to connect to a SQL Database using an Azure AD principal name and password.

Use SqlPassword to connect to a SQL Server using userName/user and password properties.

Use NotSpecified if none of these authentication methods are needed.

Important: If authentication is set to ActiveDirectoryIntegrated, the following two libraries need to be installed: SQLJDBC_AUTH.DLL (available in the JDBC driver package) and Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL) It's available in different languages (for both x86 and amd64) from the download center at Microsoft Active Directory Authentication Library for Microsoft SQL Server. The JDBC driver only supports version 1.0.2028.318 and higher for the ADALSQL.DLL.

Note: When authentication property is set to any value other than NotSpecified, the driver by default uses Secure Sockets Layer (SSL) encryption.

For information on how to configure Azure Active Directory authentication visit Connecting to SQL Database By Using Azure Active Directory Authentication.
authenticationScheme

String

NativeAuthentication
Indicates which kind of integrated security you want your application to use. Possible values are JavaKerberos and the default NativeAuthentication.

When using authenticationScheme=JavaKerberos, you must specify the fully qualified domain name (FQDN) in the serverName or serverSpn property. Otherwise, an error occurs (Server not found in Kerberos database).

For more information on using authenticationScheme, see Using Kerberos Integrated Authentication to Connect to SQL Server.
cancelQueryTimeout

int

-1
Beginning with Microsoft JDBC Driver 6.4 for SQL Server, this property can be used to cancel queryTimeout set on the connection. Query execution hangs and does not throw exception if TCP connection to SQL Server is silently dropped. This property is only applicable if 'queryTimeout' is also set on the connection.

The driver waits the total amount of cancelQueryTimeout + queryTimeout seconds, to drop the connection and close the channel.

The default value for this property is -1 and behavior is to wait indefinitely.
columnEncryptionSetting

String
["Enabled" | "Disabled"]

Disabled
Set to "Enabled" to use the Always Encrypted (AE) feature beginning with Microsoft JDBC Driver 6.0 for SQL Server. When AE is enabled, the JDBC driver transparently encrypts and decrypts sensitive data stored in encrypted database columns in the SQL Server.

For more information about columnEncryptionSetting, see Using Always Encrypted with the JDBC Driver for more details.

Note: Always Encrypted is available with SQL Server 2016 or later versions.
databaseName,
database

String
[<=128 char]

null
The name of the database to connect to.

If not stated, a connection is made to the default database.
disableStatementPooling

boolean
["true" | "false"]

true
Flag indicating if the statement pooling should be used.
enablePrepareOnFirst...
PreparedStatementCall

boolean
["true" | "false"]

false
enablePrepareOnFirstPreparedStatementCall

Set to "true" to enable the prepared statement handle creation by calling sp_prepexec in first execution of prepared statement.

Set to "false" to change the first execution of a prepared statement to call sp_executesql and not prepare a statement, once the second execution happens it would call sp_prepexec to setup a prepared statement handle.
encrypt

boolean
["true" | "false"]

false
Set to "true" to specify that the SQL Server uses Secure Sockets Layer (SSL) encryption for all the data sent between the client and the server if the server has a certificate installed. The default value is "false".

Beginning with Microsoft JDBC Driver 6.0 for SQL Server, there is a new connection setting ‘authentication’ that uses SSL encryption by default.

For more information, see the ‘authentication’ property.
failoverPartner

String

null
The name of the failover server used in a database mirroring configuration. This property is used for an initial connection failure to the principal server; after you make the initial connection, this property is ignored. Must be used in conjunction with databaseName property.

Note: The driver does not support specifying the server instance port number for the failover partner instance as part of the failoverPartner property in the connection string. However, specifying the serverName, instanceName, and portNumber properties of the principal server instance and failoverPartner property of the failover partner instance in the same connection string is supported.

If you specify a Virtual Network Name in the Server connection property, you cannot use database mirroring. For more information, see JDBC Driver Support for High Availability, Disaster Recovery
fips

boolean
["true" | "false"]

"false"
For FIPS enabled JVM this property should be true.
fipsProvider

String

null
FIPS provider configured in JVM. For example, BCFIPS or SunPKCS11-NSS. Removed in version 6.4.0 - see the details Here.
gsscredential

org.ietf.jgss.GSSCredential

null
Beginning with Microsoft JDBC Driver 6.2 for SQL Server, user credentials to be used for Kerberos Constrained Delegation can be passed in this property.

This should be used with integratedSecurity as true and JavaKerberos authenticationscheme.
hostNameInCertificate

String

null
The host name to be used in validating the SQL Server SSL certificate.

If the hostNameInCertificate property is unspecified or set to null, the Microsoft JDBC Driver for SQL Server uses the serverName property value on the connection URL as the host name to validate the SQL Server SSL certificate.

Note: This property is used in combination with the encrypt/authentication properties and the trustServerCertificate property. This property affects the certificate validation, if and only if the connection uses Secure Sockets Layer (SSL) encryption and the trustServerCertificate is set to "false". Make sure the value passed to hostNameInCertificate exactly matches the Common Name (CN) or DNS name in the Subject Alternate Name (SAN) in the server certificate for an SSL connection to succeed. For more information, see Understanding SSL Support.
instanceName

String
[<=128 char]

null
The SQL Server instance name to connect to. When it is not specified, a connection is made to the default instance. For the case where both the instanceName and port are specified, see the notes for port.

If you specify a Virtual Network Name in the Server connection property, you cannot use instanceName connection property. See JDBC Driver Support for High Availability, Disaster Recovery for more information.
integratedSecurity

boolean
["true"|"false"]

false
Set to "true" to indicate that Windows credentials are used by SQL Server on Windows operating systems. If "true," the JDBC driver searches the local computer credential cache for credentials that have already been provided at the computer or network logon.

Set to "true" (with authenticationscheme=JavaKerberos), to indicate that Kerberos credentials are used by SQL Server. For more information on Kerberos authentication, see Using Kerberos Integrated Authentication to Connect to SQL Server.

If "false," the username and password must be supplied.
jaasConfigurationName

String

SQLJDBCDriver
Beginning with Microsoft JDBC Driver 6.2 for SQL Server, each connection to SQL Server can have its own JAAS Login Configuration file to establish Kerberos connection. Name of the Login Configuration file can be passed through this property.
By default, driver sets property useDefaultCcache = true for IBM JVMs, and useTicketCache = true for other JVMs.
keyStoreAuthentication

String

null
Beginning with Microsoft JDBC Driver 6.0 for SQL Server, this property identifies which key store to seamlessly set up for the connection with Always Encrypted and determines an authentication mechanism used to authenticate to the key store. Microsoft JDBC Driver 6.0 for SQL Server supports setting up of the Java Key Store seamlessly using this property for which you need to set "keyStoreAuthentication=JavaKeyStorePassword". Note that to use this property, you also need to set the keyStoreLocation and keyStoreSecret properties for the Java Key Store.

For more information, visit Using Always Encrypted with the JDBC Driver.
keyStoreLocation

String

null
When keyStoreAuthentication=JavaKeyStorePassword, the keyStoreLocation property identifies the path to the Java keystore file that stores the column master key to be used with Always Encrypted data. Note that the path must include the keystore filename.

For more information, visit Using Always Encrypted with the JDBC Driver.
keyStoreSecret

String

null
When keyStoreAuthentication=JavaKeyStorePassword, the keyStoreSecret property identifies the password to use for the keystore as well as for the key. Note that for using the Java Key Store the keystore and the key password must be the same.

For more information, visit Using Always Encrypted with the JDBC Driver.
lastUpdateCount

boolean
["true" | "false"]

true
A "true" value only returns the last update count from an SQL statement passed to the server, and it can be used on single SELECT, INSERT, or DELETE statements to ignore additional update counts caused by server triggers. Setting this property to "false" causes all update counts to be returned, including those returned by server triggers.

Note: This property only applies when it is used with the executeUpdate methods. All other execute methods return all results and update counts. This property only affects update counts returned by server triggers. It does not affect result sets or errors that result as part of trigger execution.
lockTimeout

int

-1
The number of milliseconds to wait before the database reports a lock time-out. The default behavior is to wait indefinitely. If it is specified, this value is the default for all statements on the connection. Note that Statement.setQueryTimeout() can be used to set the time-out for specific statements. The value can be 0, which specifies no wait.
loginTimeout

int
[0..65535]

15
The number of seconds the driver should wait before timing out a failed connection. A zero value indicates that the timeout is the default system timeout, which is specified as 15 seconds by default. A non-zero value is the number of seconds the driver should wait before timing out a failed connection.

If you specify a Virtual Network Name in the Server connection property, you should specify a timeout value of three minutes or more to allow sufficient time for a failover connection to succeed. See JDBC Driver Support for High Availability, Disaster Recovery for more information.
multiSubnetFailover

Boolean

false
Always specify multiSubnetFailover=true when connecting to the availability group listener of a SQL Server 2012 (11.x) availability group or a SQL Server 2012 (11.x) Failover Cluster Instance. multiSubnetFailover=true configures Microsoft JDBC Driver for SQL Server to provide faster detection of and connection to the (currently) active server. Possible values are true and false. See JDBC Driver Support for High Availability, Disaster Recovery for more information.

You can programmatically access the multiSubnetFailover connection property with getPropertyInfo, getMultiSubnetFailover, and setMultiSubnetFailover.

Note: Beginning with Microsoft JDBC Driver 6.0 for SQL Server, it is no longer required to set multiSubnetFailover to "true" when connecting to an Availability Group Listener. A new property, transparentNetworkIPResolution, which is enabled by default, provides the detection of and connection to the (currently) active server.
packetSize

int
[-1 | 0 | 512..32767]

8000
The network packet size used to communicate with SQL Server, specified in bytes. A value of -1 indicates using the server default packet size. A value of 0 indicates using the maximum value, which is 32767. If this property is set to a value outside the acceptable range, an exception occurs.

Important: We do not recommend using the packetSize property when the encryption is enabled (encrypt=true). Otherwise, the driver might raise a connection error. For more information, see the setPacketSize method of the SQLServerDataSource class.
password

String
[<=128 char]

null
The database password, in case of connection with SQL user and password.
For Kerberos connection with principal name and password, this property is set to Kerberos Principal password.
portNumber,
port

int
[0..65535]

1433
The port where SQL Server is listening. If the port number is specified in the connection string, no request to SQLbrowser is made. When the port and instanceName are both specified, the connection is made to the specified port. However, the instanceName is validated and an error is thrown if it does not match the port.

Important: We recommend that the port number is always specified, as this is more secure than using SQLbrowser.
queryTimeout

int

-1
The number of seconds to wait before a timeout has occurred on a query. The default value is -1, which means infinite timeout. Setting this to 0 also implies to wait indefinitely.
responseBuffering

String
["full" | "adaptive"]

adaptive
If this property is set to "adaptive", the minimum possible data is buffered when necessary. The default mode is "adaptive."

When this property is set to "full", the entire result set is read from the server when a statement is executed.

Note: After upgrading the JDBC driver from version 1.2, the default buffering behavior will be "adaptive." If your application has never set the "responseBuffering" property and you want to keep the version 1.2 default behavior in your application, you must set the responseBufferring property to "full" either in the connection properties or by using the setResponseBuffering method of the SQLServerStatement object.
selectMethod

String
["direct" | "cursor"]

direct
If this property is set to "cursor," a database cursor is created for each query created on the connection for TYPE_FORWARD_ONLY and CONCUR_READ_ONLY cursors. This property is typically required only if the application generates large result sets that cannot be fully contained in client memory. When this property is set to "cursor," only a limited number of result set rows are retained in client memory.

The default behavior is that all result set rows are retained in client memory. This behavior provides the fastest performance when the application is processing all rows.
sendStringParameters...
AsUnicode

boolean
["true" | "false"]

true
sendStringParametersAsUnicode

If the sendStringParametersAsUnicode property is set to "true", String parameters are sent to the server in Unicode format.

If the sendStringParametersAsUnicode property is set to “false", String parameters are sent to the server in non-Unicode format such as ASCII/MBCS instead of Unicode.

The default value for the sendStringParametersAsUnicode property is "true".

Note: The sendStringParametersAsUnicode property is only checked when sending a parameter value with CHAR, VARCHAR, or LONGVARCHAR JDBC types. The new JDBC 4.0 national character methods, such as the setNString, setNCharacterStream, and setNClob methods of SQLServerPreparedStatement and SQLServerCallableStatement classes, always send their parameter values to the server in Unicode regardless of the setting of this property.

For optimal performance with the CHAR, VARCHAR, and LONGVARCHAR JDBC data types, an application should set the sendStringParametersAsUnicode property to "false" and use the setString, setCharacterStream, and setClob non-national character methods of the SQLServerPreparedStatement and SQLServerCallableStatement classes.

When the application sets the sendStringParametersAsUnicode property to "false" and uses a non-national character method to access Unicode data types on the server side (such as nchar, nvarchar and ntext), some data might be lost if the database collation does not support the characters in the String parameters passed by the non-national character method.

Note that an application should use the setNString, setNCharacterStream, and setNClob national character methods of the SQLServerPreparedStatement and SQLServerCallableStatement classes for the NCHAR, NVARCHAR, and LONGNVARCHAR JDBC data types.
sendTimeAsDatetime

boolean
["true" | "false"]

false
This property was added in SQL Server JDBC Driver 3.0.

Set to "true" to send java.sql.Time values to the server as SQL Server datetime values.
Set to "false" to send java.sql.Time values to the server as SQL Server time values.

The default value for this property may change in a future release.

For more information about how the Microsoft JDBC Driver for SQL Server configures java.sql.Time values before sending them to the server, see Configuring How java.sql.Time Values are Sent to the Server.
serverName,
server

String

null
The computer running SQL Server.

You can also specify the Virtual Network Name of a AlwaysOn Availability Groups availability group. See JDBC Driver Support for High Availability, Disaster Recovery for more information.
serverNameAsACE

boolean
["true" | "false"]

false
Beginning with Microsoft JDBC Driver 6.0 for SQL Server, set to "true" to indicate that the driver should translate the Unicode server name to ASCII compatible encoding (Punycode) for the connection. If this setting is false, the driver connects using the server name as provided by the user.

See International Features of the JDBC Driver for more details.
serverPreparedStatement...
DiscardThreshold

Integer

10
serverPreparedStatementDiscardThreshold

Beginning with JDBC Driver 6.2 for SQL Server, this property can be used to control how many outstanding prepared statement discard actions (sp_unprepare) can be outstanding per connection before a call to clean up the outstanding handles on the server is executed.

If this property is set to <= 1, unprepare actions are executed immediately on prepared statement close. If it is set to >1 these calls are batched together to avoid overhead of calling sp_unprepare too often.
serverSpn

String

null
Beginning in Microsoft JDBC Driver 4.2 for SQL Server, this optional property can be used to specify the Service Principal Name (SPN) for a Java Kerberos connection. It is used in conjunction with authenticationScheme.

To specify the SPN, it can be in the form of: “MSSQLSvc/fqdn:port@REALM” where fqdn is the fully qualified domain name, port is the port number, and REALM is the Kerberos realm of the SQL Server in upper-case letters.

Note: the @REALM is optional if the default realm of the client (as specified in the Kerberos configuration) is the same as the Kerberos realm for the SQL Server.

For more information on using serverSpn with Java Kerberos, see Using Kerberos Integrated Authentication to Connect to SQL Server.
statementPooling...
CacheSize

int

0
statementPoolingCacheSize

Beginning with JDBC Driver 6.4 for SQL Server, this property can be used to enable Prepared Statement Handle Caching in the driver.

This property defines the size of the cache for statement pooling.

This property can only be used in conjunction with disableStatementPooling connection proeprty which should be set to "false". Setting disableStatementPooling to "true" or statementPoolingCacheSize to 0 disables prepared statement handle caching.
socketTimeout

int

0
The number of milliseconds to wait before a timeout is occurred on a socket read or accept. The default value is 0, which means infinite timeout.
sslProtocol

String

TLS
Beginning with JDBC Driver 6.4 for SQL Server, this property can used to specify TLS protocol to be considered during secure connection.
Possible values are: TLS, TLSv1, TLSv1.1, and TLSv1.2.

For more information, see SSLProtocol.
transparentNetwork...
IPResolution

boolean
["true" | "false"]

true
transparentNetworkIPResolution

Beginning with Microsoft JDBC Driver 6.0 for SQL Server, this property provides faster detection of and connection to the (currently) active server. Possible values are "true" and "false" where "true" is the default value.

Prior to Microsoft JDBC Driver 6.0 for SQL Server, an application had to set the connection string to include “multiSubnetFailover=true” to indicate that it was connecting to an AlwaysOn Availability Group. Without setting the multiSubnetFailover connection keyword to "true", an application might experience a timeout while connecting to an AlwaysOn Availability Group. Beginning with Microsoft JDBC Driver 6.0 for SQL Server, an application does not need to set multiSubnetFailover to true anymore.

Note: When transparentNetworkIPResolution=true, the first connection attempt uses 500 ms as the timeout. Any subsequent attempts use the same timeout logic as used by the multiSubnetFailover property.
trustManagerClass

String

null
The fully qualified class name of a custom javax.net.ssl.TrustManager implementation.
trustManager...
ConstructorArg

String

null
trustManagerConstructorArg

An optional argument to pass to the constructor of the TrustManager. If trustManagerClass is specified and an encrypted connection is requested, the custom TrustManager is used rather than the default system JVM keystore-based TrustManager.
trustServerCertificate

boolean
["true" | "false"]

false
Set to "true" to specify that the Microsoft JDBC Driver for SQL Server does not validate the SQL Server SSL certificate.

If "true", the SQL Server SSL certificate is automatically trusted when the communication layer is encrypted using SSL.

If "false", the Microsoft JDBC Driver for SQL Server validates the server SSL certificate. If the server certificate validation fails, the driver raises an error and terminate the connection. The default value is "false". Make sure the value passed to serverName exactly matches the Common Name (CN) or DNS name in the Subject Alternate Name in the server certificate for an SSL connection to succeed. For more information, see Understanding SSL Support.

Note: This property is used in combination with the encrypt/authentication properties. This property only affects server SSL certificate validation if and only if the connection uses SSL encryption.
trustStore

String

null
The path (including filename) to the certificate trustStore file. The trustStore file contains the list of certificates that the client trusts.

When this property is unspecified or set to null, the driver relies on the trust manager factory's lookup rules to determine which certificate store to use.

The default SunX509 TrustManagerFactory tries to locate the trusted material in the following search order:

A file specified by the "javax.net.ssl.trustStore" Java Virtual Machine (JVM) system property.

"<java-home>/lib/security/jssecacerts" file.

"<java-home>/lib/security/cacerts" file.



For more information, see the SUNX509 TrustManager Interface documentation on the Sun Microsystems Web site.

Note: This property only affects the certificate trustStore lookup, if and only if the connection uses SSL encryption and the trustServerCertificate property is set to "false".
trustStorePassword

String

null
The password used to check the integrity of the trustStore data.

If the trustStore property is set but the trustStorePassword property is not set, the integrity of the trustStore is not checked.

When both trustStore and trustStorePassword properties are unspecified, the driver uses the JVM system properties, "javax.net.ssl.trustStore" and "javax.net.ssl.trustStorePassword". If the "javax.net.ssl.trustStorePassword" system property is not specified, the integrity of the trustStore is not checked.

If the trustStore property is not set but the trustStorePassword property is set, the JDBC driver uses the file specified by the "javax.net.ssl.trustStore" as a trust store and the integrity of the trust store is checked by using the specified trustStorePassword. This might be needed when the client application does not want to store the password in the JVM system property.

Note: The trustStorePassword property only affects the certificate trustStore lookup, if and only if the connection uses SSL connection and the trustServerCertificate property is set to "false".
trustStoreType

String

JKS
Set this property to specify trust store type to be used for FIPS mode.

Possible values are either PKCS12 or type defined by FIPS provider.
useBulkCopyFor...
BatchInsert

boolean
["true" | "false"]

false
useBulkCopyForBatchInsert

Beginning with Microsoft JDBC Driver 7.0 for SQL Server, this connection property can be enabled to make use of Bulk Copy API when performing batch insert operations using java.sql.PreparedStatement for performance improvement.

This feature is functional only when target server is of type Azure Data Warehouse. It is disabled by default, set this property to "true" to enable this feature.
Important Note: This feature only supports fully parameterized INSERT queries. If the INSERT Queries are combined by other SQL queries, or contain data in values, execution will fall back to the basic Batch Insert operation.

For more information on how to use this property, see Using Bulk Copy API for Batch Insert Operation
userName,
user

String
[<=128 char]

null
The database user, in case of connection with SQL user and password.

For Kerberos connection with principal name and password, this property is set to Kerberos Principal name.
workstationID

String
[<=128 char]

<empty string>
The workstation ID. Used to identify the specific workstation in various SQL Server profiling and logging tools.

If none is specified, the <empty string> is used.
xopenStates

boolean
["true" | "false"]

false
Set to "true" to specify that the driver returns XOPEN-compliant state codes in exceptions.

The default is to return SQL 99 state codes.
   

Note

The Microsoft JDBC Driver for SQL Server takes the server default values for connection properties except for ANSI_DEFAULTS and IMPLICIT_TRANSACTIONS. The Microsoft JDBC Driver for SQL Server automatically sets ANSI_DEFAULTS to ON and IMPLICIT_TRANSACTIONS to OFF.

Important

If authentication is set to ActiveDirectoryPassword, the following library needs to be included in classpath: azure-activedirectory-library-for-java. It can be found on Maven Repository. The simplest way to download the library and its dependencies is using Maven:

  1. First, install Maven on your system
  2. Go to the GitHub page of the driver
  3. Download the pom.xml file
  4. Run the following Maven command to download the library and its dependencies: mvn dependency:copy-dependencies

See Also

Connecting to SQL Server with the JDBC Driver
FIPS Mode