SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



Running Active Directory Services Queries Using MS SQL Server T-SQL OPENQUERY Command


Using Microsoft SQL Server, you can connect to Microsoft Active Directory Services applications in your network and query users or objects in the Active Directory Services.

Connecting to an Active Directory domain can be useful in many ways and gives environmental informations about users in an intranet or local network for sql server programmers and also .NET developers
For instance, as a database programmer you can get emails of your network users and send them emails when specific events are triggered in your SQL Server database. Or as a .NET developer you can create an application like MS Outlook mail client, where you can find and send email to people by querying them according to their names.


What is LDAP (Lightweight Directory Access Protocol)?


LDAP is a standart internet protocol used to get information from a directory service.
We will use LDAP to connect to an Active Directory Service and query the directory information.


Create a Linked Server to the Active Directory Services


First of all define a Linked Server to the Active Directory Services using the below t-sql command.

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

Enable QPENQUERY in SQL Server 2005 and run QPENQUERY T_SQL Statements


Then try running the below LDAP SELECT queries using t-sql OPENQUERY command.

Be aware that, Microsoft SQL Server 2005 by default prevents the usage of "OpenQuery", in a way OpenQuery is disabled by default.
To enable "OpenQuery" you can refer to article titled "How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure" at Kodyaz.com.
You might have to run the below t-sql script batch in order to enable OpenQuery in a MS SQL Server instance.

sp_configure 'show advanced options', 1
reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=kodyaz,DC=com'' WHERE objectCategory=''user'' ')

SELECT * FROM OpenQuery(ADSI, 'SELECT mail, displayName, userPrincipalName FROM ''LDAP://DC=kodyaz,DC=com'' WHERE objectCategory=''user'' ')

You can see that you should use the attribute names properly in the t-sql OPENQUERY statements.





Common LDAP Attributes for Active Directory Entries


Here is a list of common attributes with names and short descriptions:

sAMAccountNameNT account or logon name
displayName
description
CNCommon Name
DN (distinguishedName)
givenNameFirstname of a user
SNSurname
nameCommon name
objectCategoryActive Directory Schema category
objectClassDefines if is a user, computer, organizational unit or a container
userPrincipalName (UPN)
mail
cCountry/Region
companyCompany/Organization name
departmentDepartment
managerManager name


You can use these common attributes in the LDAP queries or in your vbs scripts/VBScript according to your needs.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems