Author Posts

July 13, 2016 at 7:53 am

Hello All,

I am all new to this forum, can someone please help me with this..
I have below code for transferring activedirectory data into sql, can someone plz help me code to update activedirectory from sql server.

#GetActiveDirectoryUsers
Import-Module ActiveDirectory
###################################################################################################
# Powershell to grab all active directory users incrementally
#inserting or updating them into a SQL table.
###################################################################################################
function Get-String ($obj){
if ([string]::IsNullOrEmpty($obj))
{
return ""
}
else
{
return $obj.ToString();
}
}

###################################################################################################
#SQL Connection Setup
###################################################################################################
$SQLServer = "localhost";
$SQLDBName = "DBA_Utilities";
$SqlQuery = @"
UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
SET
[OperationalUnit] = @OperationalUnit,
[FirstName] = @FirstName,
[LastName] = @LastName,
[DisplayName] = @DisplayName,
[email] = @email,
[EmailAddress] = @EmailAddress,
[StreetAddress] = @StreetAddress,
[City] = @City,
[State] = @State,
[PostalCode] = @PostalCode,
[HomePhone] = @HomePhone,
[MobilePhone] = @MobilePhone,
[OfficePhone] = @OfficePhone,
[Fax] = @Fax,
[Company] = @Company,
[Organization] = @Organization,
[Department] = @Department,
[Title] = @Title,
[Description] = @Description,
[Office] = @Office,
[extensionAttribute1] = @extensionAttribute1,
[extensionAttribute2] = @extensionAttribute2,
[extensionAttribute3] = @extensionAttribute3,
[extensionAttribute4] = @extensionAttribute4,
[extensionAttribute5] = @extensionAttribute5,
[AccountExpires] = @AccountExpires,
[AccountIsEnabled] = @AccountIsEnabled,
[PasswordLastSet] = @PasswordLastSet,
[PasswordExpires] = @PasswordExpires,
[PasswordNeverExpires]= @PasswordNeverExpires,
[PasswordIsExpired] = @PasswordIsExpired,
[LastLogonTimestamp] = @LastLogonTimestamp,
[CreatedDate] = @CreatedDate,
[DWUpdatedDate] = getdate()
WHERE[sAMAccountName] = @sAMAccountName
AND [CanonicalName] = @CanonicalName

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
(CanonicalName,[sAMAccountName],[OperationalUnit],[FirstName],[LastName],[DisplayName],[email],[EmailAddress],[StreetAddress],[City],[State],[PostalCode],[HomePhone],[MobilePhone],[OfficePhone],[Office],[Fax],[Company],[Organization],[Department],[Title],[Description],[extensionAttribute1],[extensionAttribute2],[extensionAttribute3],[extensionAttribute4],[extensionAttribute5],[AccountExpires],[AccountIsEnabled],[PasswordLastSet],[PasswordExpires],[PasswordNeverExpires],[PasswordIsExpired],[LastLogonTimestamp])
SELECT @CanonicalName,@sAMAccountName ,@OperationalUnit ,@FirstName ,@LastName ,@DisplayName ,@email ,@EmailAddress ,@StreetAddress ,@City ,@State ,@PostalCode ,@HomePhone ,@MobilePhone ,@OfficePhone ,@Office ,@Fax ,@Company ,@Organization ,@Department ,@Title ,@Description ,@extensionAttribute1 ,@extensionAttribute2 ,@extensionAttribute3 ,@extensionAttribute4 ,@extensionAttribute5 ,@AccountExpires ,@AccountIsEnabled ,@PasswordLastSet ,@PasswordExpires ,@PasswordNeverExpires ,@PasswordIsExpired ,@LastLogonTimestamp
END

"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True";
$SqlConnection.Open();
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandType = [System.Data.CommandType]::Text ;

##$SqlCmd.CommandText = "SELECT COUNT(*) FROM [DBA_Utilities].[dbo].[ActiveDirectoryUsers]";
##$RecordCount = $SqlCmd.ExecuteScalar();

$SqlCmd.CommandText = $SqlQuery;

