LDAP using SQL 2012

  • In our current system, we query the LDAP using SQL 2008R2 but are moving to SQL 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of the discontinued features of SQL 2012 is the AD helper service.

    Does anyone know of a way to query the AD from SQL 2012?

  • Have you tried querying AD using a linked server via the OLE DB Provider for Microsoft Directory Services?

    Check out -

    http://msdn.microsoft.com/en-us/library/ms190803(v=sql.105).aspx

  • I don't have a problem setting up the linked server but can not query the LDAP which uses the Active Directory Helper Service

  • Did you ever get this resolved?

  • I did finally get it resolved but to be honest, I both can't remember exactly what I did a couple of years ago [think it had to do Enable Promotion of Distributed Transaction for RPC] and it's one of those things that I wish I had left broken. One of our developers ran wild with it and put it in pretty much everything he did. Querying the AD for us is about 2000 times slower than querying the SQL server, doing it thousands of times a day makes for some long days.

  • OK If you remember anything else let me know.

    Thanks

  • not sure what you need from Active directory, but something i do once a week via PowerSell is to query AD, and put the result sin a SQL table in my DBA_Utilities database, and also query for members of a handful of specific groups.

    that list lets me check things like who is disabled, who is new compared to last time i scanned, etc.

    what, specifically, are you trying to get form AD?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • mostly user and group info. We are already doing in on server 2008 i'd like to keep it the same so that we don't have to recode anything.

  • "Enable Promotion of Distributed Transaction for RPC"

    Looks like i already have this enabled.

    If you have a min would you mind Screenshoting you setting?

  • Referenced an example below

    USE [master]

    GO

    EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'SOMEDOMAIN\UserName',@rmtpassword='*******'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'

    GO

    select title, displayName as DisplayName,sAMAccountName as ADLogin, telephoneNumber, department, manager as Manager

    from openquery(ADSI,

    'SELECT title,displayName,sn,sAMAccountName,telephoneNumber,department,manager

    FROM ''LDAP://DC=somedomain,DC=local''

    WHERE objectClass = ''User''')

    where sn = 'Parker'

    select title as Title

    ,displayName as EmployeeName

    ,sAMAccountName as ActiveDirectoryLogin

    ,telephoneNumber as PrimaryTelephoneNumber

    ,mobile as PrimaryMobileNumber

    ,department as Department

    ,replace(replace(substring(manager,1,charindex(',',manager,0)-1),'CN=',''),' ',' ') as Manager

    ,'IsLocked' =

    case

    when lockoutTime is not null then 1

    else 0

    end

    from openquery(ADSI,

    'SELECT title,displayName,sn,sAMAccountName,telephoneNumber,department,manager,lockoutTime,mobile

    FROM ''LDAP://DC=somedomain,DC=local''

    WHERE objectClass = ''User''')

    where department in ('IT','Operations')

    order by department,DisplayName

    go

  • It all looks the same except I am using "Bee made using the login's current Security context" Which worked in 2008 R2

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply