I faced some troubles in executing cmdkey.exe on a PowerShell remote session, while I was trying to automate the creation of a SQL Failover Cluster Instance on Azure VMs, using Azure Premium File Share (PFS) as shared storage.

You need cmdkey.exe since you must persist Azure PFS access credentials on the Credential Manager of both cluster nodes, into the security context of the SQL Server service account. The final command is something similar to:

cmdkey.exe /user:AZURE\[StorageAccountName] /pass:[StorageAccountKey] /target:[StorageAccountFileEndpointURI]

The official doc says you should RDP on both nodes with the service account credential and run the command, but this doesn’t sound good if you’re trying to automate the deployment.

So, I tried different PowerShell ways to execute the command from a remote session, with no luck. I tried Start-Process, Start-Job, Invoke-Command, and everything else I could think it could accept a remote execution; in each case, I modified the syntax to permit the execution of cmdkey as a job, as a process or similar. Then, I tried to execute the same command in an interactive remote session, started with Enter-PSSession, and I received this error:

CMDKEY: Credentials cannot be saved from this logon session

Google drove me to several threads, that substantially say cmdkey isn’t a good friend of remote sessions.

Somewhere I read that you can overcome this issue by using a scheduled task on the remote system, so I explored this way, trying to automate it.

For the sake of truth, I’ve also found some sources that said you could use cmdkey remotely by changing a local security policy; I discarded this approach because I don’t like to mess with them for temporary necessities. I also had some experience with SECEDIT parsing that remember me this way is a pita for automation.

CmdKey via a PowerShell remote session: the proposed approach

So, let’s come back to the “scheduled task” approach, and let’s see how to implement it.

Let’s assume CONTOSO\SqlSvc is our SQL Server service account. It is a simple domain user. Therefore, it has no additional privilege, neither on the remote system where you want to execute cmdkey.

1. Compose the cmdkey command line, by retrieving PFS details;
2. Register a scheduled task on the remote system, that runs the cmdkey command;
3. The scheduled task must run under the SQL Server service account credentials in an unattended way; to make it possible, the service account must have the “logon as a batch job” privilege. The easiest way to obtain it without messing with SECEDIT or GPO is to add the service account temporarily as a local admin on the remote system;
4. Trigger the execution of the scheduled task manually;
5. Clean up the remote system, deleting both the scheduled task and the local admin permissions assigned in step 2.

Now, it’s PowerShell time!

# Specifying some parameters
$rgName     = "Name of the RG"
$saName     = "Name of the storage account"
$pfsName    = "Name of the file share"
$sqlNode    = "RemoteSqlName"
$sqlSvc     = "CONTOSO\SqlSvc"
$sqlSvcPwd  = Read-Host -Prompt "CONTOSO\SqlSvc password" -asSecureString

# Retrieving storage account and file share
$fsSa = Get-AzStorageAccount -ResourceGroupName $rgName `
            -Name $saName

$pfs = Get-AzStorageShare -Name $pfsName `
            -Context $fsSa.Context

# Open a cim session on the remote SQL node
$cimSession = New-CimSession -ComputerName $sqlNode

# Composing cmdkey parameters
$target = $pfs.Uri.Host
$usrName = "Azure\$($fsSa.StorageAccountName)"
$usrPwd = $(($fsSa | Get-AzStorageAccountKey)[0].Value)

$cmdKeyArgs = @()
$cmdKeyArgs += "/add:$($target)"
$cmdKeyArgs += "/user:$($usrName)"
$cmdKeyArgs += "/pass:$($usrPwd)"

# Add SQL service account to remote - this manage the "logon as a batch job" requiment
Invoke-Command -ComputerName $sqlNode `
    -ScriptBlock { 
        Add-LocalGroupMember -Group "Administrators" `
            -Member $sqlSvc 
    }

# Create a scheduled task action to call the cmdkey command
$sta = New-ScheduledTaskAction "cmdkey" `
            -Argument ($cmdKeyArgs -join " ") `
            -CimSession $cimSession

# Register a scheduled tasks that can be called 
Register-ScheduledTask -TaskName "cmdKeySvcAccnt" `
    -Action $sta `
    -User $sqlSvc `
    -Password $sqlSvcPwd `
    -RunLevel Highest `
    -CimSession $cimSession

# Trigger the execution of the scheduled task
Get-ScheduledTask -TaskName "cmdKeySvcAccnt" -CimSession $cimSession | 
    Start-ScheduledTask

# Clean up the remote environment
Get-ScheduledTask -TaskName "cmdKeySvcAccnt" -CimSession $cimSession | 
    Unregister-ScheduledTask -Confirm:$false
    
Invoke-Command -ComputerName $sqlNode `
    -ScriptBlock { 
        Remove-LocalGroupMember -Group "Administrators" `
            -Member $sqlSvc 
    }

The script above assumes that:

• the caller has administrative privilege on the remote SQL Server machine
• the Az.Storage PowerShell module is available and correctly logged into a subscription on the client that runs the script (typically the first cluster node)
• PowerShell Remoting is up and running between client and remote node

Remember that the script adds credentials only to the credential store of the service account; it may be advisable to duplicate the steps related to the scheduled task, to manage additional accounts (i.e., the one that executes the SQL Server setup).

That’s all, folks!

About the Author:

Curious by nature, talkative geek who can speak in front of a public or a camera, in love with technology, especially Azure and SQL Server.
I want to understand how things work and to solve problems by myself during work as in my many hobbies

Reference:

Obinu, M. (2020). Executing CMDKEY via a PowerShell remote session. Available at: https://www.omegamadlab.com/executing-cmdkey-via-a-powershell-remote-session/ [Accessed: 23rd June 2020].