PS Script to Import users from Azure SQL Table to AAD

Question

I need help with a baseline script (if possible at all) to extract users from Azure SQL to Azure AD with the fields in the SQL tables. Is there such a way to get this into AAD, not On Premise AD? The fields are phone, department..etc However does such a way even exist? I know for On Premise yes, but I cannot seem to locate anything for Azure.

Thank you all in Advance!

Does it need to be done all in one go? Is this a one off or should be repeatable. You can write Powershell code to connect to a database, extract your data then write that data to AAD. Or you can simplify it a little if you first extract to CSV as a manual step as mentioned below — Nick.Mc, Jun 30 '22 at 07:44
It needs repetition. Is there a way to directly connect the SQL table to Azure to update the fields in AAD? — PCS Services, Jul 01 '22 at 18:35
Yes there is but it seems like you're just asking more questions instead of trying things out. If you're lucky someone might write an entire script for you but in the meantime you should try the provided scripts and understand them and you might work it out yourself. Especially if you are selling IT services. — Nick.Mc, Jul 03 '22 at 11:29

Delliganesh Sevanesan · Answer 1 · 2022-06-30T04:42:08.110

to extract users from Azure SQL to Azure AD with the fields in the SQL tables

Achieving this you have to follow the below steps.

Get the user table data from the SQL table
Export the user data into csv file.
create the users in Azure AD using the csv file.

You can use the below script to export Azure SQL Table data. Then exported data can be saved in a csv file.

Import-Module sqlps  
$SQLServer = "<Your Azure SQL server>"  
$DatabaseName = "<Database Name>"  
$ExportLocation = "<Path to Export>"
$ResourceGroupName ="<Your resource group name>"  

#Select Database
Get-AzureRmSqlDatabase -ServerName $SQLServer -ResourceGroupName $ResourceGroupName | select $DatabaseName 

# Get Server Instance
$ServerInstance = Get-AzureRmSqlServer -ResourceGroupName $ResourceGroupName | where {$_.ServerName -eq $SQLServer}


$params = @{
'Database' = $DatabaseName
'ServerInstance' = $ServerInstance
'Username' = '<userName>'
'Password' = '<Password>'
'Query' = 'SELECT * FROM UsersTable'
}

$userResult = Invoke-Sqlcmd @params

$result |export-csv "$ExportLocation$userinfo.csv" -notypeinformation

Fetching all information from table

# Get user information from table
$Tables = (Get-SqlDatabase -ServerInstance $ServerInstance -Name $DatabaseName).tables  
foreach($table in $Tables) {  
$SQLquery="select * from $($table)"  
$result=invoke-sqlcmd -query $SQLquery -serverinstance $SQLServer -database $DatabaseName  
Write-Host "Now Exporting Table $table"  
$result |export-csv "$ExportLocation$($table.Name).csv" -notypeinformation  
}

Or in an Azure portal -> Azure SQL Database -> Query Editor also you can export the csv file. enter image description here

After exported the CSV file import the user information into Azure AD.

Refer Link 1 & Link 2 to import users into Azure AD

Thank you very much for this! Is there a way to automate this? Just the pulling the table data to .csv? And, What if I need to pull or specify a single user instead of the whole table? — PCS Services, Jul 01 '22 at 16:26
Yes, you can automate the PowerShell script using [Runbook](https://learn.microsoft.com/en-us/azure/automation/learn/powershell-runbook-managed-identity). if you want to get a specific user change the query accordingly. — Delliganesh Sevanesan, Jul 02 '22 at 00:48

PS Script to Import users from Azure SQL Table to AAD

1 Answers1