SharePoint Journey: PowerShell Pass Multiple Parameter using AddSript(store powershell OutPut In SQL table using Procedure and Table insertion Method)

By Procedure

param(

[string]$a,

[string]$CreatedBY,

[string]$LoginName,

[string]$RecID,

[string]$IsParticularsActionFlagvar

)

foreach ($i in $a)

{

#write "User CN Parameter: $i" >>C:\NGOForm\NGOlogfile.txt

$OU=$i

#$OU='CN=NGOuser5,OU=ExitUsersOU,OU=NGO-TEST_OU,DC=NGO,DC=Test,DC=com'

}

foreach ($j in $CreatedBY)

{

#write "User LoginName Parameter: $j" >>C:\NGOForm\NGOlogfile.txt

#$VarCreatedBY='Shrawan'

$VarCreatedBY=$j

}

foreach ($k in $LoginName)

{

#write "User LoginName Parameter: $j" >>C:\NGOForm\NGOlogfile.txt

#$UserName='NGO\ngoTest12'

$UserName=$LoginName

}

foreach ($h in $RecID)

{

#write "User LoginName Parameter: $j" >>C:\NGOForm\NGOlogfile.txt

#$VarRecID=4

$VarRecID=$h

}

foreach ($l in $IsParticularsActionFlagvar)

{

#write "User LoginName Parameter: $j" >>C:\NGOForm\NGOlogfile.txt

#$VarIsParticularsActionFlag=4

$VarIsParticularsActionFlag=$l

}

# Start Here Define The Passwoed Max Length

$length=Get-Content -Path C:\NGOForm\ResetPasswordMaxLength.txt

# End Here Define The Passwoed Max Length

$currentDate = Get-Date

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

}

# End Call The Fuction Of Rest Password

# Start Fuction Of Rest Password

Function Set-ADAccountPassNew {

[CmdletBinding()]

param(

[parameter(ValueFromPipeline=$true,Position=0,Mandatory=$True,HelpMessage="Complete OU Path")][ValidateNotNullOrEmpty()] [string[]]$OU ,

[parameter(ValueFromPipeline=$true,Position=1,Mandatory=$True,HelpMessage="UserName")][ValidateNotNullOrEmpty()] [string[]]$UserName ,

[parameter(ValueFromPipeline=$true,Position=2,Mandatory=$True,HelpMessage="Length of Random Password which should be divisible by 3")][ValidateNotNullOrEmpty()] [int]$length ,

[parameter(ValueFromPipeline=$true,Position=3,Mandatory=$True,HelpMessage="Is Particulars Action Flag")][ValidateNotNullOrEmpty()] [int[]]$VarIsParticularsActionFlag ,

[parameter(ValueFromPipeline=$true,Position=4,Mandatory=$True,HelpMessage="Created BY")][ValidateNotNullOrEmpty()] [string[]]$VarCreatedBY ,

[parameter(ValueFromPipeline=$true,Position=5,Mandatory=$True,HelpMessage="Rec ID")][ValidateNotNullOrEmpty()] [int[]]$VarRecID )

process {

Function random-password {

[CmdletBinding()]

param( [parameter(Position=0,Mandatory=$True,HelpMessage="Length of Random Password which should be divisible by 3")]

[ValidateScript({if( ($_ % 3) -eq 0 ) { $TRUE } else { Throw "Error!! Length of Random Password which should be divisible by 3" }})] [int]$length )

process {

$len= $length/3

$part1=-join ( (37..38) + (40..42) | Get-Random -Count $len | % {[char]$_})

$part2=-join ((65..90) + (97..122) | Get-Random -Count $len | % {[char]$_})

$part3=-join ((49..57) | Get-Random -Count $len | % {[char]$_})

$array = "$part1$part2$part3".ToCharArray()

[array]::sort($array)

$pass1 = "$array" | Get-Random -Count $length

$password = $pass1.replace(' ','')

return $password

}

$random=random-password -length $length

$NewPassword = ConvertTo-SecureString -AsPlainText $random -Force

# Start Log File

write "User CN Parameter: $OU" >>C:\NGOForm\NGOlogfile.txt

write "User Employee Name Parameter: $VarCreatedBY" >>C:\NGOForm\NGOlogfile.txt

write "User Windows Login Name : $UserName" >>C:\NGOForm\NGOlogfile.txt

write "User Parent Rec ID Parameter: $VarRecID" >>C:\NGOForm\NGOlogfile.txt

write "User Is Particulars Action Flag Parameter: $VarIsParticularsActionFlag">>C:\NGOForm\NGOlogfile.txt

write "Length Of Password: $length" >>C:\NGOForm\NGOlogfile.txt

write "Reset Password : $random" >>C:\NGOForm\NGOlogfile.txt

# End Log File

$DBServer = Get-Content -Path C:\NGOForm\DBServer.txt

$DBName = Get-Content -Path C:\NGOForm\DBName.txt

$Password = Get-Content -Path C:\NGOForm\DBPassword.txt

$UserID =Get-Content -Path C:\NGOForm\DBUserID.txt

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;User ID=$UserID;Password=$Password; Integrated Security=True;"

$sqlConnection.Open()

if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {

Write-Error "SQL Connection Failed! Connection to DB is not open."

Exit

}

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

Write-Output "SQL Connection successful! Connection to DB is open"

}

# Quit if the SQL connection didn't open properly.

$User = Get-Content -Path C:\NGOForm\AdminUserName.txt

$Pass = Get-Content -Path C:\NGOForm\AdminPassword.txt

$PWord = ConvertTo-SecureString -String "$Pass" -AsPlainText -Force

$mycred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User,$PWord

$error.clear()

Set-ADAccountPassword "$OU" -Server NGO.Test.com -Reset -Credential $mycred -NewPassword$NewPassword -ErrorVariable PRError

if($?)

{

$PStatus = Write-Output "Success! Password reset successfully"

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

$sqlCommand.CommandText = "[uspPowershellExecutionHistory] @NGORecID = '$VarRecID', @WindowsLoginID = '$UserName', @OUName = '$OU', @PowerShellExcutionMessage = 'Success! Password reset successful', @ResetPassword = '$random', @CreatedBy = '$VarCreatedBY', @IsParticularsActionFlag ='$VarIsParticularsActionFlag';"

$sqlCommand.ExecuteNonQuery()

# close connection

$sqlConnection.Close()

write "****************************Success! Password reset successfully On : $currentDate ******************************" >>C:\NGOForm\NGOlogfile.txt

}

else

{

Write-Output "Failed! Password reset failed"

$errorval = $error.exception

$errorval

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

$sqlCommand.CommandText = "[uspPowershellExecutionHistory] @NGORecID = '$VarRecID', @WindowsLoginID = '$UserName', @OUName = '$OU', @PowerShellExcutionMessage = 'Failed! Password reset failed', @ResetPassword = '$random', @CreatedBy = '$VarCreatedBY', @IsParticularsActionFlag ='$VarIsParticularsActionFlag';"

$sqlCommand.ExecuteNonQuery()

# close connection

$sqlConnection.Close()

write "****************************Failed! Password reset failed On : $currentDate ******************************" >>C:\NGOForm\NGOlogfile.txt

}

#$sqlConnection.Close()

}

# End Fuction Of Rest Password

# Start Call The Fuction Of Rest Password

Set-ADAccountPassNew -OU "$OU" -UserName "$UserName" -length "$length" -VarIsParticularsActionFlag "$VarIsParticularsActionFlag" -VarCreatedBY "$VarCreatedBY" -VarRecID "$VarRecID"

By Table

param(

[string]$a,

[string]$CreatedBY,

[string]$LoginName,

[string]$RecID,

[string]$IsParticularsActionFlagvar

)

foreach ($i in $a)

{

#write "User CN Parameter: $i" >>c:\NGOlogfile.txt

$VarCN=$i

}

foreach ($j in $CreatedBY)

{

#write "User LoginName Parameter: $j" >>c:\NGOlogfile.txt

$VarCreatedBY=$j

}

foreach ($k in $LoginName)

{

#write "User LoginName Parameter: $j" >>c:\NGOlogfile.txt

$varLoginName=$k

}

foreach ($h in $RecID)

{

#write "User LoginName Parameter: $j" >>c:\NGOlogfile.txt

$VarRecID=$h

}

foreach ($l in $IsParticularsActionFlagvar)

{

#write "User LoginName Parameter: $j" >>c:\NGOlogfile.txt

$VarIsParticularsActionFlag=$l

}

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

}

#$randomObj = New-Object System.Random

#$NewPassword=""

#1..12 | ForEach { $NewPassword = $NewPassword + [char]$randomObj.next(33,126) }

#$NewPassword

# Open SQL connection (you have to change these variables)

$DBServer = "111.22.33.54"

$DBName = "DB_NGO"

$Password = "NGO"

$UserID = "NGO"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;User ID=$UserID;Password=$Password; Integrated Security=True;"

$sqlConnection.Open()

# Quit if the SQL connection didn't open properly.

if ($sqlConnection.State -eq [Data.ConnectionState]::Close) {

$sqlConnection.Open()

}

#if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {

#"Connection to DB is not open."

# Exit

#write "Error: pa" >>c:\logfile.txt

$username = 'NGO\admin'

$Password = 'ngo' | ConvertTo-SecureString -Force -AsPlainText

$credential = New-Object System.Management.Automation.PsCredential($username, $Password)

Function random-password {

[CmdletBinding()]

param( [parameter(Position=0,Mandatory=$True,HelpMessage="Length of Random Password which should be divisible by 3")]

[ValidateScript({if( ($_ % 3) -eq 0 ) { $TRUE } else { Throw "Error!! Length of Random Password which should be divisible by 3" }})] [int]$length )

process {

$len= $length/3

$part1=-join ( (37..38) + (40..42) | Get-Random -Count $len | % {[char]$_})

$part2=-join ((65..90) + (97..122) | Get-Random -Count $len | % {[char]$_})

$part3=-join ((49..57) | Get-Random -Count $len | % {[char]$_})

$array = "$part1$part2$part3".ToCharArray()

[array]::sort($array)

$pass1 = "$array" | Get-Random -Count $length

$password = $pass1.replace(' ','')

return $password

}

$random=random-password -length $length

$NewPassword = ConvertTo-SecureString -AsPlainText $random -Force

write "User NewPassword Pass Parameter: $NewPassword" >>c:\NGOlogfile.txt

try

{

$job = Start-Job -scriptblock {

#Set-ADAccountPassword $args[0] -Server "NGO.Test.com" -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $NewPassword -Force)

#Set-ADAccountPassword $args[0] -Server "NGO.Test.com" -Reset -NewPassword (ConvertTo-SecureString -AsPlainText "vinay@3708" -Force)

Set-ADAccountPassword 'CN=Exit User1,OU=ExitUsersOU,OU=NGO-TEST_OU,DC=NGO,DC=Test,DC=com' -Server "NGO.Test.com" -Reset -NewPassword(ConvertTo-SecureString -AsPlainText "vinay@8852" -Force)

}-ArgumentList $a -credential $credential

Receive-Job $job

Wait-Job $job

if($?)

{

write "User CN Parameter: $VarCN" >>c:\NGOlogfile.txt

write "User Employee Name Parameter: $VarCreatedBY" >>c:\NGOlogfile.txt

write "User Windows Login Name : $varLoginName" >>c:\NGOlogfile.txt

write "User Parent Rec ID Parameter: $VarRecID" >>c:\NGOlogfile.txt

write "User Is Particulars Action Flag Parameter: $VarIsParticularsActionFlag">>c:\NGOlogfile.txt

$currentDate = Get-Date

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

# This SQL query will insert 1 row based on the parameters, and then will return the ID

# field of the row that was inserted.""

#$sqlCommand.CommandText ="INSERT INTO dbo.tblPowershellExecutionHistory (NGORecID,WindowsLoginID,CN,PowerShellExcutionMessage,ResetPassword,CreatedBy,CreatedDate,IsParticularsActionFlag) VALUES ('$VarRecID','$varLoginName','$VarCN','Password reset successfully: $job','$pass','$VarCreatedBY','$currentDate','$VarIsParticularsActionFlag'); "

$sqlCommand.CommandText ="INSERT INTO dbo.tblPowershellExecutionHistory (NGORecID,WindowsLoginID,CN,PowerShellExcutionMessage,ResetPassword,CreatedBy,CreatedDate,IsParticularsActionFlag,IsActive) VALUES ('$VarRecID','$varLoginName','$VarCN','Password reset successfully','$NewPassword','$VarCreatedBY','$currentDate','$VarIsParticularsActionFlag','YES'); "

$sqlCommand.ExecuteNonQuery()

write "Reset Password Success: Password reset successfully" >>c:\NGOlogfile.txt

#"Password reset successfully"

}

else

{

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

# This SQL query will insert 1 row based on the parameters, and then will return the ID

# field of the row that was inserted.""

$sqlCommand.CommandText ="INSERT INTO dbo.tblPowershellExecutionHistory (NGORecID,WindowsLoginID,CN,PowerShellExcutionMessage,ResetPassword,CreatedBy,CreatedDate) VALUES ('$VarRecID','$varLoginName','$VarCN','Password Reset failed','$NewPassword','$VarCreatedBY','$currentDate'); "

$sqlCommand.ExecuteNonQuery()

write "Reset Password Error: Password reset failed" >>c:\NGOlogfile.txt

#"Password reset failed"

}

# close connection

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

$sqlConnection.Close()

}

#Set-ADAccountPassword 'CN=Exit User1,OU=ExitUsersOU,OU=NGO-TEST_OU,DC=NGO,DC=Test,DC=com' -Server "NGO.Test.com" -Reset -NewPassword (ConvertTo-SecureString -AsPlainText "vinay@8822" -Force)

}

catch [Exception]

{

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

# This SQL query will insert 1 row based on the parameters, and then will return the ID

# field of the row that was inserted.""

$sqlCommand.CommandText ="INSERT INTO dbo.tblPowershellExecutionHistory (NGORecID,WindowsLoginID,CN,PowerShellExcutionMessage,ResetPassword,CreatedBy,CreatedDate) VALUES ('$VarRecID','$varLoginName','$VarCN','Error in reset password:$_.Exception.Message','$NewPassword','$VarCreatedBY','$currentDate'); "

$sqlCommand.ExecuteNonQuery()

write "Error: $_.Exception.Message" >>c:\NGOlogfile.txt

}

Procedure

ALTER PROCEDURE [dbo].[uspPowershellExecutionHistory]

@NGORecID int,

@WindowsLoginID nvarchar(200),

@OUName nvarchar(500),

@PowerShellExcutionMessage nvarchar(4000),

@ResetPassword nvarchar(100)=null,

@CreatedBy nvarchar(200),

@IsParticularsActionFlag int

BEGIN

INSERT INTO dbo.tblPowershellExecutionHistory

(

NGORecID,

WindowsLoginID,

CN,

PowerShellExcutionMessage,

ResetPassword,

CreatedBy,

CreatedDate,

IsParticularsActionFlag,

IsActive

)

VALUES (

@NGORecID,

@WindowsLoginID,

@OUName,

@PowerShellExcutionMessage,

@ResetPassword,

@CreatedBy,

GETDATE(),

@IsParticularsActionFlag,

'YES'

)

END

C# Code :

// ps.AddScript(@"C:\temp\DisableUserAccountPSScript.ps1 '" + dynamicReplacementParameterInScript + "'");

ps.AddScript(@"C:\temp\ChangePasswordPSScript.ps1 '" + dynamicReplacementParameterInScript + "' " + " '"+ CreatedBY + "' " + " '"+ LoginName + "' " + " '"+ RecID + "' ");

var results = ps.Invoke();

if (results.Count > 0)

{

ActionHappen = 1;

PowerShellScriptOutPutResult = "Password reset successfully";

}

else

{

ActionHappen = 0;

PowerShellScriptOutPutResult = " Password reset failed";

}