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

Question

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

Robert-157332

SSC Veteran

Points: 265
More actions
May 22, 2006 at 9:38 pm

#94243

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

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

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

Andrea Dal Farra Mr or Mrs. 500 Points: 590 More actions · Answer 1

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

Robert-157332 SSC Veteran Points: 265 More actions · Answer 2

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

Andrea Dal Farra Mr or Mrs. 500 Points: 590 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

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)

James Rizzo SSC Rookie Points: 37 More actions · Answer 5

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

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.

thor Old Hand Points: 348 More actions · Answer 6

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

kiran.anumalachetty SSC Journeyman Points: 97 More actions · Answer 7

kiran.anumalachetty

SSC Journeyman

Points: 97

July 25, 2008 at 9:21 am

#848480

Thats a real good advice by James!!!!

dumistvieh Valued Member Points: 51 More actions · Answer 8

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

srmc-646498 SSC Rookie Points: 41 More actions · Answer 9

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.

Praveena C SSC Journeyman Points: 90 More actions · Answer 10

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

DaPainKiller SSChasing Mays Points: 621 More actions · Answer 11

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.

darrenkelly SSCommitted Points: 1624 More actions · Answer 12

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

DaPainKiller SSChasing Mays Points: 621 More actions · Answer 13

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

darrenkelly SSCommitted Points: 1624 More actions · Answer 14

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