Setting the connection properties
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. For connection string syntax, see Building the connection URL.
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:
- API arguments (such as user and password)
- Property collection
- Last instance in the connection string
Unknown values are allowed for the property names, and the JDBC driver doesn't validate for case sensitivity.
Synonyms are allowed and are resolved in order, just as duplicate property names.
Properties
The following table lists all the currently available connection string properties for the JDBC driver.
| Property Type Default |
Description |
|---|---|
| accessToken String null |
(Version 6.0+) Use this property to connect to a database using an access token. accessToken can't be set using the connection URL. |
| accessTokenCallbackClass String null |
(Version 12.4+) The name of the callback-implementing class to be used with access token callback. |
| applicationIntent String ReadWrite |
(Version 6.0+) Declares the application workload type to connect to a server. Possible values are ReadOnly and ReadWrite. For more information about disaster recovery, 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 |
(Version 6.0+) This optional property indicates which authentication method to use for connection. Possible values are ActiveDirectoryIntegrated, ActiveDirectoryPassword, ActiveDirectoryManagedIdentity (version 12.2+), ActiveDirectoryMSI (version 7.2+), ActiveDirectoryInteractive (version 9.2+), ActiveDirectoryServicePrincipal (version 9.2+), SqlPassword, and the default NotSpecified. Use ActiveDirectoryIntegrated (version 6.0+) to connect to SQL using integrated Windows authentication. Use ActiveDirectoryPassword (version 6.0+) to connect to SQL using a Microsoft Entra principal name and password. Use ActiveDirectoryManagedIdentity (version 12.2+) or ActiveDirectoryMSI (version 7.2+) to connect to SQL from inside an Azure Resource. For example, an Azure Virtual Machine, App Service or Function App using managed identity authentication. The two types of managed identities supported by the driver when using ActiveDirectoryManagedIdentity or ActiveDirectoryMSI authentication mode are: 1. System-Assigned Managed Identity: Used to acquire accessToken by default. 2. User-Assigned Managed Identity: Used to acquire accessToken if the Client ID of a managed identity is specified with the msiClientId connection property. Use ActiveDirectoryInteractive to connect to a database using an interactive authentication flow. Use ActiveDirectoryServicePrincipal (version 9.2+) to connect to a database using the client ID and secret of a service principal identity. Specify client ID in the userName property and secret in the password property (10.2+). Use SqlPassword to connect to SQL 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 must be installed: mssql-jdbc_auth-<version>-<arch>.dll (available in the JDBC driver package) and Microsoft Authentication Library for SQL Server (ADAL.DLL). Microsoft Authentication Library can be installed from Microsoft ODBC Driver for SQL Server or Microsoft OLE DB Driver for SQL Server. The JDBC driver only supports version 1.0.2028.318 and higher for ADAL.DLL. Note: When the authentication property is set to any value other than NotSpecified, the driver by default uses Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL), encryption. For information on how to configure Microsoft Entra authentication, see Use Microsoft Entra authentication. |
| authenticationScheme String NativeAuthentication |
Indicates which kind of integrated security you want your application to use. Possible values are JavaKerberos, NTLM (version 7.4+), and the default NativeAuthentication. NativeAuthentication causes the driver to load mssql-jdbc_auth-<version>-<arch>.dll (for example, mssql-jdbc_auth-8.2.2.x64.dll) on Windows, which is used to obtain integrated authentication information. (The native authentication library loaded is named sqljdbc_auth.dll when using driver versions 6.0 through 7.4.) 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 about using authenticationScheme=JavaKerberos, see Using Kerberos integrated authentication to connect to SQL Server. When using authenticationScheme=NTLM, you must specify the Windows domain by using the domain or domainName property, the Windows credentials in the user or userName property, and the password property. Otherwise, an error occurs (connection properties must be specified). |
| cacheBulkCopyMetadata boolean ["true" | "false"] false |
(Version 12.8+) When using useBulkCopyForBatchInsert=true, this property is used to tell the driver whether it should cache destination column metadata at the connection level. If set to true, make sure the destination doesn't change between bulk inserts, as the driver doesn't have a way of handling this change. |
| calcBigDecimalPrecision boolean ["true" | "false"] false |
(Version 12.6+) Flag to indicate whether the driver should calculate precision for BigDecimal inputs, as opposed to using the maximum allowed valued for precision (38). |
| cancelQueryTimeout int -1 |
(Version 6.4+) This property can be used to cancel a queryTimeout set on the connection. Query execution hangs and doesn't throw an exception if the TCP connection to the 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. |
| clientCertificate String null |
(Version 8.4+) Specifies the location of the certificate to be used for client certificate authentication. The JDBC driver supports PFX, PEM, DER, and CER file extensions. For details, see Client Certificate Authentication for Loopback Scenarios. |
| clientKey String null |
(Version 8.4+) Specifies the location of the private key for PEM, DER, and CER certificates specified by the clientCertificate attribute. For details, see Client Certificate Authentication for Loopback Scenarios. |
| clientKeyPassword String null |
(Version 8.4+) Specifies the optional password string for accessing the clientKey file's private key. For details, see Client Certificate Authentication for Loopback Scenarios. |
| columnEncryptionSetting String ["Enabled" | "Disabled"] Disabled |
(Version 6.0+) Set to "Enabled" to use the Always Encrypted (AE) feature. When AE is enabled, the JDBC driver transparently encrypts and decrypts sensitive data stored in encrypted database columns on the server. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. Note: Always Encrypted is available with SQL Server 2016 or later and Azure SQL Database. |
| connectRetryCount int [0..255] 1 |
(Version 9.4+) The number of reconnection attempts if there's a connection failure. |
| connectRetryInterval int [1..60] 10 |
(Version 9.4+) The number of seconds between each connection retry attempt. |
| 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. |
| datetimeParameterType String ["datetime" | "datetime2" | "datetimeoffset"] datetime2 |
(Version 12.2+) The SQL data type to use for Java date/timestamp parameters. When one is connecting to SQL Server 2016 or higher, and interacting with legacy "datetime" values, clients may benefit from setting the property to "datetime". This setting mitigates server-side conversion issues between "datetime" and "datetime2" values. For more information, see Addressing datetime to datetime2 conversion behavior change starting from SQL Server 2016 |
| delayLoadingLobs boolean ["true" | "false"] true |
Flag to indicate whether to stream or not stream all the LOB objects being retrieved from the ResultSet. Setting this property to "false" loads the entire LOB object into memory without streaming. |
| domainName, domain String null |
(Version 7.4+) The Windows domain to authenticate to when using NTLM authentication. |
| disableStatementPooling boolean ["true" | "false"] true |
Flag indicates if statement pooling should be used. |
| enablePrepareOnFirst... PreparedStatementCall boolean ["true" | "false"] false |
Set to "true" to enable prepared statement handle creation by calling sp_prepexec with the first execution of a prepared statement. Set to "false" to change the first execution of a prepared statement to call sp_executesql and not prepare a statement. If a second execution happens, it calls sp_prepexec to set up a prepared statement handle. |
| enclaveAttestationUrl String null |
(Version 8.2+) This optional property indicates the attestation service endpoint URL to use for Always Encrypted with secure enclaves. For more information about Always Encrypted with secure enclaves, see Always Encrypted with secure enclaves. |
| enclaveAttestationProtocol String null |
(Version 8.2+) This optional property indicates the attestation protocol to use for Always Encrypted with secure enclaves. Currently, the only supported values for this field are HGS, AAS, and NONE (NONE is only supported in version 11.2+). For more information about Always Encrypted with secure enclaves, see Always Encrypted with secure enclaves. |
| encrypt String null |
Set to "true" to specify that the SQL Server uses TLS encryption for all the data sent between the client and the server if the server has a certificate installed. The default value is "true" in version 10.2 and later and "false" in 9.4 and earlier. In version 6.0 and up, there's a new connection setting 'authentication' that uses TLS encryption by default. For more information about this property, see the 'authentication' property. In version 11.2.0 and up, encrypt was changed from boolean to string, allowing for TDS 8.0 support when the property is set to strict. |
| 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 with the databaseName property. Note: The driver doesn't support the server instance port number for the failover partner instance as part of the failoverPartner property in the connection string. However, the driver does support 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. If you specify a Virtual Network Name in the Server connection property, you can't use database mirroring. For more information about disaster recovery, see JDBC driver support for High Availability, disaster recovery |
fipsboolean ["true" | "false"] "false" |
For FIPS enabled Java Virtual Machine (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. For more information, see GitHub issue 460. |
| gsscredential org.ietf.jgss.GSSCredential null |
(Version 6.2+) User credentials to be used for Kerberos Constrained Delegation can be passed in this property. This setting should be used with integratedSecurity as true and JavaKerberos as authenticationScheme. |
| hostNameInCertificate String null |
The host name to be used to validate the SQL Server TLS/SSL certificate. The hostNameInCertificate option can be used to specify the host name in situations where the name, or names, used in the certificate doesn't match the name passed in to the serverName property. If there's a match however, the hostNameInCertificate option shouldn't be used. In situations where 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 TLS/SSL certificate. Note: As the prior paragraph describes, it isn't recommended to set the hostNameInCertificate option unless you can confirm the name, or names, in the certificate don't match those names passed in the serverName option. Note: This property is used in combination with the encrypt/authentication properties and the trustServerCertificate property. This property affects the certificate validation, if the connection uses TLS encryption and the trustServerCertificate is set to "false". Make sure the value passed to hostNameInCertificate matches the Common Name (CN) or DNS name in the Subject Alternate Name (SAN) in the server certificate for a TLS connection to succeed. For more information about encryption support, see Understanding encryption support. |
| instanceName String [<=128 char] null |
The database instance name to connect to. When it isn't 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 can't use the instanceName connection property. For more information about disaster recovery, see JDBC Driver Support for High Availability, Disaster Recovery. |
| 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 were provided when a user signed in to the computer or network. Set to "true" (with authenticationscheme=JavaKerberos), to indicate that Kerberos credentials are used by SQL Server. For more information about Kerberos authentication, see Using Kerberos integrated authentication to connect to SQL Server. Set to "true" (with authenticationscheme=NTLM), to indicate that NTLM credentials are used by SQL Server. If "false", the username and password must be supplied. |
| ipaddresspreference String [<=128 char] IPv4First |
The IP preference used by the client application. With IPV4First, the driver traverses IPv4 addresses first. If no IPv4 addresses can be connected to successfully, the driver continues and tries IPv6 addresses, if there are any. With IPV6First, the driver traverses IPv6 addresses first. If no IPv6 addresses can be connected to successfully, the driver continues and tries IPv4 addresses, if there are any. With UsePlatformDefault, the driver traverses all IP addresses in their initial orders from DNS resolution. |
| jaasConfigurationName String SQLJDBCDriver |
(Version 6.2+) Each connection to SQL Server can use its own JAAS Login Configuration name to establish a Kerberos connection. The name of the configuration entry can be passed through this property. This property is intended for use when creating a Kerberos configuration file. By default, the driver looks for the name SQLJDBCDriver.If an external configuration isn't found, the driver sets useDefaultCcache = true for IBM JVMs, and useTicketCache = true for other JVMs. |
| keyStoreAuthentication String null |
(Version 6.0+) This property identifies which key store to use with Always Encrypted and determines an authentication mechanism used to authenticate to the key store. The driver supports setting up of the Java Key Store seamlessly when you set "keyStoreAuthentication=JavaKeyStorePassword". To use this property, you also must set the keyStoreLocation and keyStoreSecret properties for the Java Key Store. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. Beginning with Microsoft JDBC Driver 8.4, you can set "keyStoreAuthentication=KeyVaultManagedIdentity" or "keyStoreAuthentication=KeyVaultClientSecret" to authenticate to Azure Key Vault using Managed Identities. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. |
| keyStoreLocation String null |
(Version 6.0+) 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. The path must include the keystore filename. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. |
| keyStorePrincipalId String null |
(Version 8.4+) When keyStoreAuthentication=KeyVaultManagedIdentity, the keyStorePrincipalId property specifies a valid Microsoft Entra application client ID. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. |
| keyStoreSecret String null |
(Version 6.0+) When keyStoreAuthentication=JavaKeyStorePassword, the keyStoreSecret property identifies the password to use for the keystore and the key. When one is using the Java Key Store, the keystore and the key password must be the same. For more information about Always Encrypted, see Using Always Encrypted with the JDBC driver. |
| lastUpdateCount boolean ["true" | "false"] true |
A "true" value only returns the last update count from a SQL statement that is passed to the server. As well, it's used on only single SELECT, INSERT, or DELETE statements to ignore other update counts that are server triggers can cause. Setting this property to "false" causes all update counts to be returned, including this update counts returned by server triggers. Note: This property only applies when it's 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 doesn't 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 the user has not specified a value for this property, this value is the default for all statements on the connection. Alternatively, Statement.setQueryTimeout() can be used to set the query time-out for specific statements. The value can be 0, which specifies no wait. |
| loginTimeout int [0..65535] 30 (version 11.2 and up) 15 (version 10.2 and below) |
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. This value is either 30 seconds (the default in version 11.2 and above) or 15 seconds (the default in version 10.2 and below). A nonzero 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. For more information about disaster recovery, see JDBC driver support for High Availability, disaster recovery. |
| maxResultBuffer String null |
(Version 9.2+) maxResultBuffer can be used to set the maximum bytes to read when reading a result set. If not specified, then the entire result set is read. Size can be specified in two styles: 1. as size of bytes (for example, 100, 150M, 300K, 400G)2. as a percent of maximum heap memory (for example, 10p, 15pct, 20percent). |
| msiClientId String null |
(Deprecated) (Version 7.2+) The Client ID of the Managed Identity (MSI) used to acquire an accessToken to establish a connection with the ActiveDirectoryManagedIdentity or ActiveDirectoryMSI authentication mode. |
| multiSubnetFailover Boolean false |
Always specify multiSubnetFailover=true to connect to the availability group listener of a SQL Server availability group or an SQL Server Failover Cluster Instance. multiSubnetFailover=true configures the driver to provide faster detection of and connection to the (currently) active server. Possible values are true and false. For more information about disaster recovery, see JDBC Driver Support for High Availability, Disaster Recovery. 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's no longer required to set multiSubnetFailover to "true" to connect 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 the server, specified in bytes. A value of -1 indicates to use the server's default packet size. A value of 0 indicates to use the maximum value of 32767. If this property is set to a value outside the acceptable range, an exception occurs. Important: Using the packetSize property when the encryption is enabled (encrypt=true) isn't recommended. Otherwise, the driver might raise a connection error. For more information about this property, see the setPacketSize method of the SQLServerDataSource class. |
| password String [<=128 char] null |
The database password, if connection with SQL user and password. For Kerberos connection with principal name and password, this property is set to the Kerberos Principal password. (Version 10.2+) When authentication=ActiveDirectoryServicePrincipal, the password property identifies the password to use for the Active Directory principal. |
| portNumber, port int [0..65535] 1433 |
The port where the 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 doesn't match the port. Important: We recommend that the port number is always specified, as it's more secure than using SQLbrowser. |
| prepareMethod String prepexec |
(Version 11.2.0+) Specifies the underlying prepare method to be used by the driver with prepared statements. Set to prepare to use sp_prepare as the prepare method. Setting prepareMethod to this value results in a separate, initial trip to the database to prepare the statement without any initial values for the database to consider in the execution plan. Set to prepexec to use sp_prepexec as the prepare method. This method combines the prepare action with the first execute, reducing round trips. It also provides the database with initial parameter values that the database may consider in the execution plan. |
| queryTimeout int -1 |
The number of seconds to wait before a timeout happens on a query. The default value is -1, which means infinite timeout. Set this value to 0 also implies to wait indefinitely. |
| realm String null |
(Version 9.4+) The realm for Kerberos authentication. Set this value to override the Kerberos authentication realm the driver autodetects from the server's realm. |
| replication boolean ["true" | "false"] false |
(Version 9.4+) This setting tells the server if the connection is used for replication. When enabled, triggers with the NOT FOR REPLICATION option don't fire on the connection. |
| 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." If this property is set to "full", the entire result set is read from the server when a statement is executed. Note: After JDBC driver version 1.2, the default buffering behavior is "adaptive." If you want to keep the version 1.2 default behavior in your application, set the responseBufferring property to "full" either in the connection properties, or use 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 can't be fully contained in client memory. If this property is set to "cursor," only a limited number of result set rows are kept in client memory. The default behavior is that all result set rows are kept in client memory. This behavior provides the fastest performance when the application is processing all rows. |
| sendStringParameters... AsUnicode boolean ["true" | "false"] true |
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 to send a parameter value with CHAR, VARCHAR, or LONGVARCHAR JDBC types. The new JDBC 4.0 national character methods include methods such as setNString, setNCharacterStream, and setNClob of the SQLServerPreparedStatement and SQLServerCallableStatement classes. These methods 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 doesn't support the characters in the String parameters passed by the non-national character method. 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. Changing this value can affect sorting of results from the database. The sorting differences are due to different sorting rules for Unicode versus non-Unicode characters. |
| sendTemporalDataTypes... AsStringForBulkCopy boolean ["true" | "false"] true |
(Version 8.4+) This connection property, when set to "false", sends DATE, DATETIME, DATIMETIME2, DATETIMEOFFSET, SMALLDATETIME, and TIME datatypes as their respective types instead of sending them as String. With this connection property set to "false", the driver accepts the default string literal format of each temporal datatype, for example: DATE: YYYY-MM-DDDATETIME: YYYY-MM-DD hh:mm:ss[.nnn]DATETIME2: YYYY-MM-DD hh:mm:ss[.nnnnnnn]DATETIMEOFFSET: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+/-}hh:mm]SMALLDATETIME: YYYY-MM-DD hh:mm:ssTIME: hh:mm:ss[.nnnnnnn] |
| sendTimeAsDatetime boolean ["true" | "false"] true |
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 is currently "true" and might change in a future release. For more information about how the Microsoft JDBC Driver for SQL Server configures java.sql.Time values before it sends them to the server, see Configuring How java.sql.Time Values are Sent to the Server. |
| serverCertificate, server String null |
(Version 11.2.0+) The path to the server certificate file. Used for validation when using encrypt set to strict. The driver supports certificate files using the PEM file format. |
| serverName, server String null |
The computer running SQL Server or an Azure SQL database. You can also specify the Virtual Network Name of an availability group. For more information about disaster recovery, see JDBC driver support for High Availability, disaster recovery. |
| serverNameAsACE boolean ["true" | "false"] false |
(Version 6.0+) 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 uses the server name as provided to connect. For more information about international features, see International features of the JDBC driver. |
| serverPreparedStatement... DiscardThreshold Integer 10 |
(Version 6.2+) 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 the property is set to > 1, these calls are batched together to avoid overhead of calling sp_unprepare too often. |
| serverSpn String null |
(Version 4.2+) This optional property can be used to specify the Service Principal Name (SPN) for a Java Kerberos connection. It's used 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 about using serverSpn with Java Kerberos, see Using Kerberos integrated authentication to connect to SQL Server. |
| socketFactoryClass String null |
(Version 8.4+) Specifies the class name for a custom socket factory to be used instead of the default socket factory. |
| 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. |
| statementPooling... CacheSize int 0 |
(Version 6.4+) 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 with the disableStatementPooling connection property, which should be set to "false". Setting disableStatementPooling to "true" or statementPoolingCacheSize to 0 disables prepared statement handle caching. |
| sslProtocol String TLS |
(Version 6.4+) This property can be used to specify the TLS protocol to be considered during secure connection. Possible values are: TLS, TLSv1, TLSv1.1, and TLSv1.2. For more information about the Secure Sockets Layer protocol, see SSLProtocol. |
| transparentNetwork... IPResolution boolean ["true" | "false"] true |
(Version 6.0+) 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. Before 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 Always On Availability Group. Without setting the multiSubnetFailover connection keyword to "true", an application might experience a timeout while connecting to an Always On Availability Group. With version 6.0 and up, an application isn't required to set multiSubnetFailover to true anymore. Note: When transparentNetworkIPResolution=true, the first connection attempt uses 500 ms as the timeout. Any later attempts use the same timeout logic as used by the multiSubnetFailover property. |
| trustManagerClass String null |
(Version 6.4+) The fully qualified class name of a custom javax.net.ssl.TrustManager implementation. |
| trustManager... ConstructorArg String null |
(Version 6.4+) An optional argument to pass to the constructor of the TrustManager. If the trustManagerClass property is specified and an encrypted connection is requested, the custom TrustManager is used instead of the default system JVM keystore-based TrustManager. |
| trustServerCertificate boolean ["true" | "false"] false |
Set to "true" to specify that the driver doesn't validate the server TLS/SSL certificate. If "true", the server TLS/SSL certificate is automatically trusted when the communication layer is encrypted using TLS. If "false", the driver validates the server TLS/SSL certificate. If the server certificate validation fails, the driver raises an error and closes 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 a TLS/SSL connection to succeed. For more information about encryption support, see Understanding encryption support. Note: This property is used in combination with the encrypt/authentication properties. This property only affects server TLS/SSL certificate validation if the connection uses TLS 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" JVM system property. <java-home>/lib/security/jssecacerts file.<java-home>/lib/security/cacerts file.For more information about the SUNX509 TrustManager Interface, see the SUNX509 TrustManager Interface documentation on the Sun Microsystems Web site. Note: This property only affects the certificate trustStore lookup, if the connection uses TLS 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 isn't set, the integrity of the trustStore isn't 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 isn't specified, the integrity of the trustStore isn't checked. If the user does not set the trustStore property but they do set the trustStorePassword property, the JDBC driver uses the file that the "javax.net.ssl.trustStore" specifies as a trust store. Furthermore, the driver checks the integrity of the trust store by using the specified trustStorePassword. This setting is needed when the client application doesn't want to store the password in the JVM system property. Note: The trustStorePassword property only affects the certificate trustStore lookup, if the connection uses TLS 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 |
(Version 9.2+) This connection property can be enabled to transparently use the Bulk Copy API when doing batch insert operations using java.sql.PreparedStatement. This feature potentially provides higher performance when enabled. This feature 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 with other SQL queries, or contain data in values, execution falls back to the basic batch insert operation. For more information about how to use this property, see Using bulk copy API for batch insert operation |
| useDefaultGSSCredential boolean ["true" | "false"] false |
(Version 12.6+) Flag to indicate whether the driver should create the GSSCredential on behalf of the user for using Native GSS-API for Kerberos authentication. |
| useDefaultJaasConfig boolean ["true" | "false"] false |
(Version 12.6+) When the application exists alongside libraries that configure JAAS at the system level, setting this property to true allows the driver to use that same configuration to perform Kerberos authentication. |
| useFmtOnly boolean ["true" | "false"] false |
(Version 7.4+) Provides an alternative way to query Parameter Metadata from the server. Set this property to "true" to specify that the driver should use SET FMTONLY logic when querying Parameter Metadata. This feature is off by default, and it isn't recommended to use this property as SET FMTONLY is marked for deprecation. useFmtOnly is made available to use only as a workaround for known issues and limitations in sp_describe_undeclared_parameters.This feature currently only supports single SELECT/INSERT/UPDATE/DELETE queries. Attempting to use this feature with unsupported/multiple queries causes the driver to attempt to parse the query, but will most likely result in an exception.For more information about this property, see Retrieving ParameterMetaData via useFmtOnly. |
| userName, user String [<=128 char] null |
The database user, if connection with SQL user and password. For Kerberos connection with principal name and password, this property is set to Kerberos Principal name. (Version 10.2+) When authentication=ActiveDirectoryServicePrincipal, the userName property specifies a valid Azure Active Directory secure client ID. |
| workstationID String [<=128 char] <empty string> |
The workstation ID. Used to identify the specific workstation in various 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 the classpath: microsoft-authentication-library-for-java. It can be found on Maven Repository. The simplest way to download the library and its dependencies is by using Maven:
- Install Maven on your system
- Go to the GitHub page of the driver
- Download the pom.xml file
- Run the following Maven command to download the library and its dependencies:
mvn dependency:copy-dependencies