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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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

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
CNCommon Name
DN (distinguishedName)
givenNameFirstname of a user
nameCommon name
objectCategoryActive Directory Schema category
objectClassDefines if is a user, computer, organizational unit or a container
userPrincipalName (UPN)
companyCompany/Organization name
managerManager name

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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.