General information

JDBC Driver

It is recommended to use the official Microsoft JDBC Driver for SQL Server (v4.0) to connect PDI to a Microsoft SQL Server.

For Integrated Security authentication, the JDBC driver requires the bundled "DLL" file to also be added.  The DLL file can be added to the "libswt/win64/" directory of the data-integration client.

Open-Source MS SQL Driver (jTDS)

The jTDS driver is used to connect to MS SQL Server. Most questions can be solved by the jTDS FAQ:

http://jtds.sourceforge.net/faq.html

Windows Single Sign On (SSO) Authentication using

...

NTLM

Just leave the username and password blank and jTDS uses the ntlmauth.dll that is placed in the distributions \libswt\win32 folder and logs on with the logged in user credentials.

For further information search for NTML NTLM in the jTDS FAQ. E.g. you can also specify the domain option:

...

We look forward to your experiences and comments.

BLOBS in MS SQL Server

Here is a nice article "Storing Images and BLOB files in SQL Server":
http://www.databasejournal.com/features/mssql/article.php/3719221/Storing-Images-and-BLOB-files-in-SQL-Server.htm
http://www.databasejournal.com/features/mssql/article.php/3724556/Storing-Images-and-BLOB-files-in-SQL-Server-Part-2.htm

Note: The CREATE statement includes UNKNOWN instead of IMAGE [backwards compatible but should be avoided since SQL 2005] or VARBINARY [since SQL 2005]. Please see feature request PDI-2969.