Powershell To Get Active Directory Users And Groups into SQL!

  • Lowell

    SSC Guru

    Points: 323357

    Comments posted to this topic are about the item Powershell To Get Active Directory Users And Groups into SQL!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • matt 27758

    SSC Veteran

    Points: 213

    Thanks for this nice article very informative. Its been a while since I had to do anything with AD from SQL Server, we traditionally used a view with a linked server/openquery which was realtime

    https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

    Is your method advantageous in some way?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thank you for the article.

  • Lowell

    SSC Guru

    Points: 323357

    matt 27758 (3/31/2016)


    Thanks for this nice article very informative. Its been a while since I had to do anything with AD from SQL Server, we traditionally used a view with a linked server/openquery which was realtime

    https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

    Is your method advantageous in some way?

    sure lots of advantages.

    probably the most important is the ability to run my version as is, and you get immediate results.

    The Linked server/LDAP version requires some setup, just so you can query the domain(having to edit ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''.) i know my domain, but does everyone know there DC? their OU?

    My version is a convenient DBAlevel 101 fire and review, so it's a fantastic baseline for enhancing,and that is already capturing tons of information.

    My version is seriously much more robust than the LDAP example; the SQL tables , the logic to insert and update to capture changes, getting groups and group members. the LDAP is an example you have to grab and enhance, do a ton of due diligence and discover the LDAP names for the properties manually, and modify for groups and group members, and finally then capture the information yourself; it's not something you can immediately consume and report on yet.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Marek Grzymala

    SSCommitted

    Points: 1890

    Get-ADUser : The server has returned the following error: invalid enumeration context.

    At D:\_TEMP\GetActiveDirectoryUsersWithPowerShell\GetActiveDirectoryUsers.ps1:123 char:22

    + $Results = Get-ADUser <<<< -Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,GivenName,SurName,DisplayName,email,emailaddress,StreetAddress,City,State,PostalCode,HomePhone,MobilePhone,OfficePhon

    e,Fax, Company,Organization,Department,Title,Description,Office, extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5, @{Name='AccountExpires';Expression={[DATETIME]::fromFil

    eTime($_.accountExpires)}},Enabled,PasswordLastSet, @{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}}, PasswordNeverExpires,PasswordExpired, LastLogonDate,whenCreated

    + CategoryInfo : NotSpecified: (:) [Get-ADUser], ADException

    + FullyQualifiedErrorId : The server has returned the following error: invalid enumeration context.,Microsoft.ActiveDirectory.Management.Commands.GetADUser

  • matt 27758

    SSC Veteran

    Points: 213

    Lowell (3/31/2016)


    matt 27758 (3/31/2016)


    Thanks for this nice article very informative. Its been a while since I had to do anything with AD from SQL Server, we traditionally used a view with a linked server/openquery which was realtime

    https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

    Is your method advantageous in some way?

    sure lots of advantages.

    probably the most important is the ability to run my version as is, and you get immediate results.

    The Linked server/LDAP version requires some setup, just so you can query the domain(having to edit ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''.) i know my domain, but does everyone know there DC? their OU?

    My version is a convenient DBAlevel 101 fire and review, so it's a fantastic baseline for enhancing,and that is already capturing tons of information.

    My version is seriously much more robust than the LDAP example; the SQL tables , the logic to insert and update to capture changes, getting groups and group members. the LDAP is an example you have to grab and enhance, do a ton of due diligence and discover the LDAP names for the properties manually, and modify for groups and group members, and finally then capture the information yourself; it's not something you can immediately consume and report on yet.

    Cool makes sense thank you. I was quite lucky I suppose also being a domain admin and a single domain so it worked for us (and it was back on Win/SQL2k) 🙂

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    I would recommend against the Quest (now Dell cmdlets). They don't release memory very well and you can't force garbage collection on them. So while they are great for small numbers of users/groups like in the examples given, I've seen them crash out a 16 GB server processing only about 5,000 users. We ended up making .NET calls directly.

    K. Brian Kelley
    @kbriankelley

  • Lowell

    SSC Guru

    Points: 323357

    Marek Grzymala (3/31/2016)


    Get-ADUser : The server has returned the following error: invalid enumeration context.

    At D:\_TEMP\GetActiveDirectoryUsersWithPowerShell\GetActiveDirectoryUsers.ps1:123 char:22

    + $Results = Get-ADUser <<<< -Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,GivenName,SurName,DisplayName,email,emailaddress,StreetAddress,City,State,PostalCode,HomePhone,MobilePhone,OfficePhon

    e,Fax, Company,Organization,Department,Title,Description,Office, extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5, @{Name='AccountExpires';Expression={[DATETIME]::fromFil

    eTime($_.accountExpires)}},Enabled,PasswordLastSet, @{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}}, PasswordNeverExpires,PasswordExpired, LastLogonDate,whenCreated

    + CategoryInfo : NotSpecified: (:) [Get-ADUser], ADException

    + FullyQualifiedErrorId : The server has returned the following error: invalid enumeration context.,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Marek from what i see, i see that the size/quantity of the data being returned is too large, and the suggestions on powershell.com and stackoverflow.com say to try to use a -ResultPageSize 500 or -ResultPageSize 1000 parameter;

    i'll test it and post my results.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • cyp901

    Mr or Mrs. 500

    Points: 539

    Depending on the size of active directory... This may not be the most efficient way to get the data, especially for group memberships. It has been a minute, but I do not think the powershell ad-group commands will resolve nested groups.

    Pulling all the groups and running a CTE on it is certainly one way to determine nested groups, but active directory already does this for you.

    "The tokenGroups property of an Active Directory (AD) user object contains the binary security identifiers (SIDs) of all of the security groups that a user is a member of. "

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    cyp901 (3/31/2016)


    Depending on the size of active directory... This may not be the most efficient way to get the data, especially for group memberships. It has been a minute, but I do not think the powershell ad-group commands will resolve nested groups.

    Pulling all the groups and running a CTE on it is certainly one way to determine nested groups, but active directory already does this for you.

    "The tokenGroups property of an Active Directory (AD) user object contains the binary security identifiers (SIDs) of all of the security groups that a user is a member of. "

    If you do an LDAP query (such as with a call to System.DirectoryServices), if this is part of your filter, you'll get recursion:

    memberof:1.2.840.113556.1.4.1941

    For instance, assuming $groupDN has your group's DN, if you're filter is set to the following, it'll recurse and pull all user members of that group, including nesting, in PowerShell:

    "(&(objectClass=user)(memberof:1.2.840.113556.1.4.1941:=$($groupDN)))"

    K. Brian Kelley
    @kbriankelley

  • JJ B

    SSCarpal Tunnel

    Points: 4570

    We use active directory to handle security for our internal applications also. I think it is a great method as our Help Desk staff can handle security maintenance this way. I also think it is a good security practice to have someone other than me, as DBA and developer, controlling who is in which security groups. Thanks for sharing this nice feature.

  • cyp901

    Mr or Mrs. 500

    Points: 539

    K. Brian Kelley (3/31/2016)


    cyp901 (3/31/2016)


    Depending on the size of active directory... This may not be the most efficient way to get the data, especially for group memberships. It has been a minute, but I do not think the powershell ad-group commands will resolve nested groups.

    Pulling all the groups and running a CTE on it is certainly one way to determine nested groups, but active directory already does this for you.

    "The tokenGroups property of an Active Directory (AD) user object contains the binary security identifiers (SIDs) of all of the security groups that a user is a member of. "

    If you do an LDAP query (such as with a call to System.DirectoryServices), if this is part of your filter, you'll get recursion:

    memberof:1.2.840.113556.1.4.1941

    For instance, assuming $groupDN has your group's DN, if you're filter is set to the following, it'll recurse and pull all user members of that group, including nesting, in PowerShell:

    "(&(objectClass=user)(memberof:1.2.840.113556.1.4.1941:=$($groupDN)))"

    This is correct. Definitely the best approach to use when dealing with groups.

  • andre.quitta

    SSCommitted

    Points: 1862

    when I tried to run the code in Powershell, I got the following error.

    [font="Courier New"]Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

    At line:3 char:1

    + Import-Module ActiveDirectory

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : ResourceUnavailable: (ActiveDirectory:String) [Import-Module], FileNotFoundException

    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    [/font]

    So I tried to download the module ActiveDirectory from this site and got a page not found. Can someone suggest a download location for this module?

    [font="Courier New"]https://blogs.msdn.microsoft.com/rkramesh/2012/01/17/how-to-add-active-directory-module-in-powershell-in-windows-7/[/font]

    Thanks much

  • David Dye-457666

    SSC Journeyman

    Points: 82

    Lowell,

    AWESOME article! I had been using a linked server passing an LDAP query for years and hadn't considered using PowerShell. The only problem that I ran into was when calling the Get-ADUser cmdlet. The error was

    "Get-ADUser : One or more properties are invalid".

    I changed the cmdlet to pipe the properties to Get-ADObject. For example:

    Get-ADUser -Filter * -ResultSetSize 100 -Properties *

    I changed to:

    Get-ADUser -Filter * -ResultSetSize 100 [highlight="#FFFBCC"]| Get-ADObject -Properties * [/highlight]

    Not sure what the underlying issue is, but by making this small change I was able to run all of your scripts perfectly.

    Great work and Thanks again!

  • Ray Herring

    SSCertifiable

    Points: 5207

    andre.quitta (3/31/2016)


    when I tried to run the code in Powershell, I got the following error.

    [font="Courier New"]Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

    At line:3 char:1

    + Import-Module ActiveDirectory

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : ResourceUnavailable: (ActiveDirectory:String) [Import-Module], FileNotFoundException

    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    [/font]

    So I tried to download the module ActiveDirectory from this site and got a page not found. Can someone suggest a download location for this module?

    [font="Courier New"]https://blogs.msdn.microsoft.com/rkramesh/2012/01/17/how-to-add-active-directory-module-in-powershell-in-windows-7/[/font]

    Thanks much

    I receive a similar error when I try to Import-Module ActiveDirectory.

    WARNING: Error initializing default drive: 'Unable to find a default server with Active Directory Web Services running.'.

    What do I need to set up to resolve this issue?

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply