Update SQL command

Question

Could you help me with an 'UPDATE [User] ...' SQL command to provide the SSHPublicKeyRSA row with the keys?

Found nothing which works with SQL CE.

Comments

Unfortunately, because the public keys are stored in a blob, this isn't very easy to do, at least via an UPDATE command. What's needed is a small program to insert the contents of a file as a blob.

Answer 1

You can do it with the following Powershell script.  It uses the 32-bit version of the SqlServerCe DLL, so you'll need to run it in the 32-bit/x86 version of Powershell (see here).

$userName = "myuser"
$pkPath = "C:\Temp\pk.txt"
$configPath = "C:\ProgramData\Enterprise Distributed Technologies\Complete FTP\config.sdf" 

$pkBytes = [System.IO.File]::ReadAllBytes($pkPath)

[Reflection.Assembly]::LoadFile("C:\Program Files (x86)\Complete FTP\Server\System.Data.SqlServerCe.dll") | out-null
$connString = "Data Source=" + $configPath
$connection = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString
$connection.Open()

# create the command 
$command = new-object "System.Data.SqlServerCe.SqlCeCommand" 
$command.CommandType = [System.Data.CommandType]"Text" 
$command.CommandText = "UPDATE [User] SET SSHPublicKeyRSA=@PK WHERE [UserName]=@UserName" 
$command.Parameters.Add("@PK", $pkBytes) | out-null
$command.Parameters.Add("@UserName", $userName) | out-null
$command.Connection = $connection
$command.ExecuteNonQuery() | out-null

$command.CommandText = "SELECT SSHPublicKeyRSA FROM [User] WHERE [UserName]=@UserName" 
$result = $command.ExecuteScalar()

"Wrote " + $result.Length + " bytes to User.SSHPublicKeyRSA for " + $userName | Out-Default | Format-Table
$connection.Close()

You'll need to set the $userName and $pkPath variables to match your needs. The text file must contain the public keys as described in your other post, i.e.

MULTI
ssh-rsa ACBDEF0123456789....
ssh-rsa ACBDEF0123456789....