This articles looks at configuring the following components,
- SAP Business Objects Data Services XI3.2
- SQL Server Express 2005
- Tomcat 5.5
- JDK 1.6
The JDK 1.6 is not supported and indeed when trying to configure the Management Console to connect to a repository created in SQL Server Express 2005 we get the following error,
Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Please use the JDBC 4 driver (sqljdbc4.jar) instead.
As the error suggests we need to upgrade the SQL Server JDBC driver to use a Type 4 JDBC driver. The instructions below assume you have already setup a Data Integrator repository in SQL Server and also you are not using windows authentication to connect to this repository.
To install and use a Type 4 JDBC driver,
- Download Microsoft SQL Server JDBC Driver 2.0 from Microsoft. Note the previous versions of this driver are also Type 4 JDBC drivers but we might as well use the latest version.
- Save the downloaded file sqljdbc_<version>_enu.exe to a temporary directory and run the executable.
- Enter an install directory when prompted; it is recommended that you unpack this zip file in %ProgramFiles% with the default directory “Microsoft SQL Server JDBC Driver”.
- Copy the sqljdbc4.jar file from
%Program Files%\Microsoft SQL Server JDBC Driver\sqljdbc_2.0\enu
where %Tomcat% is your tomcat installation directory.
- Rename the existing sqljdbc.jar (without a “4” in the name) to sqljdbc.jar.old or move it to a different folder.
Before restarting Tomcat to load the new driver we need to check our SQL Server configuration.
- Launch SQL Server Configuration Manager.
- Expand SQL Server 2005 Network Configuration.
- Double click TCP/IP protocol in right hand panel
- Enable the protocol if it is not enabled
- Select the IP Addresses tab and under IPALL make a note of the port number next to TCP Dynamic Ports. In the screenshot above the port number is 1028.
- Save and close the TCP/IP properties window
If you had to enable the TCP/IP protocol you’ll need to restart SQL Server,
- Select SQL Server 2005 Services in left hand panel
- In turn, select both entries on the right and click the restart icon
Restart Tomcat and when started launch the Management Console. Now we can add the Data Integrator repository to the console,
- After you have logged in select Administration from the main screen
- From the left hand menu expand Management and select Repositories. Cick Add
- Enter a repository name. This can be anything you like and it is used to identify the repository in the management console.
- Under Database Type select Microsoft_SQL_Server
- Leave windows authentication unchecked
- Enter a machine name: <servername>\SQLEXPRESS where <servername> is the name of the server where SQL Server Express has been installed.
- Enter a database port: 1028 Note, this isn’t the usual port used by SQL Server which is 1433 but is the port that we noted above when configuring TCP/IP for SQL Server.
- For database name enter the name of your Data Integrator repository database
- and finally enter the required username and password.
- Click Test and if it is working OK click apply.