Posted On June 14, 2020

PowerShell: Validate SQL Server Credentials

kimconnect 0 comments
blog.KimConnect.com >> Codes >> PowerShell: Validate SQL Server Credentials

Add this to your SQL toolbox so that it’ll be quick and easy to validate a given MS SQL database credential before applying it to your scripts.

$id='sa'
$password='somePasswordHere'
$sqlServer='sqlServerName'
$databaseName='bookFace'
$cred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $id,$(ConvertTo-securestring $password -AsPlainText -Force)

function testSqlCredential {
    param(
        [Parameter(Mandatory)][string]$sqlServer,
        [Parameter(Mandatory)][pscredential]$credential,
        [string]$databaseName='master'
    )

    $ErrorActionPreference = 'Stop'

    try {
        $id = $credential.UserName
        $password = $credential.GetNetworkCredential().Password
        $connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $sqlServer,$databaseName,$id,$password
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
        $sqlConnection.Open()
        $sqlConnection.Close()
        write-host "Credential is valid for $servername for database $databaseName"
        return $true
        }
    catch {
        write-warning "$error"
        return $false
        }
}

testSqlCredential $sqlServer $cred $databaseName

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post

PowerShell: How to Properly Delete a Msol User Account

# Set the user ObjectID attribute variable$msolUser="6f2fcfcd-...."# Move user account to Recycle BinRemove-MsolUser -ObjectId $msolUser#…

Basic CSS: Use RGB to Mix Colors

<style>.red-text {color: #000000;}.orchid-text {color: #000000;}.sienna-text {color: #000000;}.blue-text {color: #000000;}</style><h1 class="red-text">I am red!</h1><h1 class="orchid-text">I am orchid!</h1><h1…

How To Remove A Program on Windows Using PowerShell

# removeAppwizProgram.ps1 # Version 0.02 $computernames=@( 'SERVER0001', 'SERVER0002' ) $appName='Dell EMC OpenManage Systems Management Software…