[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Update attribute in AD with data in SQL table via batch or script

I have two fields in an SQL 2K5 table (tblOne) named Name and ExtID. I need to run a loop and match each value in the Name field to saMAccountname fields in AD and update extensionAttribute12 with the correlating value in ExtID. Script or Batch is fine
0
Mike Miller
Asked:
Mike Miller
1 Solution
 
Chris DentPowerShell DeveloperCommented:

How about a bit of PowerShell?

It uses these two:

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx
http://www.quest.com/activeroles-server/arms.aspx

Both are free. You'd have to run PowerShell using the shortcut under the quest forlder. Or include this line at the beginning of the script:

Add-PsSnapIn "Quest.ActiveRoles.ADManagement"

The code below can run directly from the PowerShell prompt. If scheduling this then it should be saved as a .ps1 file and can be called with "PowerShell.exe ScriptName.ps1".

Chris
$Server = "TheSQLServer"
$Database = "TheDatabaseName"
$SqlQuery = "SELECT * FROM tblOne"
 
# Setup SQL Connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database =$Database;Integrated Security=True"
 
# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
 
#Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)
 
if ($nRecs -gt 0)
{
  $DataSet.Tables[0] | %{
 
    # This line gets the user by sAMAccountName and sets extensionAttribute 12 for that user.
    # Note that -WhatIf is used here for testing. Remove once happy.
 
    Get-QADUser $_.Name | Set-QADUser -ObjectAttributes @{extensionAttribute12='$($_.ExtID)'} -WhatIf
  }
}

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now