Our Products:   CompleteFTP  edtFTPnet/Free  edtFTPnet/PRO  edtFTPj/Free  edtFTPj/PRO

Update SQL command

0 votes
asked Apr 26 in CompleteFTP by HansHuebner (190 points)
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.
commented Apr 26 by support2 (143,710 points)
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.

1 Answer

0 votes
answered Apr 27 by Admin (2,230 points)

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

# 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

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.

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