Project Azure_Users_SQL_Insert
Below is the full script I use in Task Scheduler to be executed each day at 8 AM to insert any new user accounts without any interaction required. 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.
​
If you read through the script, you will see I
poll AzureAD and SQL and do a comparison
by ObjectID to assure I only add new accounts.
Special Note: If you are not familiar with this type of connection to AzureAD its due to the need to logon via Task Scheduler. You need a passthrough "Unattended Login" read only account. I explain and link to site how to create this type of account Here.
​
Connection: Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint
#Install Azure AD Module Internet & Import the SQL Module (Local File Systems) for PowerShell
install-module AzureAD
Import-Module "C:\Scheduled-Tasks\SQL_Module\SqlServer.psm1"
#Auto login Data & Connect to Azure
$AppID = 'b7bbcxxxxxx-9c667105'
$TenantID = '8d151eaxxxxxx5e020c04470'
$Thumbprint = 'D5Exxxxxxx752AF04E022C71F8'
Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint
#-------------------------------------------------------------------------------------------------
# Compare Data to see what accounts are new and not in SQL
#-------------------------------------------------------------------------------------------------
#Gather All Azure AD Users
$Userdata=Get-AzureADUser | Select *
#Gather All SQL Users
$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);
}
#Compare Values allow only new accounts into SQL
$AccountID=Compare-Object -ReferenceObject $SQL_Array -DifferenceObject $Userdata.ObjectID | ? {$_.SideIndicator -eq '=>'} | Select -ExpandProperty inputobject
#Loop Through New Accounts to be Added by ObjectID
$NewUserData=@()
Foreach($Account in $AccountID){
#Gather All User Data in Azure AD for Select ObjectID
$NewUserData+=Get-AzureADUser | Select * | Where{$_.ObjectID -eq $AccountID}
}
#Loop Through and Insert into SQL
Foreach($Item in $NewUserData){
#Variables
$UserPrincipalName = $Item.UserPrincipalName
$DisplayName = $Item.DisplayName
$AccountEnabled = $Item.AccountEnabled
$ObjectId = $Item.ObjectId
$JobTitle = $Item.JobTitle
$Mobile = $Item.Mobile
$TelephoneNumber = $Item.TelephoneNumber
$GivenName = $Item.GivenName
$Surname = $Surname
​
#Default Template for Inserting Data into SQL
$MySQL_Insert="INSERT INTO [dbo].[Azure_User]
([UPN]
,[DisplayName]
,[AccountEnabled]
,[ObjectId]
,[JobTitle]
,[Mobile]
,[TelephoneNumber]
,[Surname]
,[GivenName])
VALUES
('$UserPrincipalName',
'$DisplayName',
'$AccountEnabled',
'$ObjectId',
'$JobTitle',
'$Mobile',
'$TelephoneNumber',
'$Surname',
'$GivenName')"
#Command to Execute and Insert to SQL
Invoke-Sqlcmd -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query $MySQL_Insert
}