How to work around the paging limitation in SQL Link Server when targeting the Active Directory.

When we create a SQL Linked Server to AD, there are certain limitations. For example, we cannot retrieve multivalued attributes from AD. More information on limitations with SQL Linked Server queries can be found in support.microsoft.com/kb/299410

As mentioned in the KB article, queries that would return a result set larger than the default page are truncated to the default page size. This blog post will focus on a workaround for retrieving more than the default page size of records from the active directory by using a VBS to create a temporary table on the SQL server.

The following steps describe the workaround:

1. Write a VBScript which will fetch "n" numbers of records from AD, based on any Search Query and Search Filter. This VBScript will create a table in SQL by name "AD_Records_Table" to store records from the AD. The script will check for the existence of the table. If the table does not exist, the script will create it. If the table does exist, the script first truncates the table and then rebuilds the table from the data just returned from the AD.

2. From SQL Server, we will invoke this VBScript by using xp_cmdShell. Before calling xp_cmdShell we need to enable "Show Advanced Option" by calling sp_configure.

Now we have the requested data stored in the table "AD_Records_Table", we can run any SQL query required on this table.

The script can be schedule to run once a day to sync up AD & SQL Data.

In this sample script we are these fields from AD sAMAccountName, givenName, sn, distinguishedName.

Here are the detailed steps:

Step 1:

Create the following VBScript. Open a notepad, paste the following script. Save the file by name Search_User.vbs.

Please note the following script will retrieve all the user objects and display it's AdsPath in the output. You can customize the output as per the requirement.

'<C:\Scripts\Search_Users.vbs>

Option Explicit

Dim con, com

Dim ConAD, ComAD, RecAD

Dim strLDAPPath, objRootDSE, strCommand

Dim strSAM, strFirstName, strLastName, strDN

Set con = CreateObject("ADODB.Connection")

Set com = CreateObject("ADODB.Command")

con.Provider = "SQLOLEDB.1"

con.ConnectionString = "Provider='sqloledb'; Data Source='SHALOINT'; Initial Catalog='Master'; User ID= 'sa'; Password = 'Password!'"

con.Open

com.ActiveConnection= con

com.CommandText = "IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='AD_Records_Table') BEGIN " & _

                                                                                                "Create Table AD_Records_Table (" &_

                                                                                                                "sAMAccountName VARCHAR(20)," &_

                                                                                                                "givenName VARCHAR(180)," &_

                                                                                                                "sn VARCHAR(180)," &_

                                                                                                                "distinguishedName VARCHAR(180) )" &_

                                                                                "END ELSE TRUNCATE TABLE AD_Records_Table"

com.Execute

Set ConAD = CreateObject("ADODB.Connection")

Set ComAD = CreateObject("ADODB.Command")

Set RecAD = CreateObject("ADODB.Recordset")

ConAD.Provider = "ADsDSOObject"

ConAD.Open

Set objRootDSE = GetObject("LDAP://RootDSE")

strLDAPPath = "<LDAP://" + objRootDSE.Get("defaultNamingContext") + ">"

strCommand = strLDAPPath & ";(&(sAMAccountName=*)(objectClass=User)(objectCategory=Person));sAMAccountName,givenName,sn,distinguishedName;subtree"

ComAD.CommandText = strCommand

ComAD.ActiveConnection = ConAD

ComAD.Properties("Page Size") = 1000

Set RecAD = ComAD.Execute

Do until RecAD.EOF

                strSAM = TRIM(RecAD("sAMAccountName"))

                strFirstName = TRIM(RecAD("givenName"))

                strLastName = TRIM(RecAD("sn"))

                strDN = TRIM(RecAD("distinguishedName"))

                com.CommandText = "INSERT INTO AD_Records_Table(sAMAccountName,givenName,sn,distinguishedName)" & _

                                                                                                "VALUES('" & strSAM & "','" & strFirstName & "', '" & strLastName & "', '" & strDN & "')"

                com.Execute

                RecAD.MoveNext

Loop

Set ComAD = Nothing

Set RecAD = Nothing

Set con = Nothing

Set con = Nothing

Set com = Nothing

'</C:\Scripts\Search_Users.vbs>

Step 2:

Open SQL Server's Query analyzer. Type the following SQL Query.

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdShell', 1

GO

RECONFIGURE

GO

xp_cmdShell "Cscript C:\Scripts\Search_Users.vbs //nologo"

GO

By executing xp_cmdShell, it will display the returned records as shown in the following image:.