Post reply

LDAP using SQL 2012

  • June 12, 2013 at 4:02 pm

    #295170

    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?

  • June 13, 2013 at 11:58 am

    #1623844

    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

    Tommy

    Follow @sqlscribe

  • June 13, 2013 at 1:22 pm

    #1623873

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

  • April 30, 2015 at 12:23 pm

    #1794814

    Did you ever get this resolved?

  • April 30, 2015 at 3:07 pm

    #1794857

    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.

  • May 1, 2015 at 6:20 am

    #1794947

    OK If you remember anything else let me know.

    Thanks

  • May 1, 2015 at 6:40 am

    #1794954

    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!

  • May 1, 2015 at 6:43 am

    #1794957

    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.

  • May 1, 2015 at 8:07 am

    #1794992

    "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?

  • May 1, 2015 at 8:18 am

    #1794998

    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

    Tommy

    Follow @sqlscribe

  • May 1, 2015 at 8:39 am

    #1795007

    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