Could not obtain information about Windows NT group user

Question

I am creating a SQL Server Replication using a script. When I try to execute

The job failed. Unable to determine if the owner (STAR\moorer7) of job L3BPT2M-Atlas-14 has server access (reason: Could not obtain information about Windows NT group/user 'STAR\moorer7', error code 0x5. [SQLSTATE 42000] (Error 15404)).

This is a job created by a script that defines replication.

How do I debug this?

Solution 1

Active Directory is refusing access to your SQL Agent. The Agent should be running under an account that is recognized by STAR domain controller.

Solution 2

We encountered similar errors in a testing environment on a virtual machine. If the machine name changes due to VM cloning from a template, you can get this error.

If the computer name changed from OLD to NEW.

A job uses this stored procedure:

msdb.dbo.sp_sqlagent_has_server_access @login_name = 'OLD\Administrator'

Which uses this one:

EXECUTE master.dbo.xp_logininfo 'OLD\Administrator'

Which gives this SQL error 15404

select text from sys.messages where message_id = 15404;
Could not obtain information about Windows NT group/user '%ls', error code %#lx.

Which I guess is correct, under the circumstances. We added a script to the VM cloning/deployment process that re-creates the SQL login.

Solution 3

For me, the jobs were running under DOMAIN\Administrator and failing with the error message "The job failed. Unable to determine if the owner (DOMAIN\administrator) of job Agent history clean up: distribution has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\administrator', error code 0x5. [SQLSTATE 42000] (Error 15404)). To fix this, I changed the owner of each failing job to sa. Worked flawlessly after that. The jobs were related to replication cleanup, but I'm unsure if they were manually added or were added as a part of the replication set-up - I wasn't involved with it, so I am not sure.

Solution 4

In my case I was getting this error trying to use the IS_ROLEMEMBER() function on SQL Server 2008 R2. This function isn't valid prior to SQL Server 2012.

Instead of this function I ended up using

select 1 
from sys.database_principals u 
inner join sys.database_role_members ur 
    on u.principal_id = ur.member_principal_id 
inner join sys.database_principals r 
    on ur.role_principal_id = r.principal_id 
where r.name = @role_name 
and u.name = @username

Significantly more verbose, but it gets the job done.

Solution 5

Just solved this problem. In my case it was domain controller is not accessible, because both dns servers was google dns.

I just add to checklist for this problem:

• check domain controller is accessible

View more solutions

Comments

[SeeSharp]

I changed the owner in job properties to 'sa' then the issue solved.

[PowerUser]

Heh. You asked this 7 years ago and it's still helping people like me today.

[Tug Strongly]

Can anybody tell me why when the job or maintenance plan owner is in AD and an Admin on the SQL box does this error still happen? I always have to revert back to the sa account to get anything to run.

[Raj More]

@Remus Rusanu:The agent is running under a local machine account as MyWorkstation\SqlServerAccount

[Remus Rusanu]

@Raj: Actually since is the SQL Server itself that is connecting to the AD, you need to change the account under which SQL is running. It has to be an account AD will authenticate, like a domain account or the NETWORK SERVICE account(provided the machine is joined to the domain). SSMS or EM can have an option to change the service account.

[DancesWithBamboo]

+1 for keeping me from having to talk to my dba (several hours saved)

[NateJ]

Brent Ozar recommends (with caution of course) that SQL Server Agent Jobs be owned by SA, because the owner doesn't really matter to how the job functions, until it fails due to the owner being dropped or non-authenticated. See here: brentozar.com/blitz/jobs-owned-by-user-accounts . A service account is the next best thing, as long as you have it configured right and it's never at risk of being locked-out or disabled.

[jl.]

I have had this SQL Agent error as the result of ephemeral port exhaustion that prevented SQL Server connections from occurring. The SQL Agent error was accompanied by intermittent NETLOGON and Group Policy errors seen in the database server's System Event log.

[ORcoder]

What is a STAR domain controller?

[Mark Berry]

@jl's comment got me to check the System event log, where I found NETLOGON and DNS update errors. Made me wonder if the SQL server's NIC was still pointing to the domain controller for DNS and it was not. Not sure how that changed, but updating the SQL server to get DNS from the domain controller should allow it to validate domain users again. See also Rail's answer stackoverflow.com/a/25117580/550712.

[Mark Berry]

Me too. Had to change DNS to point to the domain controller so it can validate domain users.