IBM Support

Connect to SQL Server using Windows Authentication

Question & Answer


Question

How do I configure the JDBC Connector in IBM Tivoli Directory Integrator (TDI) to connect to SQL Server using Windows Authentication?

Cause

Best practice indicates you should use Windows authentication instead of SQL authentication whenever possible because you avoid storing credentials in connection strings and you avoid passing passwords over the network to your database server.

In TDI, the following error message will be generated when you are using an incorrect JDBC driver. Please refer to MS SQL Server documentation to obtain the correct driver.

    CTGDIJ109E Unable to connect to the database (This driver is not configured for integrated authentication.). Verify that all the database connection parameters are correctly specified.

Answer

To configure TDI JDBC connection properties to connect to MS SQL Server using Window authentication, perform the following steps:


1. Verify your sqljdbc4.jar supports integrated authentication.
See Microsoft Download center: http://www.microsoft.com/en-us/download/details.aspx?id=21599

2. Verify your SQL Server is configured to use Windows Authentication.

3. Verify sqljdbc4.jar file is placed in a TDI jar files directory.
  • Typically jar files are copied to <TDI-Install-Dir>\jars\3rdparty\others
  • Optionally, un-comment the following line in the solution.properties file and copy the jar file to the specified directory.
        • # com.ibm.di.loader.userjars=c:\myjars
  • Verify the sqljdbc_auth.dll is included in a java.libary.path
    4. If the TDI server and/or Configuration Editor is running, then you must restart.

    5. Create a JDBC connector with the following connection parameters, specifically adding integratedSecurity=true.

    JDBC Connector > Connection Tab
    JDBC URL jdbc:sqlserver://<dbSERVER>;databaseName=<dbName>;integratedSecurity=true
    JDBC Driver com.microsoft.sqlserver.jdbc.SQLServerDriver
    UserName: <domain>\<uid>

    where
        <dbSERVER> is the hostname or ip address of the SQL Server database
        <dbName> is the database name
        <domain> is the Windows domain for the User credentials
        <uid> is the user id

    The JDBC driver does not support the integrated authentication when the driver runs on non-Windows operating systems.

    Reference Material:

    Connection Strings for SQL Server: MSDN - Setting the Connection Properties: How To: Connect to SQL Server Using Windows Authentication: TDI v7.1 Connectors - JDBC Connector
    Verification Methods and Trouble shooting:

    This is a good example to test the driver package and the database configuration.

    [{"Product":{"code":"SSCQGF","label":"Tivoli Directory Integrator"},"Business Unit":{"code":"BU008","label":"Security"},"Component":"General","Platform":[{"code":"PF033","label":"Windows"}],"Version":"7.1;7.1.1;7.2","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]

    Product Synonym

    tdi

    Document Information

    Modified date:
    16 June 2018

    UID

    swg21584489

    Page Feedback