Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

LDAP using SQL 2012 Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 4:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 3:40 PM
Points: 15, Visits: 336
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?
Post #1462851
Posted Thursday, June 13, 2013 11:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 22, 2015 8:13 AM
Points: 1,158, Visits: 3,290
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

Post #1463239
Posted Thursday, June 13, 2013 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 3:40 PM
Points: 15, Visits: 336
I don't have a problem setting up the linked server but can not query the LDAP which uses the Active Directory Helper Service
Post #1463277
Posted Thursday, April 30, 2015 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 7:43 AM
Points: 5, Visits: 5
Did you ever get this resolved?
Post #1681780
Posted Thursday, April 30, 2015 3:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 3:40 PM
Points: 15, Visits: 336
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.
Post #1681831
Posted Friday, May 1, 2015 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 7:43 AM
Points: 5, Visits: 5
OK If you remember anything else let me know.

Thanks
Post #1681942
Posted Friday, May 1, 2015 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 13,617, Visits: 34,768
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1681949
Posted Friday, May 1, 2015 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 7:43 AM
Points: 5, Visits: 5
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.
Post #1681952
Posted Friday, May 1, 2015 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2015 7:43 AM
Points: 5, Visits: 5
"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?
Post #1681992
Posted Friday, May 1, 2015 8:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 22, 2015 8:13 AM
Points: 1,158, Visits: 3,290
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

Post #1681998
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse