SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_logininfo fails for agent account


xp_logininfo fails for agent account

Author
Message
Simon Larsen
Simon Larsen
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 434
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23581 Visits: 9730
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
Simon Larsen
Simon Larsen
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 434
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.
Ken.L.Wolff
Ken.L.Wolff
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 251
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
Simon Larsen
Simon Larsen
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 434
Yes we did.

I don't remember how Smile 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
Ken.L.Wolff
Ken.L.Wolff
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 251
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
Matt Lavery
Matt Lavery
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 62
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
Tara-1044200
Tara-1044200
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1473 Visits: 3074
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 ?
jxj363
jxj363
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 117
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search