Creating More Effective Queries

The  BasicQuery.ps1 script is a fairly wasteful script in that all it does is produce a list of user names and print them out. Although the script illustrates the basics of making a connection into Active Directory by using ADO, it is not exactly a paradigm of efficiency. ADO, however, is a very powerful technology, and there are many pieces of the puzzle we can use to make the script more efficient and more effective. The first thing we need to do is to understand the objects we have that we can use with ADO. These objects are listed in Table 8-2.

Table 8-2: Objects Used to Search Active Directory
Open table as spreadsheet

Object	Description
Connection	An open connection to an OLE DB data source such as ADSI
Command	Defines a specific command to execute against the data source
Parameter	An optional collection for any parameters to provide to the Command object
RecordSet	A set of records from a table, a Command object, or SQL syntax A RecordSet object can be created without any underlying Connection object
Field	A single column of data in a recordset
Property	A collection of values supplied by the provider for ADO
Error	Contains details about data access errors. Refreshed when an error occurs in a single operation

When we use ADO to talk to Active Directory, we often are working with three different objects: the Connection object, the Command object, and the RecordSet object. The Command object is used to maintain the connection, pass along the query parameters, and perform such tasks as specifying the page size and search scope and executing the query. The Connection object is used to load the provider and to validate the user’s credentials. By default, it utilizes the credentials of the currently logged-on user. If you need to specify alternative credentials, you can use the properties listed in Table 8-3. To do this, we need to use the Properties property of the Connection object. After we have the Connection object, and we use Properties to get to the properties, we then need to use Item to supply value for the specific property item we want to work with.

Table 8-3: Authentication Properties for the Connection Object
Open table as spreadsheet

Property	Description
User ID	A string that identifies the user whose security context is used when performing the search. (For more information about the format of the user name string, see IADsOpenDSObject::OpenDSObject in the Platform SDK.) If the value is not specified, the default is the logged-on user or the user impersonated by the calling process.
Password	A string that specifies the password of the user identified by “User ID”
Encrypt	Password A Boolean value that specifies whether the password is encrypted. The default is False.
ADSI Flag	A set of flags from the ADS_AUTHENTICATION_ENUM enumeration. The flag specifies the binding authentication options. The default is zero.

Using Alternative Credentials

As network administration becomes more granular, with multiple domains, work groups, OUs, and similar grouping techniques, it becomes less common for everyone on the IT team to be a member of the Domain Admins group. If the script does not impersonate a user who is a member of the Domain Admins group, then it is quite likely it will need to derive permissions from some other source. One method to do this is to supply alternative credentials in the script. To do this, we need to specify certain properties of the Connection object.

Just the Steps 

To create a connection in Active Directory using alternative credentials

1. Create the ADODB.Connection object

2. Use the Provider property to specify the ADsDSOObject provider

3. Use Item to supply a value for the properties “User ID” and “Password”

4. Open the connection while supplying a value for the name of the connection

The technique outlined in the using alternative credentials step-by-step exercise is shown here. This code is from the  QueryComputersUseCredentials.ps1 script, which is developed in the querying active directory using alternative credentials procedure.

 $objConnection = New-Object -comObject "ADODB.Connection" $objConnection.provider = "ADsDSOObject" $objConnection.properties.item("user ID") = $strUser $objConnection.properties.item("Password") = $strPwd $objConnection.open("modifiedConnection")

Querying Active Directory using alternative credentials

1. Open the  QueryComputers.ps1 script in Notepad or in your favorite Windows PowerShell script editor and save it as yourname QueryComputersUseCredentials.ps1.

2. On the first noncommented line, define a new variable called $strBase, and use it to assign the LDAP connection string. This variable is used to define the base of the query into Active Directory. For this example, we will connect to the root of the NwTraders.msft domain. To do this, the string is enclosed in angle brackets and begins with the moniker LDAP. The base string is shown here:

    "<LDAP://dc=nwtraders,dc=msft>"

   The new line of code is shown here:

    $strBase = "<LDAP://dc=nwtraders,dc=msft>"

3. Create a new variable called $strFilter. This will be used to hold the filter portion of our LDAP syntax query. Assign a string that specifies the objectCategory attribute when it is equal to the value of computer. This is shown here:

    $strFilter = "(objectCategory=computer)"

4. Create a new variable called $strAttributes and assign the string of name to it. This variable will be used to hold the attributes to search on in Active Directory. This line of code is shown here:

    $strAttributes = "name"

