This driver is based on the example coming with JDBC 1.6.2 driver for MSSQL, which uses some MSSQL side tables(EMP, EVENTLOG, etc). It takes care of users and roles, and memberships, and syncs the password on the Sub channel (eDir to MSSQL). It works with Nsure Identity Manager 2.0 aka DirXML 2.0. I did not test it yet against MSSQL 7 but it should work. I have tested it with the Microsoft third party JDBC driver.

It is required to obtain msbase.jar, mssqlserver.jar and msutil.jar from SQL Server 2000 Driver for JDBC Service Pack 2 http://www.microsoft.com/downloads/details.aspx?FamilyID=9f1874b6-f8e1-4bd6-947c-0fc5bf05bf71&DisplayLang=en. The files are extracted on the computer where Setup.exe is executed.

The example included with the driver comes with some limited capabilities regarding the integration of MSSQL security: The Subscriber Channel encapsulates JDBC statements in XSL in order to CREATE/DROP logins and GRANT privileges. However, the example does not integrates ROLES and ROLES memberships. This advanced connector has been designed in order to handle USERS, ROLES, and memberships (USERS to ROLES, ROLES to ROLES). It also synchronizes the password for the Subscriber(eDir to MSSQL) channel.

The use of Active Directory security would very likely render this driver not necessary, but since some MSSQL customers have dozens of applications and many more instances, AD enablement of all their MSSQL applications instances/applications is still something planned for the future. Meanwhile, this driver would allow a more comprehensive integration of MSSQL and eDirectory security, especially interesting for customers with many MSSQL developers needing privileges and access on many MSSQL instances.

Schema extensions (CASE IGNORE STRING, SINGLE VALUE, SYNC IMMEDIATE):
User: mssqlUser, mssqlPubRole, mssqlPubRoleOp
Dynamic Group: mssqlRole, mssqlPubRole, mssqlPubRoleOp

N.B. Dynamic Group has been selected since it can be nested.

N.B. While Users with a MSSQL account(mssqlUser) can be anywhere in the tree, MSSQL roles(dynamicGroup) must be located in one context per MSSQL driver(flat).

What the connector does.

Subscriber:

• USER CREATE EVENT: Updates dirxml.emp and sp_addlogin, sp_grantdbaccess(JDBC SQL in XSL) Required attribute = mssqlUser
• USER DELETE EVENT: Updates dirxml.emp and sp_dropuser, sp_droplogin(JDBC SQL in XSL)
• USER Modify EVENT: Updates dirxml.emp
• ROLE CREATE EVENT: Updates dirxml.role and sp_addrole(JDBC SQL in XSL) Required attribute = mssqlRole
• ROLE DELETE EVENT: Updates dirxml.role and sp_droprole(JDBC SQL in XSL)
• ADD ROLE TO USER EVENT: sp_addrolemember(JDBC SQL in XSL).
• DELETE ROLE FROM USER EVENT: sp_droprolemember(JDBC SQL in XSL).
• ADD ROLE TO ROLE EVENT: sp_addrolemember(JDBC SQL in XSL).
• DELETE ROLE FROM ROLE EVENT: sp_droprolemember(JDBC SQL in XSL).

N.B. The DirXML rules for role memberships kick-in only if the added dynamicGroup has a value for the mssqlRole attribute.

Publisher:

• CREATE/DROP USER EVENT: A MSSQL Job Polls dirxml.dbo.sysusers with Stored Procedure sp_dirxml_pub_ae/sp_dirxml_pub_der which updates dirxml.emp(which updates dirxml.eventlog)
• CREATE/DROP ROLE EVENT: A MSSQL Job Polls dirxml.dbo.sysusers with Stored Procedure sp_dirxml_pub_ar/sp_dirxml_pub_der which updates dirxml.role(which updates dirxml.eventlog)
• GRANT/REVOKE ROLE EVENT: A MSSQL Job Polls dirxml.dbo.sysmembers with Stored Procedure sp_dirxml_pub_add_member/sp_dirxml_pub_del_member which updates dirxml.member, dirxml.emp and dirxml.role (which update dirxml.eventlog)

N.B. A delay has been implemented between consecutive add/drop membership events with WAIT FOR DELAY '00:00:11'. The delay is 11-seconds, which is more than the polling interval for Publisher(10-seconds). If multiple add/drop membership records are present in dirxml.eventlog, the driver will treat only the last one for a given user/role. This seems to be caused by the Update Optimizer or a similar feature for the driver, that retains only the last value for an attribute when multiple changes occur within the same polling event. This could be a potential issue if a huge number of memberships needs to be imported into eDir. 11-seconds per membership would be required. One could lower the Pub polling interval to 1-second, and lower the delay to 3-seconds in order to reduce the time required for the massive memberships import.

Installation:

• Extend the schema for User and Dynamic Group
• Import the driver into eDirectory, SQL2000-adv-JDBC-Driver-NIM2.xml for DirXML 2.0/Apollo/Nsure Identity Management 2.0. Enter the selected password for dirxml account(dirxml), IP address for MSSQL server, and database name (e.g. dirxml).
• Login as sa against MSSQL with Query Analyzer. Run SQL script INSTALL_INDIRECT_1.sql.
• Logout from Query Analyzer and login as dirxml. Run SQL scripts EMP.sql, ROLE.sql, PUB_Poll_for_EMP_ROLE.sql and PUB_Poll_membership.sql.
• Create MSSQL jobs for sp_dirxml_pub_ae, sp_dirxml_pub_der ,sp_dirxml_pub_ae, sp_dirxml_pub_ar, sp_dirxml_pub_der. You can schedule them to run every five minutes. You can run them manually for testing purposes. Make sure the jobs are run as a user other than dirxml, for example sa. Otherwise the loopback prevention feature for the driver would discard the Publisher events(perpetrator = dirxml).