# tMSSqlConnection Opens a connection to the specified database that can then be reused in the subsequent subJob or subJobs. tMSSqlConnection opens a connection to a Microsoft SQL Server database or a Microsoft Azure SQL database. ## tMSSqlConnection Standard properties These properties are used to configure tMSSqlConnection running in the Standard Job framework. The Standard tMSSqlConnection component belongs to the Databases and the ELT families. The component in this framework is available in all Talend products. Information noteNote: This component is a specific version of a dynamic database connector. The properties related to database settings vary depending on your database type selection. For more information about dynamic database connectors, see [DB Generic components](https://help.qlik.com/talend/r/en-US/8.0/components/db-generic/db-generic-component). Information noteNote: When building a Job as an OSGI Bundle for ESB, MS SQL Server drivers jtds and mssql-jdbc used by the SQL Server components are not included in the build artifact. To run the artifact in Talend Runtime, you need to deploy the drivers manually by copying the drivers to the <TalendRuntimePath>/lib folder and restart Talend Runtime. ## Basic settings | Properties | Description | | --- | --- | | Database | Select the desired database type from the list and click
Apply. | | Property type | Either Built-in or
Repository.

| | JDBC Provider | Select the provider of the JDBC driver to be used, either Microsoft (the default, recommended) or Open
source JTDS.

