locked
Use Data Accorss Databases RRS feed

  • Question

  • Hello,

     

    We have multiple applications which have a table called users. Our organization has 40K users and each application is querying active directory and keeping a list of users.

     

    Is there any way we can centralize is this information and use it without breaking referential integrity.

     

    Some applications are deployed on their own dedicated servers and farms.

    Thursday, April 22, 2010 2:26 PM

Answers

  • If you only want *ONE* copy of the data, that would be in AD, correct? Then you need to finds a way so your applications can get to it when they need it. That would be a LINKED Server into AD to query the appropriate table. ( used to have a query that did this but at my last job).

    But querying the Active Directory catalog may require higher privaledges than you are willing to expose. In that case, and this is my recommended suggestion, I suggest you have an SSIS package that runs every night that gets data from AD, loads it into a staging table, then updates, adds, and marks as deleted any rows that have changed.

    Are you looking for specifics, like how to set up a linked server to AD?

                 Please mark helpful replies as 'Answered'


    Todd C MSCTS SQL Server 2005
    Friday, April 23, 2010 11:57 AM
  • Hi,

    The best way for you to query Active directory would be LDAP. Pls go through the following link for LDAP details:

    http://technet.microsoft.com/en-us/library/aa996205(EXCHG.65).aspx


    Rojit
    Friday, April 23, 2010 3:06 PM
  • Can you set up replication? Have each application query a replicated table?

    Or have one "Book of record" for the user data (AD) and have a nightly process that queries it and pushed updats to all other locations.

     


    Todd C MSCTS SQL Server 2005
    Thursday, April 22, 2010 5:07 PM

All replies

  • Can you set up replication? Have each application query a replicated table?

    Or have one "Book of record" for the user data (AD) and have a nightly process that queries it and pushed updats to all other locations.

     


    Todd C MSCTS SQL Server 2005
    Thursday, April 22, 2010 5:07 PM
  • Can you recommend an approach which avoids multiple copies of data.
    Friday, April 23, 2010 11:10 AM
  • If you only want *ONE* copy of the data, that would be in AD, correct? Then you need to finds a way so your applications can get to it when they need it. That would be a LINKED Server into AD to query the appropriate table. ( used to have a query that did this but at my last job).

    But querying the Active Directory catalog may require higher privaledges than you are willing to expose. In that case, and this is my recommended suggestion, I suggest you have an SSIS package that runs every night that gets data from AD, loads it into a staging table, then updates, adds, and marks as deleted any rows that have changed.

    Are you looking for specifics, like how to set up a linked server to AD?

                 Please mark helpful replies as 'Answered'


    Todd C MSCTS SQL Server 2005
    Friday, April 23, 2010 11:57 AM
  • Hi,

    The best way for you to query Active directory would be LDAP. Pls go through the following link for LDAP details:

    http://technet.microsoft.com/en-us/library/aa996205(EXCHG.65).aspx


    Rojit
    Friday, April 23, 2010 3:06 PM