Project Azure Azure_Users_SQL_Update
My design is set to pull and compare the ObjectID from the SQL table and AzueAD and only update records that are currently in the database table. I use a read only account that has to be set up to use Task Scheduler to connect to AzureAD that information is posted Here.
I use a Invoke-SQLCmd statement that allow the use of the Widows Account executing the script to login to SQL. This way I can use a one account assigned in Task Scheduler that has access to SQL and then an inline authentication in the script to access AzureAD.
#--------------Connect to SQL to Get List of Known Accounts by ObjectID---------------------
#Import the SQL Module & Query to Select Data All ObjectID
Import-Module "C:\Scheduled-Tasks\SQL_Module\SqlServer.psm1"
$Query= "SELECT ObjectID FROM [Azure_Data].[dbo].[Azure_User]"
$SQL_ObjectIDs=Invoke-Sqlcmd -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$Query"
#Build Usable Array for SQL Data (System.Data.DataRow)
$SQL_Array=@()
Foreach($SQLItem in $SQL_ObjectIDs){
$SQL_Array+=$SQLItem.Item(0);
}
#------------------------------Gather All User Data in Azure AD------------------------------
$AppID = 'b7bbcf98-f84e-xxxxx-66427c667105'
$TenantID = '8d151ea2-4bxxxx09b-e5e020c04470'
$Thumbprint = 'D5EA77AD4C9xxxxxD35032AF04E022C71F8'
Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint
$Userdata=Get-AzureADUser | Select *
#Compare Values to see allow only updating current accounts known to SQL
$ObjectID_LeftOver=Compare-Object -ReferenceObject $SQL_Array -DifferenceObject $Userdata.ObjectID -IncludeEqual | ? {$_.SideIndicator -eq '=='} | Select -ExpandProperty inputobject
#--------------------------------------------------------------------------------------------
#Loop Through Matching ObjectID and Update SQL only for these accounts
Foreach($Items in $ObjectID_LeftOver){
$MyValues=$Userdata | Where{$_.ObjectID -eq $Items}
#Variables
$DisplayName = $MyValues.DisplayName
$AccountEnabled = $MyValues.AccountEnabled
$JobTitle = $MyValues.JobTitle
$Mobile = $MyValues.Mobile
$TelephoneNumber = $MyValues.TelephoneNumber
$GivenName = $MyValues.GivenName
$Surname = $MyValues.Surname
#Update Query for Azure Users Table
$Update_Query="UPDATE [dbo].[Azure_User]
SET [DisplayName] = '$Displayname',
[AccountEnabled] = '$AccountEnabled',
[JobTitle] = '$JobTitle',
[Mobile] = '$Mobile',
[TelephoneNumber] = '$TelephoneNumber',
[Surname] = '$Surname',
[GivenName] = '$GivenName' WHERE [ObjectID] = '$Items'"
#Executes Command to Update Record
Invoke-Sqlcmd -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query $Update_Query
}