$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CanonicalName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@sAMAccountName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OperationalUnit", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FirstName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DisplayName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@email", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EmailAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@StreetAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@City", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@State", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PostalCode", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@HomePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@MobilePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OfficePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Fax", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Company", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Organization", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Department", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Title", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Description", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Office", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute1", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute2", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute3", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute4", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute5", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountIsEnabled", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordLastSet", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordNeverExpires",[Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordIsExpired", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastLogonTimestamp", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CreatedDate", [Data.SQLDBType]::VarChar, 128))) | Out-Null

###################################################################################################
#Get the Results From AD
###################################################################################################
#$Results = Get-ADUser -Filter * -Server "BigBang.com" -ResultSetSize 10 -Properties * | select -property sAMAccountName,ou,
$Results = Get-ADUser -Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,
GivenName,SurName,DisplayName,email,emailaddress,
StreetAddress,City,State,PostalCode,
HomePhone,MobilePhone,OfficePhone,Fax,
Company,Organization,Department,Title,Description,Office,
extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5,
@{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},Enabled,PasswordLastSet,
@{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}},PasswordNeverExpires,PasswordExpired,
LastLogonDate,whenCreated

foreach ($item in $Results) {
$SqlCmd.Parameters[0].Value = Get-String($item.CanonicalName)
$SqlCmd.Parameters[1].Value = Get-String($item.sAMAccountName)
$SqlCmd.Parameters[2].Value = Get-String($item.ou)
$SqlCmd.Parameters[3].Value = Get-String($item.GivenName)
$SqlCmd.Parameters[4].Value = Get-String($item.SurName)
$SqlCmd.Parameters[5].Value = Get-String($item.DisplayName)
$SqlCmd.Parameters[6].Value = Get-String($item.email)
$SqlCmd.Parameters[7].Value = Get-String($item.emailaddress)
$SqlCmd.Parameters[8].Value = Get-String($item.StreetAddress)
$SqlCmd.Parameters[9].Value = Get-String($item.City)
$SqlCmd.Parameters[10].Value = Get-String($item.State)
$SqlCmd.Parameters[11].Value = Get-String($item.PostalCode)
$SqlCmd.Parameters[12].Value = Get-String($item.HomePhone)
$SqlCmd.Parameters[13].Value = Get-String($item.MobilePhone)
$SqlCmd.Parameters[14].Value = Get-String($item.OfficePhone)
$SqlCmd.Parameters[15].Value = Get-String($item.Fax)
$SqlCmd.Parameters[16].Value = Get-String($item.Company)
$SqlCmd.Parameters[17].Value = Get-String($item.Organization)
$SqlCmd.Parameters[18].Value = Get-String($item.Department)
$SqlCmd.Parameters[19].Value = Get-String($item.Title)
$SqlCmd.Parameters[20].Value = Get-String($item.Description)
$SqlCmd.Parameters[21].Value = Get-String($item.Office)
$SqlCmd.Parameters[22].Value = Get-String($item.extensionAttribute1)
$SqlCmd.Parameters[23].Value = Get-String($item.extensionAttribute2)
$SqlCmd.Parameters[24].Value = Get-String($item.extensionAttribute3)
$SqlCmd.Parameters[25].Value = Get-String($item.extensionAttribute4)
$SqlCmd.Parameters[26].Value = Get-String($item.extensionAttribute5)
$SqlCmd.Parameters[27].Value = Get-String($item.AccountExpires)
$SqlCmd.Parameters[28].Value = Get-String($item.Enabled)
$SqlCmd.Parameters[29].Value = Get-String($item.PasswordLastSet)
$SqlCmd.Parameters[30].Value = Get-String($item.PasswordExpirationDate)
$SqlCmd.Parameters[31].Value = Get-String($item.PasswordNeverExpires)
$SqlCmd.Parameters[32].Value = Get-String($item.PasswordExpired)
$SqlCmd.Parameters[33].Value = Get-String($item.LastLogonDate)
$SqlCmd.Parameters[34].Value = Get-String($item.whenCreated)
$SqlCmd.ExecuteNonQuery();
}
if ($SqlConnection.State -eq "Open") {$SqlConnection.Close()}

Many thanks in advance.

July 13, 2016 at 1:27 pm

If you look right above the posting textbox, you'll see two options for formatting code – either use PRE tags, or post your code into a Gist and then paste the Gist URL here.

However, what you're asking is potentially a lot of work. While everyone here is happy to try and answer questions, we're not able to provide free script-writing services. If you have a specific question about where to start or are running into a specific problem, people here are usually happy to try and help. I'd post that in a new thread, rather than continuing this one.

July 13, 2016 at 10:38 pm

First, that is a really hard and ugly way to get AD information to a database. How many Active Directory records are you replicating to the database? From a performance standpoint, doing a BULK INSERT is the fastest method to get data into SQL that I've found.

Can you provide some background on why you are putting the data in the database and how it's going to be used?

July 13, 2016 at 10:44 pm

Can you provide some background on why you are trying to replicate Active Directory users to another database? There are some much easier methods to get data from Powershell to SQL. How many records would you be migrating? 100, 1000, 10000...