Category: VBScript

Active Directory and SSIS

I was looking into ways to get the owner of an Active Directory user. I looked at VBScript and C#.NET. While searching for code and while looking at the objects available in .NET using the object browser I found this MSDN blog entry by Alex Tcherniakhovski.

Extracting object ownership information from Active Directory into SQL

One fact mentioned near the bottom of the blog entry is yet another testimony to keep the Domain Admins group small.

Yet another reason to keep Domain Admins group small.

VBScript

Option Explicit

Dim sADDN,objUser,objNtSecurityDescriptor

sADDN = "LDAP://YourContextHere"

Set objUser = GetObject (sADDN)
 
Set objNtSecurityDescriptor = objUser.Get("ntSecurityDescriptor")
WScript.Echo "Current owner of this item: " & objNtSecurityDescriptor.Owner

C#.NET

References Used
Name: System.DirectoryServices
Path: C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.1\System.DirectoryServices.dll
Version: 4.0.0.0
Name: System.DirectoryServices.AccountManagement
Path: C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.1\System.DirectoryServices.AccountManagement.dll
Version: 4.0.0.0
Name: Active DS Type Library
Path: C:\Windows\System32\activeds.tlb
Version: 1.0
File Version: 6.1.7600.16385(win7_rtm.090713-1255)

using System;
using System.Security.Principal;
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;
using System.DirectoryServices.AccountManagement;
using ActiveDs;

namespace ADOwner
{
    class Program
    {
        static void Main(string[] args)
        {

            string ADDomain = "yourdomain.com";
            string ADUser = "youraduser";
            string ADPass = "youradpass";
            string ADsAMAccountName = "YOURDOMAIN\\USERNAMEHERE";

            using (var pc = new PrincipalContext(ContextType.Domain, ADDomain, ADUser, ADPass))
            {

                // get UserPrincipal Object
                UserPrincipal inetPerson = UserPrincipal.FindByIdentity(pc, IdentityType.SamAccountName, ADsAMAccountName);

                // Get Directory Entry Object
                DirectoryEntry de = inetPerson.GetUnderlyingObject() as DirectoryEntry;

                // Get Active Directory Security Object
                ActiveDirectorySecurity ads = de.ObjectSecurity;

                // Get sid Object                                                 
                SecurityIdentifier sid = new SecurityIdentifier(ads.GetOwner((typeof(SecurityIdentifier))).Value);

                // Translate sid to account
                NTAccount account = (NTAccount)sid.Translate(typeof(NTAccount));

                // Get owner string
                Console.WriteLine("Owner: {0}",account.ToString());
                Console.ReadKey();

            }


        }
    }
}

Resources to investigate using Perl

VBScript, Javascript for JSON from SQL Server

<meta http-equiv="x-ua-compatible" content="ie=9" /> 
<html>
      <head>
            <title>JSON from SQL Server</title>
            <hta:application
                  SINGLEINSTANCE="yes"
                  MAXIMIZEBUTTON="yes"
            >

            <script language="javascript">
                  try {
                    self.resizeTo(800, 400);
                   } catch(e) {
                   }
            </script>
            
      
<script language="VBscript">
	
Dim strSQL
    
Sub btnShowJSON()
  ProcessJSON(fnQueryData)
End Sub

function RStoJSON(rs)
    dim sFld,fld,sFlds
    dim sRec,sRecs
    dim sRecordSet
    dim lRecCnt

    sRecordSet = ""
    sRecs = ""
    lRecCnt = 0
    if rs.EOF or rs.BOF then
        RStoJSON = "null"
    else
        do while not rs.EOF and not rs.BOF
            lRecCnt = lRecCnt + 1
            sFlds = ""
            for each fld in rs.Fields
                sFld = """" & fld.Name & """:""" & fld.Value&"" & """"
                sFlds = sFlds & GetDelim(sFlds <> "","fld") & sFld
            next 
            sRec = "{" & sFlds & "}"
            sRecs = sRecs & GetDelim(sRecs <> "", "rec") & sRec
            rs.MoveNext
        loop
        sRecordSet = "( {""Records"": [" & vbCrLf & sRecs & vbCrLf & "], " 
        sRecordSet = sRecordSet & """RecordCount"":""" & lRecCnt & """ } )"
        RStoJSON = sRecordSet
    end if
end Function

' Return comma depending on condition and type fld or rec '
function GetDelim(bResult,sType)
    if bResult Then
        If sType = "fld" then
        GetDelim = ","
        End If
        If sType = "rec" Then
        GetDelim = "," & vbCrLf
        End if
    else
        GetDelim = ""
    end if
end function

Function fnQueryData()

Dim strJSON

Dim oCn,oRs1,strSQL

Set oCn = CreateObject( "ADODB.Connection" )
Set oRs1 = CreateObject( "ADODB.Recordset"  ) 

oCn.ConnectionString = "PROVIDER=SQLOLEDB" & ";Server=JEFFLD-HP\JD01SQL;Database=AdventureWorks2012;Trusted_Connection=Yes;"

oCn.open

strSQL = "select top 5 firstname,lastname,emailaddress from person.person P join person.emailaddress E on E.BusinessEntityID=P.BusinessEntityID"

oRs1.Open strSQL, oCn


strJSON = RStoJSON(oRS1)

' Clean up database resources
oRs1.Close
oCn.Close    
    
fnQueryData = strJSON    
    
End Function




</script>

<script language="javascript" type="text/javascript">
		
			function ProcessJSON(sJSON)
			{
			var rs = eval(sJSON);
			if ( rs ) { // has a non-null value
			  // get the record count
			  var str = rs.RecordCount + " Users:<br/>";
			  // get the data from the records
			  str = "<table border=1>"
			  for ( var recno = 0 ; recno < rs.RecordCount ; recno++ ) {
			    str += "<tr>";
			    str += "<td>"+rs.Records[recno].firstname + "</td>";
			    str += "<td>"+rs.Records[recno].lastname + "</td>";
			    str += "<td>"+rs.Records[recno].emailaddress + "</td>";
			    str += "</tr>";
			  }
			  str+= "</table>"
			  document.getElementById('userlist').innerHTML = str;
			  document.getElementById('json').innerHTML = sJSON;
			} else { // rs = null
			    str = "No users found"
			}
			}

	
</script>
             

</head>          
<body>
    <input type="button" name="btnShowJSON" id="btnShowJSON" value="Show JSON" onclick="btnShowJSON()"></input>
    <p></p>
    <div id="userlist"></div>
    <div id="json"></div>
</body>

</html>