Querying Active Directory from SQL Server 2005

by

Pretty general question but here are some pointers.

You need a linked server creating on the SQL Server that points to ADSI (Active Directory Service Interface) something like this will do it.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

Then you can use the following sort of query.


SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass=""user''')

You’ll need to set the LDAP:// line appropriately (ask your AD admin for the details) and be aware that distributed adhoc queries using OpenQuery are disabled by default in SQL Server. Once you have the above though it should be pretty easy to google for any particular variations.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. SQL update query using joins
  3. How to update two tables in one statement in SQL Server 2005?
  4. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  5. SQL query to select dates between two dates
  6. Create a date from day month and year with T-SQL
  7. Querying data by joining two tables in two database on different servers
  8. Are there any disadvantages to always using nvarchar(MAX)?
  9. SQL – Subtracting a depleting value from rows
  10. Turn off constraints temporarily (MS SQL)
  11. Get the week start date and week end date from week number
  12. Pass table as parameter into sql server UDF
  13. conditional unique constraint
  14. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  15. Is the LIKE operator case-sensitive with SQL Server?
  16. SELECT FOR UPDATE with SQL Server
  17. How to connect to SQL Server from another computer?
  18. Easiest way to populate a temp table with dates between and including 2 date parameters
  19. Performance of SQL Server 2005 Query
  20. OPENXML with xmlns:dt
  21. Cross Join without duplicate combinations
  22. How to select date without time in SQL
  23. Query times out from web app but runs fine from management studio
  24. SQL . The SP or function should calculate the next date for friday
  25. What is the difference between a stored procedure and a view?
  26. How to change column order in a table using sql query in sql server 2005?
  27. Add a row number to result set of a SQL query
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Is there a quick way to check if ANY column is NULL?
  30. SQL query for parent-child chain

Leave a Comment