I have a powershell script that logs into a SQL server and writes data to a table whenever a user logs in. I want to protect the password and not just have it in cleartext within the script. What would be the best way to go about doing that?
$server = "sql"
$database = "login"
$table = "UserLogins"
$uid = "user"
$pwd = "password"
$username = $env:USERNAME
$computername = $env:COMPUTERNAME
$userProfilePath = "$env:USERPROFILE"
$date = Get-Date -Format "yyyy-MM-dd"
$time = Get-Date -Format "HH:mm:ss"
$userProfileSize = ((Get-ChildItem -Path $userProfilePath -Recurse -Force | Measure-Object -Property Length -Sum -ErrorAction Stop).Sum / 1MB).ToString("#.##")
$connectionString = "Server=$server;Database=$database;User ID= $uid;Password= $pwd;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = "IF NOT EXISTS (SELECT * FROM $table WHERE username = @username AND computername = @computername)
BEGIN
INSERT INTO $table (username, computername, date, time, profilesize)
VALUES (@username, @computername, @date, @time, @profilesize)
END
ELSE
BEGIN
UPDATE $table SET date = @date, time = @time, profilesize = @profilesize WHERE username = @username AND computername = @computername
END"
$command.Parameters.AddWithValue("@username", $username)
$command.Parameters.AddWithValue("@computername", $computername)
$command.Parameters.AddWithValue("@date", $date)
$command.Parameters.AddWithValue("@time", $time)
$command.Parameters.AddWithValue("@profilesize", $userProfileSize)
$command.ExecuteNonQuery()
$connection.Close()
