pentaho local and remote connection to sql server

Question

This is giving me headache. I just set up pentaho community edition on my laptop. I was able to connect to mysql server in xampp by following the instructions in the pdf documentation that comes with the enterprise edition getting_started_with_pentaho. I connect perfectly to mysql database. However I need to also connect both locally and remotely to a sql server (mssql) but follwing the same process as i did for the mysql database does not work. This is what i did: I installed SQLServer2008R2SP1-KB2528583-x64-ENU.exe sql server and logged in successfully with the following input

server type: database engine
server name: ME-PC\SQLEXPRESS
authentication: windows authentication
login: ME-PC\ME
password:

Now I downloaded the jdbc driver for mssql from here and extracted the folder to my desktop. The folder contained sqljdbc4.jar and sqljdbc.jar as well as both x64 and x86 versions of sqljdbc_auth.dll.

I am running a x64 OS but but the jdk i installed went to the Programfiles(x86) so I guess the java i had install was for x86. Due to this reason I copied sqljdbc4.jar and the x86 version of sqljdbc_auth.dll to the directorys:

C:\Users\ELRAPHA\Desktop\PENTAHO\biserver-ce\tomcat\webapps\pentaho\WEB-INF\lib

C:\Users\ELRAPHA\Desktop\PENTAHO\administration-console\jdbc

C:\Users\ELRAPHA\Desktop\PENTAHO\data-integration\libext\JDBC

I also enabled TCI/IP on my local sql server.

As I mentioned, am able to connect to sql server via the management studio but when i try to create a new connection through pentaho in the web browser with the following

hostname: localhost
database name: master
instance name: ME-PC\SQLEXPRESS
port: 1433
username: ME-PC\ME
password: 

I get the error

error connecting to database [mssqllocal] org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database

error connecting to database:(using class com.microsoft.sqlserver.JDBC.SqlServerDriver) The TCP/IP connection to the host localhost, port 1433 has failed. Error: Connection refused: connect.

I dont get what am doing wrongly especially since mysql was to straight forward. Been at this all day. I need help . Please can anyone see my error or something that am missing here? HELP

Answer 1

In case of having issues, together with empty the port number, is to manually set the instance name as a manual parameter:

instanceName=SQLEXPRESS

The default pentaho dialog (at least to 8.3) generates only the code "instance=SQLEXPRESS"

Answer 2

If you want to connect through JDBC to SQL Server via Windows Authentication, then the sqljdbc_auth.dll must be in the right location. On my system (x86) that this is relative to the install directory of the sqljdbc4 driver. By "extracted the folder to my desktop" I assume you mean you ran the installer, and that's where you choose the location of the driver. For example on my system, this file is not in the Pentaho folders, it's in:

C:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\auth\x86

I would ensure that both 32 and 64 bit versions of sqljdbc_auth.dll are in their respective locations since you seem to have a mixed system.

Alternatively, you can use SQL Server authentication which is simpler, but doesn't integrate with AD, etc.

EDIT:

Oops, I forgot something very important. The JVM must be made aware of the location of sqljdbc_auth.dll. I had to add the path of that dll to %LIBSPATH% in Spoon.bat (I'm only using PDI).

So essentially I changed this block of code in the batch file:

:USE32
REM ===========================================
REM Using 32bit Java, so include 32bit SWT Jar
REM ===========================================
set LIBSPATH=libswt\win32
GOTO :CONTINUE
:USE64
REM ===========================================
REM Using 64bit java, so include 64bit SWT Jar
REM ===========================================
set LIBSPATH=libswt\win64
set SWTJAR=..\libswt\win64

to this:

:USE32
REM ===========================================
REM Using 32bit Java, so include 32bit SWT Jar
REM ===========================================
set LIBSPATH=libswt\win32;C:\PROGRA~1\MICROS~1.0FO\sqljdbc_4.0\enu\auth\x86
GOTO :CONTINUE
:USE64
REM ===========================================
REM Using 64bit java, so include 64bit SWT Jar
REM ===========================================
set LIBSPATH=libswt\win64
set SWTJAR=..\libswt\win64

Note the shortened path names. In your case, I would add an entry for the 64 bit lib also since you have a mixed environment.

Essentially whatever program you're starting that uses the sqljdbc driver, you must add the path the sqljdbc_auth.dll to the Java library path (i.e. -Djava.library.path=%LIBSPATH%). After doing that, my instance of PDI can connect to SQL Server via Windows Authentication with no trouble. I don't use the BI_Server or the Admin Console, but I suspect you'll have the same issue.

Note, I have no experience with SQLEXPRESS, or a mixed 32 bit JVM/64 bit OS environment. There could be problems there too, but if you want to use Windows Authentication with SQL Server, you have to at least deal with the library path issue.

Answer 3

If you are using a intance of Sql Express in local machine and connecting Pentaho, you must empty the field Port Number. Try the configuration below:

Host Name: localhost  
Database: database_name  
Instance Name: sqlexpress 
Port Number:  
User Name: user_name  
Password: *****