Se connecter
 
AccueilLibrairieFormationTéléchargementsSupport techniqueCommunautésForums
Pour les professionnels de l’informatique >
> 
> 
Changing Service Account causes xp_logininfo to error

Traitée Changing Service Account causes xp_logininfo to error

  • jeudi 3 mai 2012 12:43
     
     
    Connectez-vous pour voter
    0
      I have two domain accounts used as SQL service accounts, Domain\dev and Domain\prod. When I execute sp_Send_DBMail with the @query parameter as Domain\prod, I receive the error below, but I don't receive the error under the Domain\dev account. The accounts have the same local and domain privileges.  The two service accounts are on the same domain. The user I am signed in as is on another domain with a one way trust, and this user is a sysadmin.

    Error: Could not obtain information about Windows NT group/user 'Domain\User', error code 0x5.

    Through researching this issue, I have tried adding the prod user to the following groups with no success, Windows Authorization Access Group, Pre-Windows 2000 Compatible Access, and the Local Admins group. The issue seems to point towards a problem with executing the SP, xp_logininfo 'Domain\User'. I confirmed I get a very similar error when I run this command as Domain\prod. Once again, if the service is running under Domain\dev, the SP executes successfully.

    I also made a copy of the dev user to test with, but the copied user heeds the same errors as prod.

    I am not sure what is causing the user to need this access, as this user is running our production environment with no problems (except for this one).
     

Toutes les réponses

  • vendredi 4 mai 2012 02:48
    Modérateur
     
     
    Connectez-vous pour voter
    0

    Hi Joe.Sudon,

    >>Could not obtain information about Windows NT group/user 'DOMAIN\useradmin', error code 0x5"

    Regarding to the error message you provided, which is means "Access Denied". In this case, it means the "'DOMAIN\useradmin" does not permissions to access the database.

    Based on my research, there are some of the issues that may cause the error.
    •Network issues.
    •An issue with the name of a Windows Local Group or a Windows Global Group.
    •A conflict with the EVERYONE group.
    • Domain account cannot be verified by the activity directory service (ADS), please double check whether the ADS is properly running

    To solve the issue, please follow these steps:
    1.  Change the Windows Local Group name or the Windows Global Group name.
    Remove the user from the Windows Local Group or the Windows Global Group that has the same name as the Windows domain.
    2.Change the name of "useradmin" in the domain.
    Alternatively you can try a work around, try a local Windows account or SQL login account who is at least db_owner.

    For more information, please see:
    How to troubleshoot a SQL Server 8198 error: http://support.microsoft.com/kb/834124

    Regards, Amber zhang

     
  • vendredi 4 mai 2012 13:57
     
     
    Connectez-vous pour voter
    0

    It is odd that I have come across this issue in my test environment with our SQL 2008 R2 servers, running specifically Window Server 2008 R2. Any job I create that uses a domain account cannot run and returns the same error you are receiving.

    I am checking with my AD admins to see if they can tell me what changed, cause it worked fine as of yesterday. I did come across this link that the guy was able to reproduce it: click here

    Shawn Melton

     
  • mardi 8 mai 2012 15:06
     
     
    Connectez-vous pour voter
    0
    The user "DOMAIN\useradmin" has access to the database.  It is definitely a problem with the user that is running the SQL Service.  If I change the user that is running the SQL service, I can run the query using DOMAIN\useradmin without issue.  I just can't find the difference between the two users that we use to run the SQL service.  Both users are under only one group in the domain, and that is the users group (no special permissions).  Is there somewhere else I should/could be looking to see permission differences between the two service accounts?
     
  • mardi 8 mai 2012 15:08
     
     
    Connectez-vous pour voter
    0
    I ran across this link while trying to figure out the issue.  Adding the user to the "BUILTIN\Windows Authorization Access Group", unfortunately does not fix the problem.  Neither does adding it to any other domain group.
     
  • mardi 8 mai 2012 16:33
     
     Traitée
    Connectez-vous pour voter
    0

    I figured it out.  There is a legacy dev user on the untrusted domain with the same un and pw.  I created the prod user on the untrusted domain with the same un and pw, and everything works.  The one thing I am troubled with is the fact that the query only fails if I attach a query.  Below is the query that fails.  If I remove the @query parameter, the SP works.  The query I tested with is just "Select 1".  What is causing the SP to try to contact the other domain when this is run in a job owned by the sa user?

        EXEC msdb.dbo.sp_Send_DBMail
            @profile_name = 'Mail' ,
            @recipients = 'email@address.com' ,
            @subject = 'Test' ,
            @query = 'Select 1' ,
            @body = 'Test'

     
  • mardi 8 mai 2012 21:35
     
     Traitée
    Connectez-vous pour voter
    0

    I looked in the code for sp_send_dbmail, and I arrived to a call to master..xp_sysmail_format_query which seems to be the one that runs the query. I don't have DB mail set up, so I cannot test, but my guess is that the XP makes a new connection to SQL Server and that's where you get the problem.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se