top of page

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)
Foreach($SQLItem in $SQL_ObjectIDs){

#------------------------------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}
    $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


bottom of page