none
Accessing shared data via LDAP

    Question

  • Hi All...

    We have a very old, legacy web/database application.  It was built on CGI technology and is comprised of many C-compliled executables.  And that C code has loads of embedded SQL that it uses to access a database via ODBC.  Ah, those were the days...

    Today, the database typically lives in SQL Server 2008, but it was originally developed under Access.

    The application has a table, Users, that contains information about users who use the application.  Personal preferences, role information pertaining to the application, etc.  In recent years, some of the data in that table is being duplicated in other applications.  We're exploring moving essentially the Users table to some directory and accessing it via LDAP.

    We don't fancy going through all of the C code looking for SQL code that accesses the Users table.  And there's really no market to justify a rewrite...

    The question is...  So what can be done from SQL Server to minimize the pain/work in the C code?  Is it possible for SQL Server to somehow "map" a table to a directory via ldap, have it live with all the other tables, and not need to change any of the C/SQL?

    Thanks!  -- Curt

    Wednesday, September 11, 2013 1:10 PM

Answers

  • As far as I understand your problem: You have a running system where user information/credentials are already existing in tables of SQL Server. Thus the simple idea is to keep these tables, and find a way of copying the data from the LDAP into them.

    Well, it's a kind of passive or manual replication, thus you need to define a trigger. E.g. a fixed time interval when differences are transfered automatically. Or the people changing values in the LADP need to manually initilize this transfer.

    • Marked as answer by CurtisDeHaven Thursday, September 12, 2013 3:09 PM
    Thursday, September 12, 2013 1:35 PM

All replies

  • You need to replicate the necessary information from the LDAP to the already existing tables. Then you don't need to fiddle with the legacy code. This can be done by SSIS, SQL CLR or even ADSI queries.

    Wednesday, September 11, 2013 5:31 PM
  • Thanks for the reply, Stefan.  When you say "replicate", are you talking about a one-time data transfer or something that contunually happens in real time?  That is, as other applications are writing data to the directory, are those updates being pushed to the SS database automatically?

    We did explore ADSI a little, but that seemed to be a tool used within TSQL.  We'd have to integrate that into the existing code, no?  Or perhaps into an sproc - but then that would need to be integrated...

    Curt

    Thursday, September 12, 2013 12:31 PM
  • As far as I understand your problem: You have a running system where user information/credentials are already existing in tables of SQL Server. Thus the simple idea is to keep these tables, and find a way of copying the data from the LDAP into them.

    Well, it's a kind of passive or manual replication, thus you need to define a trigger. E.g. a fixed time interval when differences are transfered automatically. Or the people changing values in the LADP need to manually initilize this transfer.

    • Marked as answer by CurtisDeHaven Thursday, September 12, 2013 3:09 PM
    Thursday, September 12, 2013 1:35 PM