sql

psftp.exe: The server’s host key is not cached in the registry. You have no guarantee that the server is the computer you think it is.

 in Windows  tagged psftp.exe / sftp / sql / sql server / sql server management studio / windows by

Recently, we were debugging a scheduled job running on a Microsoft SQL Server Agent. After starting the SQL Server Management Studio, we saw that the specific task was using psftp.exe to upload some data securely to a remote server. When executing the job manually, it would work as expected. On the other hand, when the job would be executed automatically, it would always fail. After review the error logs, we got the following message:

Executed as user: FSRV\SYSTEM. …s\FTP\remote-server.ppk myuser@remote-server -batch -bc -be -b C:\putty\upload.txtThe server's host key is not cached in the registry. You  have no guarantee that the server is the computer you  think it is.  The server's rsa2 key fingerprint is:  ssh-rsa 2048 39:e4:84:b2:6f:bc:87:04:1f:21:bf:32:83:79:0b:cf  Connection abandoned.  DTSRun:  Loading…   DTSRun:  Executing…   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_3   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_3   DTSRun OnStart:  DTSStep_DTSActiveScriptTask_2   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_2   DTSRun OnStart:  DTSStep_DTSActiveScriptTask_3   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_3   DTSRun OnStart:  DTSStep_DTSDataPumpTask_1   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 34 Rows have been transformed or copied.; …  Process Exit Code 1.  The step failed.

The problem was with the account executing the scheduled job, which was different than the one that created the job. The second account, the one that was executing the scheduled jobs did not have any knowledge of the ssh-rsa key of the remote server. Because of this lack of information, psftp.exe could not verify that we were indeed trying to connect to the correct server. To fix this issue, we modified the psftp.exe execution command to match the following one:

C:\putty\psftp.exe -i C:\connections\FTP\remote-server.ppk myuser@remote-server -batch -bc -be -b C:\putty\upload.txt -hostkey 39:e4:84:b2:6f:bc:87:04:1f:21:bf:32:83:79:0b:cf

To help any reader that is not familiar with the psftp.exe and powershell (or cmd) we will breakdown the arguments of the above command:

  • C:\putty\psftp.exe : is the exact location of the psftp.exe binary on that server
  • -i C:\connections\FTP\remote-server.ppk : PPK files are PuTTY Private Key Files developed by Putty and they serve as storage for the private keys the program generated. In this case, instead of using a combination of username and password to authenticate, the client was given a private key to use as proof of identity and authenticity.
  • myuser@remote-server : The username and the domain or IP of the remote server.
  • -batch : Disables interactive prompts as no person will be supervising the script.
  • -bc : It displays batch commands in the same way they are run. It is useful for logging and troubleshooting.
  • -be : When running a batch file, this additional option causes psftp.exe to continue processing even if a command fails to complete successfully. An example you might want this to happen is the following: you want to delete a file and don’t care if it is already not present.
  • -b C:\putty\upload.txt : It specifies a file with batch commands. This argument helps users automate tasks by allowing them to set commands in advance.
  • -hostkey 39:e4:84:b2:6f:bc:87:04:1f:21:bf:32:83:79:0b:cf : Here, we copied the rsa-ssh key of the server that was displayed on the error and we explicitly defined it to let psftp.exe that it is trying to connect to the correct server. If you are not sure if the value you get at the errors is indeed the correct value, consult your system administrator.

Count how many submissions per score

 in SQL  tagged count / group by / max / order by / sql / truncate by

Recently, we had access to a database that contained the scores of a programming competitions system.
The database contained several contests, each contest contained several challenges and any competitor could make multiple submissions.
We wanted to extract a couple of charts showing

  • how many submissions we had per score and
  • how many submissions we had per score while filtering out the best submission (max score) per contestant per challenge per contest

The following code will return the number of submissions per score per challenge per contest.


SELECT contest_id, challenge_id, TRUNCATE(score, 1), COUNT(*)
FROM submissions
GROUP BY contest_id, challenge_id, TRUNCATE(score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(score, 1);

The next one will return the number of submissions per score per challenge per contest while filtering out the best submission (max score) per contestant per challenge per contest:


SELECT contest_id, challenge_id, TRUNCATE(max_score, 1), COUNT(*)
FROM
(
  SELECT contest_id, challenge_id, competitor_id, MAX(score) AS max_score
  FROM submissions
  GROUP BY contest_id, challenge_id, competitor_id
) AS max_scores
GROUP BY contest_id, challenge_id, TRUNCATE(max_score, 1)
ORDER BY contest_id, challenge_id, TRUNCATE(max_score, 1);

WordPress: Google AdSense Plugin 3

 in WordPress  tagged AdSense / Google Ads / Google AdSense Plugin / mysql / sql by

Recently, we were trying to changing the position of the Google Ads that are shown on a website using the Google AdSense plugin for WordPress.

After clicking on the Manage Ads button at the configuration page (http://example.com/wp-admin/options-general.php?page=GooglePublisherPlugin) we would get an error that another theme had set the active ads and that the plugin cannot proceed to modify them unless they are disabled from the other theme (Specifically we would get the following error: Due to a theme change, ads are not being shown. Please reconfigure your ads or restore the previous theme.).

(a) Disabling the plugin, (b) Uninstalling the plugin (c) Reconnecting with another AdSense account did not help at all.

What we did at the end was to remove the GooglePublisherPlugin entry from the wp_option database of our installation.

DELETE FROM `MyDatabase`.`wp_options` WHERE `option_name` = "GooglePublisherPlugin";

Next time we visited the configuration page, we were disconnected from the previous account and we had to reconnect.

GetStarted-AdSense
After reconnecting, we followed the configuration procedure, we pressed the Manage Ads button, the website was analyzed, we got the confirmation message and we clicked on the Save & Activate button to store the changes.

For a few hours, no ads were shown on the site. We did not take any action and on the next day that ads started appearing as expected.