SQL Server Utilities - Use SqlCredential with Powershell to connect to an instance of SQL Server

In this post I will share how to use the SqlCredential object with Powershell when using SQL Authentication in order to pass the password in a secured way connecting to an instance of SQL Server.

First step is a one time step creating a secured encrypted password saved in a text file that we latter read in runtime and convert the encrypted saved text in the file to a SecureString object to be passed to sql server.

The first line uses the Read-Host command prompting the user to type in the password. Note that the encrypted password can be decrypted only on the machine where it was encrypted, this is by design and should not be an issue. In case you need your password file to be read on a machine where it was not created or multiple machines use the -Key option that results in using the Advanced Encryption Standard (AES) encryption algorithm instead of the default Windows Data Protection API (DPAPI), for more information on that see this post.

$Secure = Read-Host -AsSecureString;
$Encrypted = ConvertFrom-SecureString -SecureString $Secure;
$Encrypted | Set-Content c:\temp\sql_password.txt;

The bellow code block assigns some variables and after the variables assignment the code reads the saved encrypted password and converts it to a SecureString object to be passed to a PSCredential object. We then modify the credentials password to be ReadOnly which is a mandatory requirement when using SqlCredential latter on. The next bit then creates the $credential object by passing the cred that were generated using PSCredential to SqlCredential.

That’s it, we now have the credential object needed to be used with SqlCredential to connect to an instance of sql server using SQL Authentication.

The last line in the code snippet calls the Exec-Sql function that executes a database command. Based on the value assigned to the Boolean variable $windows_authentication the Exec-Sql function will build the appropriate SqlConnection object internally.

[string]$server = $env:COMPUTERNAME;
[string]$query = 'SET NOCOUNT ON; SELECT @@SERVERNAME;';
[string]$command_type = 'Scalar';
[string]$database = 'master';
[string]$sql_user = 'sql_login';
[bool]$windows_authentication = $false;

[string]$sql_password_full_name = 'c:\temp\sql_password.txt';
$password = Get-Content $sql_password_full_name | ConvertTo-SecureString;
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sql_user, $password;
$cred.Password.MakeReadOnly();
$credentials = New-Object System.Data.SqlClient.SqlCredential($cred.username, $cred.password);

# execute database command here
$ds = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;

Here is the Exec-Sql Powershell function providing data access to sql server that was used in the above code snippet. The function supports 3 execution methods:

NonQuery (i.e. any data modification command such as an insert, update , delete, select…into, merge )
Scalar (i.e. a query that returns a single value such as select count(*) or select getdate() )
DataSet (i.e. a query that returns one or more tables)

<#
.Synopsis
   Executes sql command
.DESCRIPTION
   A generic code to execute sql commands
.EXAMPLE
   ExecuteScalar
        $command_type = 'Scalar';
        $val = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;     
   DataSet        
    $command_type = 'DateSet';        
        $ds = Exec-Sql -Server $server -Database $database -CommandText $query -CommandType $command_type -IntegratedSecurity $windows_authentication -Credentials $credentials;     

#>
function Exec-Sql
{
    [CmdletBinding()]
    [Alias()]
    #[OutputType([int])]
    Param
    (    
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=0)]
        [string]$Server,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=1)]
        [string]$Database,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=2)]
        [string]$CommandText,

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=3)]
        [string]$CommandType,       

        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$false, Position=4)]
        [bool]$IntegratedSecurity,  
        
        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=5)]
        [System.Data.SqlClient.SqlCredential]$Credentials,
        
        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=6)]
        [int32]$CommandTimeOut = 30,

        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=7)]
        [string]$ApplicationName = "CloudMonitoring",
        
        [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$false, Position=8)]
        [string]$ApplicationIntent        
    )

    Begin
    {
        $ConnectionString = "Server=$Server; Database=$Database; Integrated Security=$IntegratedSecurity; Application Name=$ApplicationName;";
        
        if($CommandType -notin ('NonQuery' ,'Scalar' ,'DataSet') )
        {
            throw 'The ''$CommandType'' parameter contains an invalid value Valid values are: ''NonQuery'' ,''Scalar'' ,''DataSet''';
        }

        try
        {
            if ($windows_authentication)
            {
                $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
            }
            else
            {
                $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString, $Credentials);
                $SqlConnection.Credential = $Credentials;
            }

            
            $SqlCommand = $sqlConnection.CreateCommand();
            $SqlConnection.Open(); 
            $SqlCommand.CommandText = $CommandText;                      
                  
            # NonQuery
            if($CommandType -eq 'NonQuery')
            {      
                $sqlCommand.ExecuteNonQuery();
                $SqlConnection.Close();
                return;
            }

            # Scalar
            if($CommandType -eq 'Scalar')
            {                
                 Write-Host $CommandText -ForegroundColor Yellow   
                                   
                $Val = $sqlCommand.ExecuteScalar();
                $SqlConnection.Close();
                return $Val;
            }
            
            # DataSet
            if($CommandType -eq "DataSet")
            {
                $DataSet = New-Object System.Data.DataSet;
                $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
                $SqlDataAdapter.SelectCommand = $SqlCommand;    
                $SqlDataAdapter.Fill($DataSet);  
                $SqlConnection.Close();
                return $DataSet;   
            }
        }
        catch
        {       
            throw;
        }
    }
}