5. Create a variable called $strScope. This variable will be used to hold the search scope parameter of our LDAP syntax query. Assign the value of subtree to it. This line of code is shown here:

    $strScope = "subtree"

6. Modify the $strQuery line of code so that it uses the four variables we created:

    $strQuery = "<LDAP://dc=nwtraders,dc=msft>;;name;subtree"

   The advantage of this is that each of the four parameters that are specified for the LDAP syntax query can easily be modified by simply changing the value of the variable. This preserves the integrity of the worker section of the script. The order of the four parameters is base, filter, attributes, and scope. Thus, the revised value to assign to the $strQuery variable is shown here:

    $strQuery = "$strBase;$strFilter;$strAttributes;$strScope"

7. Create a new variable called $strUser and assign the string “LondonAdmin” to it. This is the name of the useraccount to use to make the connection to Active Directory. This line of code is shown here:

    $strUser = "LondonAdmin"

8. Create a new variable called $strPassword and assign the string Password1 to it. This is the password that will be used when connecting into the NwTraders.msft domain by using the LondonAdmin account. This is shown here:

    $strPwd = "Password1"

9. Between the $objConnection = New-Object -comObject "ADODB.Connection" command and the $objCommand = New-Object -comObject "ADODB.Command" command, insert four blank lines. This space will be used for rearranging the code and for inserting new properties on the Connection object. The revised code is shown here:

    $objConnection = New-Object -comObject "ADODB.Connection"   $objCommand = New-Object -comObject "ADODB.Command"

10. Move the $objConnection.provider = "ADsDSOObject;" line of code from its position below the $objCommand = New-Object –comObject “ADODB.Command" line of code to below the line of code that creates the Connection object. After you have the code moved, remove the trailing semicolon because it is not needed. This revised code is shown here:

     $objConnection = New-Object -comObject "ADODB.Connection" $objConnection.provider = "ADsDSOObject"

11. Use the Item method of the properties collection of the Connection object to assign the value contained in the $strUser variable to the “User ID” property. This line of code is shown here:

     $objConnection.properties.item("user ID") = $strUser

12. Use the Item method of the properties collection of the Connection object to assign the value contained in the $strPassword variable to the “Password” property. This line of code is shown here:

     $objConnection.properties.item("Password") = $strPwd

13. The last line of code we need to modify from the old script is the $objConnection.Open("Provider=ADsDSOObject;") line. Because we needed to move the provider string up earlier in the code to enable us to modify the properties, we have already specified the provider. So, now we only need to open the connection. When we open the connection, we give it a name “modifiedConnection” that we would be able to use later on in the script if we so desired. The revised line of code is shown here:

     $objConnection.open("modifiedConnection")

14. Save and run your script. If it does not perform as expected, compare it with the  QueryComputersUseCredentials.ps1 script.

15. This concludes the querying Active Directory using alternative credentials procedure.

Modifying Search Parameters

A number of search options are available to the network administrator. The use of these search options will have an extremely large impact on the performance of your queries against Active Directory. It is imperative, therefore, that you learn to use the following options. Obviously, not all options need to be specified in each situation. In fact, in many situations, the defaults will perform just fine. However, if a query is taking a long time to complete, or you seem to be flooding the network with unexpected traffic, you might want to take a look at the Search properties in Table 8-4.

Table 8-4: ADO Search Properties for Command Object
Open table as spreadsheet

Property	Description
Asynchronous	A Boolean value that specifies whether the search is synchronous or asynchronous. The default is False (synchronous). A synchronous search blocks until the server returns the entire result (or for a paged search, the entire page). An asynchronous search blocks until one row of the search results is available, or until the time specified by the Timeout property elapses.
Cache Results	A Boolean value that specifies whether the result should be cached on the client side. The default is True; ADSI caches the resultset. Turning off this option might be desirable for large resultsets.
Chase Referrals	A value from ADS CHASE_REFERRALS_ENUM that specifies how the search chases referrals. The default is ADS_CHASE_REFERRALS EXTERNAL = 0x40. To set ADS_CHASE_REFERRALS_NEVER, set to 0.
Column Names Only	A Boolean value that indicates that the search should retrieve only the name of attributes to which values have been assigned. The default is False.
Deref (dereference) Aliases	A Boolean value that specifies whether aliases of found objects are resolved. The default is False.
PageSize	An integer value that turns on paging and specifies the maximum number of objects to return in a resultset. The default is no page size, which means that after 1000 items have been delivered from Active Directory, that is it. To turn on paging, you must supply a value for page size, and it must be less than the SizeLimit property. (For more information, see PageSize in the Platform SDK, which is available online from http://msdn2.microsoft.com/.)
SearchScope	A value from the ADS_SCOPEENUM enumeration that specifies the search scope. The default is ADS_SCOPE_SUBTREE.
SizeLimit	An integer value that specifies the size limit for the search. For Active Directory, the size limit specifies the maximum number of returned objects. The server stops searching once the size limit is reached and returns the results accumulated up to that point. The default is No Limit.
Sort on	A string that specifies a comma-separated list of attributes to use as sort keys. This property works only for directory servers that support the LDAP control for server-side sorting. Active Directory supports the sort control, but this control can affect server performance, particularly when the resultset is large. Be aware that Active Directory supports only a single sort key. The default is No Sorting.
TimeLimit	An integer value that specifies the time limit, in seconds, for the search. When the time limit is reached, the server stops searching and returns the results accumulated to that point. The default is No Time Limit.
Timeout	An integer value that specifies the client-side timeout value, in seconds. This value indicates the time the client waits for results from the server before quitting the search. The default is No Timeout.

In the previous section, when we used alternative credentials in the script, we specified various properties on the Connection object. We will use the same type of procedure to modify search parameters, but this time we will specify values for various properties on the Command object. As an example, suppose we wanted to perform an asychronous query of Active Directory. We would need to supply a value of true for the asynchronous property. The technique is exactly the same as supplying alternative credentials: create the object, and use the Item method to specify a value for the appropriate property. This piece of code, taken from the  AsynchronousQueryComputers.ps1 script, is shown here:

 $objCommand = New-Object -comObject "ADODB.Command" $objCommand.ActiveConnection = $objConnection $objCommand.Properties.item("Asynchronous") = $blnTrue

Important 

When specifying properties for the Command object, ensure you have the activeConnection associated with a valid Connection object before making the assignment. Otherwise, you will get an error stating the property is not valid-which can be very misleading.

Note that you should specify a page size. In Windows Server 2003, Active Directory is limited to returning 1000 objects from the results of a query when no page size is specified. The PageSize property tells Active Directory how many objects to return at a time. When this property is specified, there is no limit on the number of returned objects Active Directory can provide. If you specify a size limit, the page size must be smaller. The exception would be if you want an unlimited size limit of 0, then obviously the PageSize property would be larger than the value of 0. In the  SizeLimitQueryUsers.ps1 script, after creating a Command object, associating the connection with the activeConnection property, we use the Item method of the properties collection to specify a size limit of 4. When the script is run, it only returns four users. The applicable portion of the code is shown here:

 $objCommand = New-Object -comObject "ADODB.Command" $objCommand.ActiveConnection = $objConnection $objCommand.Properties.item("Size Limit") = 4

Controlling script execution

1. Open the  QueryComputersUseCredentials.ps1 script, and save it as yourname QueryTimeOut.ps1.

2. Edit the query filter contained in the string that is assigned to the variable $strFilter so that the filter will return items when the ObjectCategory is equal to User. The revised line of code is shown here:

    $strFilter = "(objectCategory=User)"

3. Delete the line that creates the $strUser variable and assigns the LondonAdmin User to it.

4. Delete the line that creates the $strPwd variable and assigns the string Password1 to it.

5. Delete the two lines of code that assign the value contained in the $strUser variable to the User ID property, and the one that assigns the value contained in the $strPwd variable to the Password property of the Connection object. These two lines of code are shown commented-out here:

    #$objConnection.properties.item("user ID") = $strUser #$objConnection.properties.item("Password") = $strPwd

6. Inside the parentheses of the Open command that opens the Connection object to Active Directory, delete the Reference string that is contained inside it. We are able to delete this string because we did not use it to refer to the connection later in the script. The modified line of code is shown here:

    $objConnection.open()

7. Under the line of code that assigns the Connection object that is contained in the $objConnection variable to the ActiveConnection property of the Command object, we want to add the value of 1 to the TimeLimit property of the Command object. To do this, use the property name TimeLimit, and use the Item method to assign it to the properties collection of the Command object. The line of code that does this is shown here:

    $objCommand.properties.item("Time Limit")=1

8. Save and run your script. If it does not produce the desired results, compare it with  QueryTimeOut.ps1.

9. This concludes the controlling script execution procedure.