xp_cmdshell PSCP - "The server''s host key is not cached in the registry"

  • Hi,

    In the same machine, I can run the following PSCP command to fetch a

    file from remote server from a command window:

    C:\Download\Putty\pscp.exe -p -pw xxxx@xxxx

    name@serverIP:/export//detail-20060518.csv

    C:\Temp\Test\detail-20060518.csv

    But when I run it in SQL Sever Query Analyzer (log in as 'sa')

    Exec master..xp_cmdshell 'C:\Download\Putty\pscp.exe -p -pw xxxx@xxxx

    name@serverIP:/export//detail-20060518.csv

    C:\Temp\Test\detail-20060518.csv'

     I always get the following error message:

    The server's host key is not cached in the registry.......... Store key

    in cache? (y/n) Connection abandoned

    What I am trying to do is to schedule a job to run the above command

    Any ideas/suggestions please.

    Thanks in advance.

    Bob

  • Hello Bob,

    I think you had to run (once per ssh server) pscp with the account impersonified by sql server and sql server agent in order to have pscp create the registry key

    andrea

  • Hi Andrea,

    Thanks for the reply, but HOW CAN I DO THAT ? I tried login as 'sa' and/or Windows Authorization account (I installed the SQL Server in that machine), open Query Analyzer to run the above script, got the same error message.

    If you can please provide more details, or a link to reference site/pages would be highly appreciated.

    Bob

  • I think, you had to logon on the machine with the same account showned on services snap-in for SQL server agent and mssqlsever services; if these ones are all localsystem i think is better for you to change the account (from enterprise manager, it's more sure).

    Once you have logged you had to make a connection with pscp and caching the server's key on the registry so it is available to the services.

    andrea

  • Why not go for the simple method... schedule it with Windows Task Scheduler if you can't get it to run through the command shell.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I know this is a very old thread but I have yet to see a solution to this problem if your SQL Server is running as the local system account - so here it is!

    1. Create a text file with the letter Y and a carriage return in it:

    For examle :

    Y

    <blank line>

    2. at the end of pscp command add the following:

    < <name_of_your_text_file_created_in_step_1>

    THATS IT !!

    Basically, the less than character '<' will redirect the contents of the text file to the input of the pscp command, answering the prompt automatically for you.

    Hope this is of some help to somebody sometime.

  • Log into the server either at the consul or through RDC as the user that starts the SQL service

    Create the connection to the SSH server

    putty.exe

    Save the RSA hash to the registry

    Now when the scheduled job runs it will have the hash saved

  • Thats a real good advice by James!!!!

  • Thank you so much for that Tip, James! Had the same problem with psftp, works fine right now!

  • Thanks for the post. Adding that file with the "Y" did the trick. I hate working blind with xp_cmdshell but I guess you deal with it.

  • H James,

    Thank you, we just used your tip (adding Y to a file) and our pscp command works fine from SQL Server now.

    Thanks again,

    Praveena

  • Thanks for your post James, it worked for me too.

    Just to add to it,

    I had to put whole path for the text file, without which it was complaining 'The system cannot find the file specified'. So my command in the BAT file is now:

    D:\psftp.exe -be -pw Password -v -bc -b d:\BatFileWithUNIXCommands.bat LoginName@IPAddress < D:\yes.txt

    James Rizzo (9/6/2007)


    I know this is a very old thread but I have yet to see a solution to this problem if your SQL Server is running as the local system account - so here it is!

    1. Create a text file with the letter Y and a carriage return in it:

    For examle :

    Y

    <blank line>

    2. at the end of pscp command add the following:

    < <name_of_your_text_file_created_in_step_1>

    THATS IT !!

    Basically, the less than character '<' will redirect the contents of the text file to the input of the pscp command, answering the prompt automatically for you.

    Hope this is of some help to somebody sometime.

  • Hi All,

    I am experiencing the same issue in relation to the Servers host key. Unfortunately I have not been able to get James’ fix to work.

    My set up is as follows:-

    SQL Server and SQL Server Agent both run under Local\System

    The code I am trying to run is as follows:-

    DECLARE @sDosCommand VARCHAR(90)

    SET @sDosCommand = 'D:\Putty\pscp -q -batch -scp -C -pw Password -l user UserName@IPAddress:F:/Testfile.txt F:/ < D:\Putty\Important.txt'

    EXEC master..xp_cmdshell

    @sDosCommand

    Note Important.txt has Y and the carriage return lines in it.

    If I run the above form the command line it works perfectly (due to the host key for the logged in User already been generated). However when I run it from within a ISQL session (logged in as the SA account), ISQL hangs as it waits for the relevant response to its Hash prompt (required as it wants to create one based on SQL Server Agents, Service account ie. Local\System)

    As you can see I have tried James’ trick by using the input pipe reference, but to no success.

    Can anyone show me where I am going wrong.

    Many thanks

    Darren

  • What's 'carriage return lines' ?

    I have just 'Y' in mine without anything else in it, which works for me.

    darrenkelly (8/11/2009)


    Hi All,

    I am experiencing the same issue in relation to the Servers host key. Unfortunately I have not been able to get James’ fix to work.

    My set up is as follows:-

    SQL Server and SQL Server Agent both run under Local\System

    The code I am trying to run is as follows:-

    DECLARE @sDosCommand VARCHAR(90)

    SET @sDosCommand = 'D:\Putty\pscp -q -batch -scp -C -pw Password -l user UserName@IPAddress:F:/Testfile.txt F:/ < D:\Putty\Important.txt'

    EXEC master..xp_cmdshell

    @sDosCommand

    Note Important.txt has Y and the carriage return lines in it.

    If I run the above form the command line it works perfectly (due to the host key for the logged in User already been generated). However when I run it from within a ISQL session (logged in as the SA account), ISQL hangs as it waits for the relevant response to its Hash prompt (required as it wants to create one based on SQL Server Agents, Service account ie. Local\System)

    As you can see I have tried James’ trick by using the input pipe reference, but to no success.

    Can anyone show me where I am going wrong.

    Many thanks

    Darren

  • Hi umertahir

    Wow that was a prompt reply 🙂

    In James post he said to add two lines to the file

    Line 1: Y

    Line 2: carriage return

    Thats what I have in my file, however as noted by you, you only have Y, so I tried only having that in my file, but unfortuately it didn't overcome my issue.

    I am begining to think now, the problem may relate to limitations with xp_cmdshell, but still throwing different permatations at it

    Darren

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply