Could not obtain information about Windows NT grou

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?

Answer 1

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.

Answer 2

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

Answer 3

I had to connect to VPN for the publish script to successfully deploy the DB.

Answer 4

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.

Answer 5

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.

Answer 6

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