When Microsoft is selected, you need to download the
Microsoft JDBC Driver for SQL Server on [Microsoft Download
Center](https://www.microsoft.com/en-us/download/details.aspx?id=11774), unpack the downloaded zip file, choose a jar in the
unzipped folder based on your JRE version, rename the jar to *mssql-jdbc.jar* and install it manually. For more
information about choosing the jar, see the System Requirements
information on [Microsoft Download
Center](https://www.microsoft.com/en-us/download/details.aspx?id=11774).

Note that OSGi builds will not
include any open source jTDS JDBC driver. If you need to build your Job
containing this component as an OSGi bundle, use the official Microsoft
JDBC driver; otherwise, this component will work only with Datasource
with the jTDS JDBC driver properly installed in
Talend Runtime. | | Host | Enter the IP address or the hostname of the database server or the Azure Synapse
Analytics to be used.

If the SQL Server Browser service is running on the
machine where the server resides, you can connect to a named instance through a
TCP dynamic port by providing the host name and the instance name in this field
in the format of {host\_name}\{instance\_name}. In this
case, you can leave the Port field empty. See [SQL Server Browser service](https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16) for related
information. | | Port | Enter the listening port number of the database server or the Azure Synapse
Analytics to be used.

If the SQL Server Browser service is running on the
machine where the server resides, you can connect to a named instance through a
TCP dynamic port by providing the host name and the instance name in the
Host field and leave this field empty. See [SQL Server Browser service](https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16) for related
information. | | Schema | Enter the name of the database schema. | | Database | Enter the name of the database. | | Username and
Password | Enter the authentication data.

To enter the password, click the
[...] button next to the
Password field, enter the password in double
quotes in the pop-up dialog box, and then click
OK.

You can use
Type 2 integrated authentication on
Windows by adding
integratedSecurity=true in the
Additional JDBC Parameters field and leave
these two fields empty. See section *Connecting with integrated
authentication On Windows* at [Building the connection URL](https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?redirectedfrom=MSDN) for related
information. | | Additional JDBC parameters | Specify additional connection properties for the database
connection you are creating. The properties are separated by semicolon and
each property is a key-value pair. For example, *encrypt=true;trustServerCertificate=false;*
*hostNameInCertificate=\*.database.windows.net;loginTimeout=30;* for
Azure SQL database connection. | | Use or register a shared DB Connection | Select this checkbox to share
your database connection or fetch a database connection shared by a parent or
child Job, and in the Shared DB connection name field
displayed, enter the name for the shared database connection. This allows you
to share one single database connection (except the database schema setting)
among several database connection components from different Job levels that can
be either parent or child.

This option is
incompatible with the Use dynamic Job and Use an
independent process to run subJob options of the
tRunJob component. Using a shared connection together with a
tRunJob component with either of these two options enabled
will cause your Job to fail.

This checkbox is not available when the Specify a data source
alias checkbox is selected. | | Specify a data source alias | Select this checkbox and specify the alias of a data source created on the *Talend Runtime* side to use the shared connection pool defined in the data source configuration. This
option works only when you deploy and run your Job in *Talend Runtime*.

This checkbox is not visible when the Use or register a
shared DB Connection checkbox is selected. | | Data source alias | Enter the alias of the data source created on the *Talend Runtime* side.

This field is available only when the Specify a data
source alias checkbox is selected. | ## Advanced settings | Properties | Description | | --- | --- | | Authenticate using Azure Active
Directory | When selected, this option allows you to pick the authentication mode to use
from the Azure Active Directory authentication mode
dropdown list:



Read the Usage section of this document for
information about Microsoft SQL Server/JDBC encryption requirements.

This option is only available when
Microsoft is selected from the JDBC
Provider dropdown list in the Basic
settings view. | | Enable always encrypted (Column encryption) | Select this option to use the Microsoft [Always encrypted](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15) feature to encrypt and protect your
data. To use this option, you need to have previously stored your secrets with
[Azure Key Vault](https://learn.microsoft.com/en-us/azure/key-vault/general/basic-concepts):



Some limitations regarding query operations and statements apply to this
feature. For the exhaustive list of limitations, see the [Microsoft SQL Server
documentation](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15#limitations). | | Auto Commit | Select this checkbox to commit any changes to the database automatically
upon the transaction.

With this checkbox selected, you cannot use the corresponding commit
component to commit changes to the database; likewise, when using the
corresponding commit component, this checkbox has to be cleared. By default, the
auto commit function is disabled and changes must be committed explicitly using
the corresponding commit component.

Note that the auto commit function commits each SQL statement as a
single transaction immediately after the statement is executed while the
commit component does not commit only until all of the statements are
executed. For this reason, if you need more room to manage your transactions
in a Job, it is recommended to use the commit component. | | Share identity insert in multi table | Select this checkbox to share IDENTITY\_INSERT among multiple
Microsoft SQL tables that accept data through the same database connection.

This option prevents errors caused by setting IENTITY\_INSERT to ON (or OFF) repeatedly
when writing Microsoft SQL tables through a database connection. It is
necessary to check this option when the component is used along with
multiple tMSSqlOutput components
with their Turn on Identity
insert options checked in the Basic settings tab.

Note that checking this option may decrease the
execution performance. | | tStatCatcher Statistics | Select this checkbox to gather the Job processing metadata at a Job level as
well as at each component level. | ## Usage | Usage guidance | Description | | --- | --- | | Usage rules | This component is more commonly used with other tMSSql\* components,
especially with the tMSSqlCommit and
tMSSqlRollback components.

Information noteImportant: Microsoft SQL Server/JDBC encryption requirements

Starting
with recent versions of the Microsoft SQL Server JDBC driver, TLS encryption
is enabled by default
(encrypt=true;trustServerCertificate=false). This
enhances security, but if your SQL Server does not support encrypted
connections or does not require clients to use TLS, you must set
encrypt=false in the JDBC parameters of this component
to connect successfully.



For more information on how to use connections with encryption
properties, see [Microsoft SQL JDBC documentation](https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver17). | | Limitation | Due to license incompatibility, one or more JARs required to use this component
are not provided. You can install the missing JARs for this particular component by
clicking the Install button on the Component tab view. You can also find out and add all missing
JARs easily on the Modules tab in the Integration perspective of Talend Studio. For
details, see [Installing external modules](https://help.qlik.com/talend/r/en-US/8.0/studio-user-guide/installing-external-modules). | **Related concepts** * [Inserting data into a database table and extracting useful information from it](https://help.qlik.com/talend/en-US/components/8.0/mssql/tmssqlinput-tmssqloutput-tlogrow-inserting-data-into-database-table-and-extracting-useful-information-from-it-standard-component-the)