Connect Pentaho to MS SQL Server (Native)
Integrated Security Setup
To connect to database with integrated security, following steps should be done:
- Download Microsoft JDBC Drivers 4.2 on https://www.microsoft.com/en-us/download/details.aspx?id=11774
- Unzip the package in a temp directory
- Close Spoon
Copy '<temp directory>\sqljdbc_4.2\enu\auth\x86\sqljdbc_auth.dll' to 'C:\Program Files (x86)\Java\jre1.8.0_73\bin' (32-bit)
- Copy '<temp directory>\sqljdbc_4.2\enu\sqljdbc42.jar' to '<Kettle installation folder>\data-integration\lib'
- Open Spoon
- Test connection in spoon
- Delete temp directory
If you upgrade Kettle, Java, JDBC Driver to a higher version, you have to repeat the above steps
Here is what worked for me to connect Pentaho PDI to a Microsoft SQL Server:
Download the official Microsoft JDBC Driver for SQL Server (v4.0) from here.
It comes bundled with two jar files, copy the file "sqljdbc4.jar" into your path
data-integration\lib.Restart Pentaho and try the connection
MS SQL server(Native)again.
Note: I used this method for Pentaho PDI (Kettle), but it should work for the rest of products of the suite.
Note2: Even if you are using windows you can download the ".tar.gz" file instead of the ".exe" because you are using it just for Pentaho.
I dont know whether you have followed these steps or not.but what i want to mention ::
1>first add jdbc driver for ms sql server add in tomcat->lib.sqljdbc4.jar
find the below image
I hope it is helpful.