Post reply

Error in Authentication while running the command in Jobs

  • April 2, 2008 at 5:56 am

    #184673

    Select lSysKey from "USUT-DB02".master.dbo.MaintananceStatus where sJulianDate = (Select max(sJulianDate) from "USUT-DB02".master.dbo.MaintananceStatus ) AND sMessage = ' ' AND lSysKey = '446'

    while i'm running the above command in TSQL (ie.,) Query Windows i got the value. I got the error, if i make job and trying to run the same query.

    Error Message :

    Date4/2/2008 5:45:08 AM

    LogJob History (AutoRe)

    Step ID1

    ServerUSUT-DB03

    Job NameAutoRe

    Step NameStep3-restore

    Duration00:00:00

    Sql Severity15

    Sql Message ID102

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near 'USUT-DB02'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'USUT-DB02'. [SQLSTATE 42000] (Error 102). The step failed.

    I'm unable to fix it. any one help me out to resolve the same. thanks

    Saravanakumar.R

  • April 2, 2008 at 6:16 am

    #797132

    Try using brackets ([]) instead of inverted commas ("") to enclose your server name.

    John

  • April 2, 2008 at 6:34 am

    #797148

    I got the same error message while i am running the command in job even though i replaced the square bracket instead of "".

    if any authentication problem ???

    thanks

    Saravanakumar.R

  • April 2, 2008 at 6:40 am

    #797156

    I think I see what's going on. You need to make sure that NT AUTHORITY\SYSTEM has permissions on the remote server. Either that or make a different login the owner of the job. I'm still a bit puzzled about why it says "Incorrect syntax", though.

    John

  • April 2, 2008 at 6:50 am

    #797163

    Thanks for your response john,

    i had a doubt,

    if there is a problem on authentication then i could not able to run in query window ? but the case is different.

    the same query when i execute in Query Window, the query is executed and return the value.

    Why it is not executed in job ?

    The same error has raised in SQLServerCentral

    http://www.sqlservercentral.com/Forums/Topic276279-8-1.aspx#bm276301

    but i couldn't find answer....

    thanks & regards

    Saravanakumar.R

  • April 2, 2008 at 6:52 am

    #797166

    change the sqlserveragent service login to a windows login which has access to the target. the LOCALSYSTEM account cannot access a different machine. you probably are using a windows auth login which has access to the target server through QA, thats why you get eresults in QA

  • April 2, 2008 at 7:13 am

    #797178

    As per john suggession,

    i have changed

    Select lSysKey from [USUT-DB02].master.dbo.MaintananceStatus where sJulianDate = (Select max(sJulianDate) from [USUT-DB02].master.dbo.MaintananceStatus ) AND sMessage = ' ' AND lSysKey = '446'

    now i got the error message of :

    Message

    Executed as user: NT AUTHORITY\NETWORK SERVICE. Could not connect to linked server 'USUT-DB02' (OLE DB Provider 'SQLNCLI'). Enable delegation or use a remote SQL Server login for the current user. [SQLSTATE 42000] (Error 7413). The step failed.

  • April 2, 2008 at 7:24 am

    #797201

    I'm searching in net and found the same scenario and one of the member has replied that the below quote.

    http://www.developersdex.com/sql/message.asp?p=3786&ID=%3C501fdf8b-2d6d-4304-80bb-6b97feb8dfb7%40s13g2000prd.googlegroups.com%3E

    Quote :

    I remember with SQL Server 2000 that I had trouble going through a linked

    server with a job that ran as a specific user. This (IF I remember

    correctly) is because the job actually starts as the server account and then

    is switched using SETUSER like functions (found by using Profiler) which

    make the security context invalid for a linked server.

    Does this happen with 2005? I do not know because I have not tried it.

    Another possibility is to use a CREDENTIAL and a SQL AGENT PROXY to get a

    new security context to run the job step in question. If it is a TSQL step

    this will not work, but you can change the step to Operating System and run

    OSQL or SQLCMD with the query and it will run as the underlying account set

    by the proxy.

  • April 2, 2008 at 7:25 am

    #797203

    if any other way to run the job in TSQL? help me to resolve..

    thanks

    Saravanakumar.R

  • April 2, 2008 at 8:12 am

    #797248

    any hope on these ?

  • April 3, 2008 at 1:02 am

    #797639

    Again i got the same error if i have changed the sqlserver agent server as a windows authentication. i couldn;t find the result.

    i have seen the HOTFIX and it was not raised before.

    thanks

    Saravanakumar.R

  • May 12, 2009 at 7:54 pm

    #993384

    You have to change the windows account that the SQL Server Agent runs on. Check the following link on how to do this: http://support.microsoft.com/kb/283811

Viewing 12 posts - 1 through 11 (of 11 total)

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