August 19, 2008 at 12:35 pm
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.
August 19, 2008 at 12:49 pm
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
August 19, 2008 at 1:07 pm
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.
August 19, 2008 at 1:18 pm
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
August 19, 2008 at 1:25 pm
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.....
August 19, 2008 at 1:43 pm
Instead of changing the user you may have to drop the user then add the user again being careful to use the proper case.
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]
August 19, 2008 at 7:23 pm
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.
August 19, 2008 at 8:13 pm
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
August 19, 2008 at 8:22 pm
Could you make sa the owner? The job would then run using the system account.
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]
August 20, 2008 at 3:27 am
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.
August 20, 2008 at 4:09 am
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
August 20, 2008 at 6:06 am
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.
August 20, 2008 at 1:09 pm
keep the ideas coming!!
August 21, 2008 at 4:35 am
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
August 21, 2008 at 6:29 am
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