December 2, 2008 at 10:10 pm
SQL 2005, Windows 2008 clustered
when I run xp_logininfo I get the list of all the accounts.
when I run xp_logininfo 'domain\agentaccount'
I get Could not obtain information about Windows NT group / user error code 0x5
I also find in the server security log that the SQL service account has failed to login.
SQL and the agent are both running fine.
xp_logininfo sql service account
works fine.
The source of the problem was a db_mail which has a query attached. Works fine for my windows account and others but not for the agent.
December 3, 2008 at 9:22 am
Is the agent account part of the domain, or is it a local account on the server?
- 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
December 3, 2008 at 11:25 am
The agent is part of the domain (it is a clustered instance).
The agent gains access via the domain group SQL_Agent.
Note that my account (which works fine) is a member of the DBA domain group (almost identical to the above group).
I have tried putting the agent into the DBA group with no success.
June 30, 2009 at 9:02 am
Simon - were you ever able to resolve this? I'm encountering the same problem and would love to know the solution, if there is one.
Thank you.
- Ken
June 30, 2009 at 2:50 pm
Yes we did.
I don't remember how 🙂 Give me a few hours to chase up the details.
I remember it ended up being a PSS fix which was nice to have them help.
Oh I remembered the PSS persons name and found the steps she recommended:
I suggest that you try to implement the below changes in AD:
1. Add the SQL service account (SVCNS02IS0V001SQL) into the Windows Authorization Access group
To add the SQL service account into the Windows Authorization Access group, do as follows:
- Open ADUC (Active Directory Users and Computers) console on a domain controller which hosts the user account - SVCNS02IS0V001AGT.
- Go to the Builtin container. Find Windows Authorization Access Group
- Open its properties. Under the Members tab, add the SQL service account into the list.
- Apply the changes.
- Restart the SQL service to re-logon the SQL service account.
- Check if the issue persists.
2. Also, confirm if the SVCNS02IS0V001SQL service account has at least Read permission on the user account object (SVCNS02IS0V001AGT) for this attribute:
Read tokenGroupsGlobalAndUniversal
June 30, 2009 at 8:25 pm
Awesome. Thanks so much, Simon. Really appreciate the information and the quick response. I'll get my my AD admin and give it a try...
Thanks again...
- Ken
August 20, 2009 at 9:05 pm
Just something I have found in my own situation.
This is more likely to be caused by changes in Windows 2008 rather than any changes to SQL server.
My scenario is documented in my blog
http://matticus-au.blogspot.com/2009/08/windows-2008-and-xplogininfo.html
Hope this helps someone else who comes across similar issues in the future.
Matt
February 18, 2010 at 9:14 am
I did not get all users listed in the active directory group by runing this..
EXEC xp_logininfo 'State_CO\Programmers' ,'members'
I still miss some users in the result though i see them in AD. any reason ?
March 25, 2014 at 10:34 am
don't know if this is absolutely true, but had been told that it will list info only for current logins. If you are able to get info. for any login currently used and not for others, that might confirm.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply