Connecting to Microsoft SQL Server using IBM Tivoli Directory Integrator

How-to Guide

IBM® Tivoli® Directory Integrator (ITDI) is a data manipulation tool ideal for synchronizing and transforming data. Despite its name, ITDI has a flexible connector architecture allowing for integration of diverse data repositories. This article describes how to connect to Microsoft® SQL Server from ITDI via Java™ Database Connectivity (JDBC).

Share:

Neil Readshaw (readshaw@au1.ibm.com), Senior Security Architect, IBM

Readshaw, NeilNeil Readshaw is a Senior Security Architect in the Tivoli Security Team based on the Gold Coast, Australia. In this role, Neil works with customers to define solutions using the Tivoli Security software suite, and works in an enablement role with IBM Business Partners and the IBM technical sales team in the Asia Pacific region.


developerWorks Contributing author
        level

23 May 2006

Introduction

IBM Tivoli Directory Integrator (ITDI) provides for ready connectivity via Java Database Connectivity (JDBC). However, the JDBC provider architecture is a pluggable model, and so additional configuration is required before connection to a relational database is possible. Most database products provide JDBC connectivity in the standard product installation. Microsoft SQL Server is an exception to this, and so enabling JDBC connectivity from ITDI to Microsoft SQL Server is one of the more interesting possibilities.

The remainder of this article provides a procedure for enabling this connectivity.

References


About IBM Tivoli Directory Integrator

Data transformation and synchronization projects are often where an organization begins its endeavours in identity management. Data rationalization initiatives are often a pre-cursor to broader identity management projects in the areas of provisioning, authentication and access control. IBM Tivoli Directory Integrator (ITDI), part of the Tivoli Security suite, is IBM's strategic solution for synchronization of identity data.

IBM Tivoli Directory Integrator provides an alternative to building a single, centralized directory, which is rarely feasible in organizations today. It provides a rich set of connectors, event handlers and parsers to provide rapid integration with existing stores of identity data in the organization. Its flexible scripting mechanism allows you to customize how identity data is transformed between systems, using JavaScript or other popular scripting languages.

When required, customized connectors, parsers and event handlers can be implemented using the Java programming language. These custom components can extend the reach of ITDI beyond the already rich set of components already provided with the product.


Obtaining the Microsoft JDBC Driver for SQL Server

The JDBC provider for Microsoft SQL Server is not provided in the standard product installation. Third-party vendors provide JDBC drivers for Microsoft SQL Server, as well as Microsoft themselves. In this article, we use the JDBC driver provided by Microsoft. Download the Microsoft JDBC Driver from Microsoft at:

http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B138-2AA54BFDC03A

Note that there does not appear to be a specific JDBC driver for Microsoft SQL Server 2003, but the driver for Microsoft SQL Server 2000 works adequately.

Follow the instructions accompanying the JDBC driver for Microsoft SQL Server to install. Installation instructions for Windows and UNIX® platforms are provided.


Configuring ITDI for use with the Microsoft JDBC Driver

  1. Stop ITDI if it is running.
  2. Copy the following JAR files from the JDBC driver installation into the <ITDI>\jars directory:
    • msbase.jar
    • mssqlserver.jar
    • msutil.jar

Connecting to Microsoft SQL Server from ITDI

  1. Start ITDI.
  2. Create a new ITDI configuration.

mssql1

  1. Create a new assembly line.

mssql2

  1. Select the ibmdi.JDBC type, name the new connector and choose Iterator mode.

mssql3

  1. Create the connector details in the following way:
ParameterFormat/Usage
JDBC URLURL of the form jdbc:microsoft:sqlserver://<db-host>:1433;SelectMethod=Cursor;DatabaseName=<db-name>
UsernameWindows or SQL Server username with access to the database
PasswordCorresponding password
SchemaOwner of database schema for the table you intend to use
JDBC Drivercom.microsoft.jdbc.sqlserver.SQLServerDriver
  1. After completing these parameters, click the Select button next to the "Table Name" parameter to display available table names. This is also the first verification of connectivity to the database.

mssql4

  1. Go to the Input Map tab and verify that ITDI can connect to the database and discover the table schema.

mssql5

  1. Verify that ITDI can retrieve a sample row using the newly configured connector.

mssql6


Conclusion

The IBM Tivoli Directory Integrator rich set of standard connectors, combined with the flexible provider architecture in JDBC, enables rapid integration of ITDI with relational databases. The Microsoft SQL Server is no exception.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Tivoli (service management) on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Tivoli
ArticleID=110837
ArticleTitle=Connecting to Microsoft SQL Server using IBM Tivoli Directory Integrator
publish-date=05232006