Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

xp_logininfo fails for agent account Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 10:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:51 PM
Points: 59, Visits: 275
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.
Post #612567
Posted Wednesday, December 3, 2008 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #612955
Posted Wednesday, December 3, 2008 11:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:51 PM
Points: 59, Visits: 275
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.
Post #613052
Posted Tuesday, June 30, 2009 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:05 PM
Points: 5, Visits: 223
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
Post #744497
Posted Tuesday, June 30, 2009 2:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:51 PM
Points: 59, Visits: 275
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

Post #744882
Posted Tuesday, June 30, 2009 8:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:05 PM
Points: 5, Visits: 223
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
Post #745027
Posted Thursday, August 20, 2009 9:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:33 PM
Points: 3, Visits: 61
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
Post #774748
Posted Thursday, February 18, 2010 9:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:36 AM
Points: 1,001, Visits: 2,982
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 ?
Post #868129
Posted Tuesday, March 25, 2014 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 2:59 PM
Points: 1, Visits: 38
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.
Post #1554583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse