Extracting Computer members from AD Security Groups

Products

• IT Management Suite

Issue/Introduction

Is it possible to allow Helpdesk users to create Security Groups that reference computers requiring a certain Software Application or Program, and not allow them access to the Notification Server?

This information is sent to the Notification Server, and the Notification Server Administrator creates a Collection from this data and assigns the relevant Software Delivery Policy to this Collection.

Resolution

AD Sync

SQL can query the Active Directory using the LDAP protocol. Altiris Collections can then be built using such a query.

Prerequisite

The Active Directory Service Interface has to be added to the SQL Server as a Linked Server. This only needs to be done once and is performed by executing the following Stored Procedure (e.g. from SQL Query Analyzer) exactly as shown below:

Sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’

"ADSDSOObject" is the provider_name, "adsdatasource" is the data_source and should not be changed. For more information, search for "ADSI" in SQL Server Books Online (Updated SP3).

An entry is put into the sysservers table in the master database. Open SQL Enterprise Manager to check that ADSI is added as a linked server in <servername>, Security, Linked Servers. Right-click on ADSI and select Properties. On the Security tab, select Be made using this security context and enter “domain\user” and password.

Query

To query the Directory Service, the format is used similar to that given in SQL Server Books Online (Updated SP3), Accessing and Changing Relational Data, Distributed Queries, OLE DB providers tested with SQL, OLE DB provider for Microsoft Directory Services. This uses the OPENQUERY function to send a command to the directory service and return the data to the Select command:

SELECT [Name]
FROM OPENQUERY (ADSI,
‘SELECT Name
FROM ‘’LDAP://DCname/ DC=uk,dc=company,dc=com’’
WHERE objectCategory=”Computer” AND memberof ‘’cn=Apps,ou=Local Apps,ou=Application Groups, DC=uk,dc=company,dc=com’’’)

Because of an error in the Notification Server code, an extra closing parenthesis, ’)’, must be added at the end of the Value SELECT command.

Note: LDIFDE.exe, a utility found on Windows 2000 Servers, can be run to export the active directory to a text file, which will assist with deriving the LDAP URL from the LDAP Distinguished Name:

Ldifde –f c:\temp\dc.txt –s DCname.uk.microsoft.com

Collection

Create a new (dynamic) collection in Software Delivery Solution. Add a filter, Type “Machine”, Table “Wrksta”, Field “name”, Operator “IN”. Value is the select command above, no carriage returns; with the appropriate computer group Distinguished Name.

 

Please note that creating LDAP based queries with an OLE DB provider causes the SQL transaction to become a unserializable transaction. Therefore, the Delta Collection Refresh scheduled task will fail on this collection. All other collection refresh tasks will work correctly. See article 20465 for further details.