xp_logininfo fails for agent account

  • Simon Larsen

    SSC Eights!

    Points: 873

    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

    SSC Guru

    Points: 260824

    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

    SSC Eights!

    Points: 873

    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

    SSC Veteran

    Points: 242

    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

    SSC Eights!

    Points: 873

    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

  • Ken.L.Wolff

    SSC Veteran

    Points: 242

    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

    SSC Enthusiast

    Points: 159

    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

    SSCoach

    Points: 15785

    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

    SSC Veteran

    Points: 213

    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 9 (of 9 total)

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