AD and SQL

I am trying to update a SQL table with some ad information. I do not want to drop the table every time that i want to update the table. I want some fails in place incase something goes wrong.

What i want to do is to export all the users from ad with a list of ad properties. That i have down with "Get-ADUser -Filter * -Properties * | Sort-Object cn" with what properties that i want to export.

So i want to have a MS SQL table with that information in it. I also what to be able to run an update every 15 minutes to update if for say the user is disabled or the account has a expiration date set. Or removed. I then need to know if the account was deleted to remove that entry from the sql table. 

I was trying to do a compare-object with the two arrays and not getting to fair. Does someone have something for this? Has anyone tried

August 25th, 2015 12:05pm

Is this something that AzureADSyncService or DirSync
Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 12:09pm

1. If you are going to return all users, I would not do -Properties *, only grab the properties you need. This will help in performance with large AD environments.

2. What is the purpose of replicating AD data in SQL server?

August 25th, 2015 12:14pm

As Clayman2 asked, what is the purpose?  This may help with the solution or give us the code you have so far.

Example I have for a start:  Does a group membership search for Office365 licenses, but could be done for user attributes very easily.

function sqlConnect
    {
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Data Source=Server1\MSONLINE;Initial Catalog=Office365Services;Integrated Security=SSPI;"
        $conn.Open()
        #$conn.Close()
    }
$officeProPlusMembers = Get-ADGroupMember Office365_Enable_Office365ProPlus
sqlConnect
foreach ($officeProPlusMember in $officeProPlusMembers)
{
    Get-ADUser $officeProPlusMember.distinguishedName -Properties name,mail | Select-Object mail
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    $cmd.commandtext = "INSERT INTO Services (UserName,Licensed,YAMMER_ENTERPRISE,RMS_S_ENTERPRISE
                        OFFICESUBSCRIPTION,MCOSTANDARD,SHAREPOINTWAC,SHAREPOINTENTERPRISE,EXCHANGE_S_ENTERPRISE) VALUES($($officeProPlusMember.mail),1,DISABLED,DISABLED,ENABLED,DISABLED,DISABLED,DISABLED,DISABLED)" 
    $cmd.executenonquery()
    
}
$conn.close()

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 12:30pm

I know about the * on the properties. But this is for sorta user automation. Comparing some other sql based outputs that i have to compare what is in ad. I can create some views and then review and change information. For example i have a sql that has department and address from one sql table. I Job Titles and Managers out of another. I have phone information from another. I have some group member information out of another. There is more in SQL. That is why I want to have a SQL view of AD. Microsoft had removed the way to query AD right from SQL. We use to use that. 

I want to have outputs that would:

1. Tell me that i need to insert a row with data into a SQL table.
2. Tell me to remove a user that is not longer in AD from the table.
3. Tell me to update fields and what fields that need to be updated.


August 25th, 2015 3:19pm

Still not sure what the benefit of repeating all the data that AD has into a SQL database. What you want sounds like a big project, so I would start off small first. vaadadmin2010 posted a script to connect to a dabase and insert information, you can use that as a guide.
Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 3:23pm

You are not asking a question.  You are asking for a solution. 

I do not recommend pursuing this as you would need to have clear technical skills in Active Directory, SQL Server and PowerShell.  You would also have to have clear abilities in the design and implementation of complex programming systems. 

If this is of critical importance you will need to contact a skilled consultant to assist you.

Good luck.

August 25th, 2015 11:58pm

Yes, I understand that. My current process is driven on sql. I am clearing a temp table and then putting the data from ad into a temp table. Then i have scripts in SQL to update the tables. I wanted to know if i could do the compares in powershell. I can get one table with the same headers of AD and one table with same headers in SQL table in two arrays in powershell. I am looking for the best way to compare the two and log out of differences. From there i can do the updates and the deletes in sql. I get that. I just do not want to pretty much drop the table and insert what is there all the time.

The other way of doing this is going line by line and comparing against the other array of items. I can accomplish that with some foreach's. So am i looking for a solution. Yes. I am only looking for some ideas on how to compare in powershell two arrays or hash tables and write out what the differences are. The what data I want to compare and writing the update and delete lines for sql I can do already. Just looking for something that can compare 20000 records and with a few minutes let me know what the differences are.

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 8:23am

Yes, I understand that. My current process is driven on sql. I am clearing a temp table and then putting the data from ad into a temp table. Then i have scripts in SQL to update the tables. I wanted to know if i could do the compares in powershell. I can get one table with the same headers of AD and one table with same headers in SQL table in two arrays in powershell. I am looking for the best way to compare the two and log out of differences. From there i can do the updates and the deletes in sql. I get that. I just do not want to pretty much drop the table and insert what is there all the time.

The other way of doing this is going line by line and comparing against the other array of items. I can accomplish that with some foreach's. So am i looking for a solution. Yes. I am only looking for some ideas on how to compare in powershell two arrays or hash tables and write out what the differences are. The what data I want to compare and writing the update and delete lines for sql I can do already. Just looking for something that can compare 20000 records and with a few minutes let me know what the differences

August 26th, 2015 8:38am

For deleted users.

$removedate = (get-date).adddays(-1)
$userstoremove = Get-MsolUser -ReturnDeletedUsers -All | ?{$_.islicensed -eq $true -and $_.softdeletiontimestamp -gt $removedate}

$sqlQuery = "delete from $table where upn like '$upn'"


Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 8:51am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics