March 3, 2009 at 9:55 pm
I have a problem when i execute my job.
This job contains stored procedure (USP_CalculateDataCapture) which is running without any problem when i run it in management studio.
EXEC USP_CalculateDataCapture @DCId = 2
but the same, when i run it through job which is not working. it gives the below error message. This is not SSIS.
NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.
i am using sqlserver authendication to connect the server by giving userid and pwd.
i have many jobs which are all running without any problem, even the same user i have assigned it in another job which is running perfectly. Now I have deleted the job and recreated and assigned sa user too. but still the same error!!!
This SP which i am using in job is creating many linkedserver by reading of another table which contained location and it's ip number.
primary stored procedure :
.
.
.
EXEC @WMS_Error = USP_CreateLinkedServer @ServerIpAddress = @ServerIpAddress, @SQLLogin = @SQLLogin, @SQLPassword = @SQLPassword
SET @TempName = 'Job_Usp_' + @DCName
.
.
.
Calling Stored Procedure : (USP_CreateLinkedServer)
.
.
IF ( NOT EXISTS ( SELECT * FROM master.dbo.sysservers WHERE srvname = @ServerIPAddress ) )
BEGIN
EXEC @WMS_Error = sp_addlinkedserver @server = @ServerIPAddress, @srvproduct = 'SQL Server'
IF ( @WMS_Error = 0 )
BEGIN
EXEC @WMS_Error = sp_addlinkedsrvlogin @rmtsrvname = @ServerIPAddress ,@useself = 'false' ,@rmtuser = @SQLLogin ,@rmtpassword = @SQLpassword
END
.
.
can anyone tell what s wrong in this...
thanks
kmr
March 3, 2009 at 10:44 pm
Hello,
I would guess the issue is related to permissions of the Account under which the SQL Server Agent Service runs.
One way to test this theory out is to temporarily change the SQL Server Agent Service to run under the same account that you used when you successfully executed the SP.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply