March 31, 2016 at 12:00 am
Comments posted to this topic are about the item PowerShell To Get Active Directory Users And Groups into SQL
Lowell
March 31, 2016 at 1:42 am
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?
March 31, 2016 at 6:28 am
Thank you for the article.
March 31, 2016 at 6:42 am
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 realtimehttps://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
March 31, 2016 at 7:00 am
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
March 31, 2016 at 7:02 am
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 realtimehttps://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) 🙂
March 31, 2016 at 7:36 am
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
March 31, 2016 at 7:38 am
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
March 31, 2016 at 8:41 am
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. "
March 31, 2016 at 8:58 am
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
March 31, 2016 at 9:51 am
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.
March 31, 2016 at 9:52 am
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.
March 31, 2016 at 10:00 am
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
March 31, 2016 at 3:21 pm
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!
April 1, 2016 at 8:33 am
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