title: Mocking an Active Directory for off site development
body: <p>I received a client application, using SQL Server 2005, which contains many views with joins to their active directory.  They do this to secure what people can see in the database.  I need to be able to run these view from my remote development environment and I am looking for suggestion on how to do this.  </p>
  
  <p>I cannot duplicate their AD server, i.e. run it in a VM.<br>
  I guess I could re-write the views removing the joins to AD, but that seems to be a waist of time.   </p>
  
  <p>I read about <a href="http://msdn.microsoft.com/en-us/library/aa705886%28VS.85%29.aspx" rel="nofollow noreferrer">Active Directory Lightweight Directory Services</a> in <a href="https://stackoverflow.com/questions/1300695/how-to-replicate-and-set-up-a-test-active-directory-dc/1300882#1300882">this SO question</a>, could that achieve an environment for me to query against?   </p>
  
  <p>I am not apposed to setting up a VM with Active Directory services, but it could lead to a lot of extra configuration time, where a simpler LDAP server may work just as well (shrug).</p>
  
  <p>Here is an example of a query used for accessing the AD (Most of the fields aren't used but that's a job for another day):</p>
  
  <pre><code>...   
  FROM    OPENQUERY(ADSI, 
     'SELECT  Sn, givenName, userPrincipalName, Name, company, physicalDeliveryOfficeName,
      department, streetAddress, L, St, postalCode, Co, Mail, telephoneNumber,
      facsimileTelephoneNumber, manager, samaccountname, extensionattribute1    
      FROM ''LDAP://OU=EPPPPUsers, DC=EPPPP, DC=COM''
      WHERE objectClass=''user'' OR objectClass=''contact''') AS rs1    
  LEFT OUTER JOIN     
    (SELECT  'EPPPP\' + SUBSTRING(userPrincipalName, 0, CHARINDEX('@', serPrincipalName)) AS UserName, CN    
  FROM     
    OPENQUERY(ADSI, 'SELECT userPrincipalName,    CN    
    FROM ''LDAP://OU=EPPPPUsers, DC=EPPPP, DC=COM''') AS Rowset_1) AS mUsr    
      ON mUsr.CN = LEFT(SUBSTRING(manager, CHARINDEX('=', manager) + 1, LEN(manager)), CHARINDEX(',', SUBSTRING(manager, CHARINDEX('=', manager) + 1, LEN(manager))) - 1)
  </code></pre>