Exec SP failed in the SQL job.
-
Hi All,
Iam trying to execute the procedure through SQL job with the user account (not windows admin or sysadmin)> Just an account to select the data. When I exec the procedure manually its working fine and i can get the data imported.
The same procedure is placed in job (EXEC Procedure), but its failing with the below error message : can any one please help me to fix this ASAP.
Linked Server : SQL 2005 (my machine) connected to SQL 2000 (diff domain)
Message
Executed as user: portuser. Login failed for user 'portuser'. [SQLSTATE 28000] (Error 18456) TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 42000] (Error 10054) OLE DB provider "SQLNCLI" for linked server "REPLDB" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412). The step failed.
Cheers,
- Win." Have a great day "
-
The problem is not in the EXEC method but in the configuration of the liked server. How did you configure the linked server in the "Security" section?
Do you impersonate a login or do you use a certain security context? If you use a user mapping than there might be a problem with the remote user, either your password and/or username are incorrect.
Is the user "portuser" created on the remote server?
-
Can you logon to the linked server and check if you have given the user account(under which the job is running) “Connect” rights to the default database on the linked server.
-
The problem is not in the EXEC method but in the configuration of the liked server. How did you configure the linked server in the "Security" section?
Do you impersonate a login or do you use a certain security context? If you use a user mapping than there might be a problem with the remote user, either your password and/or username are incorrect.
Is the user "portuser" created on the remote server?
-------------------------------------------------------------------------------------------------
Process i used by executing the query to create linked server : I can view the tables and all i can retrieve data too.
EXEC sp_addlinkedserver
@server = 'REPLDB'
, @srvproduct = ''
, @provider= 'SQLOLEDB'
, @datasrc= '10.164.0.2'
GO
sp_addlinkedsrvlogin @rmtsrvname = 'REPLDB'
,@useself = 'FALSE'
,@locallogin = 'SQLDBA'
,@rmtuser = 'portuser'
,@rmtpassword = 'portuser'
GO
No Impersonate applied.
Just created Linked server using the above query and after then tried executing the procedure manually. Its working fine and the data imported successfully.
The same when i tried in SQL job
as owner : portuser.
Yes
"portuser" is created on the linked server. I can connect to the server. execute permissions are given to that account on the server.
Thanks...
Please suggest to fix this as i have wasted doing R&D today the last day to fix this...
Cheers,
- Win." Have a great day "
-
This may fix the issue for the time being. This will authenticate all user regardless their login. See null in local login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'linkedserverName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteuser', @rmtpassword = N'remotepassword' -
Thanks Roshan..!!!!
It worked for me.... either it may be time being but... My JOB got succeeded...
Cheers,
- Win." Have a great day "
-
I said its temporary because,
any user logged in to your server will be able to access the remote server. Just a security concern
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply