SQL Server:
Datagaps ETL Validator v3.4.3 and later versions come with the JTDS JDBC driver to connect with SQL Server. There are two options to setup a connection to SQL Server :
- SQL Server Authentication
- Windows Authentication.
SQL Server Authentication:
Using SQL Server Authentication is straight forward assuming that the SQL Server is setup to accept this type of authentication mechanism.
- Create a connection from ETL Validator for SQL Server and pick the SQL Server Authentication option. Provide valid username and password and select the connection type with respect to the host name and port. Click on Retrieve Databases to get the Database list and select the required Database. Click on test to connect successfully and save the connection.
SQL Server Windows Authentication:
SQL Server knows to check Active Directory to see if the account is active, password works, and then checks what level of permissions are granted to the single SQL server instance when using this account.
- Create a connection from ETL Validator for SQL Server and pick the Windows Authentication option. Provide valid username and password and select the connection type as custom and provide the connection URL. Click on test to connect successfully and save the connection.
Using Use Cursors:
SQL server allows ETL Validator to use cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time.
JDBC/Conneciton URL: jdbc:jtds:sqlserver://<replace with hostname>:<port number>/<database name>; useCursors=true;domain=<domain name>
Using NTLM:
SQL Server allows ETL Validator to use the authentication Schema connection property to indicate that it wants to connect to a database using NTLM v2 Authentication.
JDBC/Conneciton URL: jdbc:jtds:sqlserver://<replace with hostname>:<port number>/<database name>; useNTLMv2=true;domain=<domain name>
