April 25, 2006 at 5:18 am
Hello,
I have written a SP which includes a RPC. The SP works fine when called using "Query Analyzer" and logged in as "sa". When I do the same call though a job created by "SQL Server Agent" with the owner set to "sa", I get the follow error:
(Message 0) Could not connect to server 'REMOTESERVER' because 'XXXXX' is not defined as a remote login at the server. [SQLSTATE 42000] (Error 18483). The step failed.
'XXXXX' is the startup service account for my local machine and is a windows account rather than a MSSQL. I have tried to add it as a remote login but the server does not recognise it as a local user.
Any help would be much appreciated.
April 27, 2006 at 12:01 pm
RPC(remote procedure call) I don't know why you need it in a stored proc is outside your network so you need XP_CMDSHELL with SQL Server Agent installed with service account running with Admin permissions. You are getting the error because all and I mean all automation in SQL Server require SQL Server Agent with Admin permissions so it can access network resources. Microsoft Knows about it for a long time at least 7 years and it is in MSDN but I don't think it is in the BOL (books online). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
April 28, 2006 at 3:05 am
Thank you for your reply Gift.
My stored procedure has three actions:
1. Dump database to local drive.
2. Copy database to remote server using xp_cmdshell.
3. Load dump onto database on remote server using rpc call using load dump sp.
My sp works fine when called using "Query Analyzer". I have declared "sa" as a remote login on my remote server and rpc calls work.
When I do the same call from a job created using "SQL Server Agent" it fails. The error message implies to me that the job is being executed as the SQL server startup account and not as "sa" even though I have selected it as the owner of the job.
I would like to know how I can execute my job as "sa", the defined remote login. I am also happy to listen if there is another way I should be doing this.
Thanks once again for any help.
Sam
April 28, 2006 at 6:07 am
There is install SQL Server Agent with a service account with Admin permissions because when XP_CMDSHELL is running in the context of SQL Server Agent the Agent needs the same permissions as you. There is an alternative you can create a proxy account for the Agent. The links below covers the how, I have helped a lot of people with this for DTS so if it did not work post again because you may be doing something wrong. And when you get it to work the Agent will do at least 25 percent of what you do now manually. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply