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
}