SQL Job failing because of Job owner

  • I have several jobs that have my ad account as job owner that fails because Could not obtain information about Windows NT group/user 'EBE\HBUCKNER', error code 0xffff0002.

    After running profiler, I find that sql is calling EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'EBE\HBUCKNER'

    and fails with this message:

    Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62

    Could not obtain information about Windows NT group/user 'EBE\HBUCKNER', error code 0xffff0002.

    This server instance uses SQL_Latin1_General_CP1_CS_AS collation so I thought it may be due to the case so I try:

    EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'EBE\HBUCKNER'

    EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'ebe\hbuckner'

    EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'EBE\hbuckner'

    The last one works

    has_server_access is_sysadmin actual_login_name

    ----------------- ----------- --------------------------------------------------------------------------------------------------------------------------------

    1 1 EBE\hbuckner

    Any idea what might be going on and how to fix. Right now I have to change all my job owner to SQL logins.

  • SQL installation in your case is case sensitive one. Change job owner from N'EBE\HBUCKNER' to N'EBE\hbuckner' and then try executing the job again.

    MJ

  • When I change the job owner to lower case it automaticlly changes it back to upper case. This is due to my login all uppercase which is also brought in from AD.

    Any other ideas...keep them comming.

  • Okie. Try this leave login name in uppercase(as configured under AD) and change username(currently it would be all uppercase) to have letters in lowercase.

    Hope this helps..

    MJ

  • When I try to change user to lower case it errors and states invalid characters. Must be because of the case sensitive.

    Keep the ideas comming.....

  • Instead of changing the user you may have to drop the user then add the user again being careful to use the proper case.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • No matter what I type to add the login, it has to be uppercase. If I try creating it with lower case, it does not find a AD user and the create login fails. If I type lower case ebe\hbuckner and click the check user button it just make it upper case EBE\HBUCKNER.

    Funny how it has to create the login in uppercase but the job does not authenecate the same user.

    This did work for a while with the job owner in uppercase for about 30 days when I created the job untill I moved this clustered instance to the other node and then back to the original node. Then this is when the jobs owner starts failing. This has done it on every job I created on this instance and this is the only case sensitive collation instance that is on this cluster.

    Any other ideas.....Keep them coming.

  • I guess I don't see why it has to be uppercase in the SQL Agent Job Owner field. I've typed them in lower-case in that field without difficulty.

    I think you're going to have to go with mixed-mode authentication and create an SQL login specifically for running jobs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Could you make sa the owner? The job would then run using the system account.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (8/19/2008)


    Could you make sa the owner? The job would then run using the system account.

    Yes but it does not fix the problem, just hides it.

  • Hi,

    I had this problem on a case sensitive SS2K. I asked the network guys to change the AD login to the case SQL Server insisted on then all was OK. Hope that helps you,

    Cath

  • For more clarification.....

    If I try to create a login right now, there is no way to create it except by using uppercase as shown below.

    USE [master]

    GO

    CREATE LOGIN [EBEbird] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

    GO

    Msg 15401, Level 16, State 1, Line 1

    Windows NT user or group 'EBEbird' not found. Check the name again.

    USE [master]

    GO

    CREATE LOGIN [ebebird] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

    GO

    Msg 15401, Level 16, State 1, Line 1

    Windows NT user or group 'ebebird' not found. Check the name again.

    USE [master]

    GO

    CREATE LOGIN [EBE\NBIRD] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

    GO

    Command(s) completed successfully.

    However if I create a new job "right now" it auto populates the owner as EBE\hbuckner

    overtime........the Job owner auto populate returns back to EBE\HBUCKNER...Then the job breaks and I have to change it to a SQL User to run the jobs.

    This seems to me that there are a couple of domain controllers that it looks at. One must be in upper case and one in lower case. But how do I prove what may be going on so that I can get logings synced and don't keep running into this problem.

    Thanks and keep the ideas coming.

  • keep the ideas coming!!

  • Can you provide sqlserver version info (including buildnumber / sp / CU )

    With sql2000 there is een issue when your sqlserver service account is not member of the local admins group and you use windows accounts for job owner, producing the same errormessage.

    That's one of the reasons I don't like having windows accounts for jobowner.

    Also keep in mind sqlserver saves the username in its own table and only fetches it from windows at "grant login" time.

    If the AD administrators change the username afterward, that info is not updated in the sqlserver table. (-> rename account is a bad idea for sqlserver)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • version product_level edition

    -------------------- -------------------- ------------------------------

    9.00.3175.00 SP2 Standard Edition (64-bit)

    I have 6 SQL instances on this cluster all setup the same way. This is the only instance that is Case Sensitive and the only one that has the proplem. Looks like the xp_logininfo has some code that makes the hbuckner lower case.

    The only way I can make it work correctly is going into AD and change the Pre-Windows account to lowercase hbuckner. Leave the EBE in upper case and then everything works like it suppose to.

    Might be time for a Microsoft Support call.

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

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