June 26, 2014 at 5:58 am
Hi All,
I was trying to automate my Password reset activity for Sql Logins from a central servr using Powershell.
With the help of some useful tips from the below link, I was able to create a script : http://www.mssqltips.com/sqlservertip/1947/connect-to-sql-server-via-windows-powershell-with-sql-server-authentication/
Initially I am just trying to read from the csv file and print the details to check if it works properly. Here is the script :
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#imports the csv file with details
$Items = Import-CSV D:\TEMP\test.csv -Delimiter "|" #Use the chosen delimiter
ForEach ($Item in $Items)
{
$Instance = $Item.ServerName
$uname = $Item.uname
$pwd = $Item.pwd
$newpwd = $Item.newpwd
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
#This sets the sql authentication
$srv.ConnectionContext.LoginSecure=$false;
#This sets the login name
$srv.ConnectionContext.set_Login($uname);
#This sets the password
$srv.ConnectionContext.set_Password($pwd)
echo $Instance
echo $uname
echo $pwd
echo $newpwd
}
This script works fine and prints everything as desired. However, there is an error that pops up in the powershell window and I would like to handle this error before I proceed.
The error is as below :
Exception calling "set_Login" with "1" argument(s): "Cannot apply value `null' to property Login: Value cannot be null
."
At line:11 char:33
+ $srv.ConnectionContext.set_Login <<<< ($uname);
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Can someone please let me know if there is anything wrong that I am doing here?
Thanks a Lot !!!
June 27, 2014 at 3:36 am
The following checks that you have values for each of the four variables:
if(($Instance) -and ($uname) -and ($pwd) -and ($newpwd))
{
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
#This sets the sql authentication
$srv.ConnectionContext.LoginSecure=$false;
#This sets the login name
$srv.ConnectionContext.set_Login($uname);
#This sets the password
$srv.ConnectionContext.set_Password($pwd)
}
else
{
# Handle missing value(s) condition.
}
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 29, 2014 at 7:35 am
Thanks..
I will check with this condition and get back to let you know how it works now 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply