I am creating a SQL Server Replication using a script. When I try to execute
我正在使用脚本创建SQL服务器复制。当我尝试执行的时候
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)).
工作的失败。无法确定工作L3BPT2M-Atlas-14的所有者(STAR\moorer7)是否具有服务器访问权限(原因:无法获取关于Windows NT组/用户'STAR\moorer7'的信息，错误代码0x5)。(SQLSTATE 42000)(错误15404))。
This is a job created by a script that defines replication.
这是由定义复制的脚本创建的作业。
How do I debug this?
如何调试?
7 个解决方案
#1
34
Active Directory is refusing access to your SQL Agent. The Agent should be running under an account that is recognized by STAR domain controller.
Active Directory拒绝访问SQL代理。代理应该在星域控制器识别的帐户下运行。
#2
8
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.
对我来说，这些工作都是在DOMAIN\Administrator下运行的，并且错误消息“工作失败了”。无法确定作业代理历史记录的所有者(域\管理员)是否已清理:发行版拥有服务器访问权限(原因:无法获得关于Windows NT组/用户“域\管理员”的信息，错误代码0x5。(SQLSTATE 42000)(错误15404))。为了解决这个问题，我将每个失败作业的所有者改为sa。准确无误地运行。这些作业与复制清理有关，但我不确定它们是手动添加的还是作为复制设置的一部分添加的——我没有参与其中，所以我不确定。
#3
6
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.
我们在虚拟机上的测试环境中遇到了类似的错误。如果由于VM克隆而导致的机器名称更改，您可以得到这个错误。
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
这会导致SQL错误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.
在这种情况下，我想这是对的。我们向重新创建SQL登录的VM克隆/部署过程添加了一个脚本。
#4
3
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.
在我的例子中，我在SQL Server 2008 R2上尝试使用IS_ROLEMEMBER()函数时遇到了这个错误。此函数在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.
非常冗长，但是它完成了工作。
#5
1
Just solved this problem. In my case it was domain controller is not accessible, because both dns servers was google dns.
只有解决了这个问题。在我的例子中，它是域控制器不可访问，因为两个dns服务器都是谷歌dns。
I just add to checklist for this problem:
我只是为这个问题增加了检查清单:
- check domain controller is accessible
- 检查域控制器是可访问的
#6
1
I was having the same issue, which turned out to be caused by the Domain login that runs the SQL service being locked out in AD. The lockout was caused by an unrelated usage of the service account for another purpose with the wrong password.
我也遇到了同样的问题，这是由于运行SQL服务的域登录被锁定在AD中导致的。锁定是由于不相关的服务帐户使用错误的密码而引起的。
The errors received from SQL Agent logs did not mention the service account's name, just the name of the user (job owner) that couldn't be authenticated (since it uses the service account to check with AD).
从SQL代理日志中接收到的错误没有提到服务帐户的名称，只有无法验证的用户(作业所有者)的名称(因为它使用服务帐户来检查AD)。
#7
1
I had to connect to VPN for the publish script to successfully deploy the DB.
我必须连接到VPN才能成功部署DB。
#1
34
Active Directory is refusing access to your SQL Agent. The Agent should be running under an account that is recognized by STAR domain controller.
Active Directory拒绝访问SQL代理。代理应该在星域控制器识别的帐户下运行。
#2
8
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.
对我来说，这些工作都是在DOMAIN\Administrator下运行的，并且错误消息“工作失败了”。无法确定作业代理历史记录的所有者(域\管理员)是否已清理:发行版拥有服务器访问权限(原因:无法获得关于Windows NT组/用户“域\管理员”的信息，错误代码0x5。(SQLSTATE 42000)(错误15404))。为了解决这个问题，我将每个失败作业的所有者改为sa。准确无误地运行。这些作业与复制清理有关，但我不确定它们是手动添加的还是作为复制设置的一部分添加的——我没有参与其中，所以我不确定。
#3
6
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.
我们在虚拟机上的测试环境中遇到了类似的错误。如果由于VM克隆而导致的机器名称更改，您可以得到这个错误。
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
这会导致SQL错误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.
在这种情况下，我想这是对的。我们向重新创建SQL登录的VM克隆/部署过程添加了一个脚本。
#4
3
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.
在我的例子中，我在SQL Server 2008 R2上尝试使用IS_ROLEMEMBER()函数时遇到了这个错误。此函数在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.
非常冗长，但是它完成了工作。
#5
1
Just solved this problem. In my case it was domain controller is not accessible, because both dns servers was google dns.
只有解决了这个问题。在我的例子中，它是域控制器不可访问，因为两个dns服务器都是谷歌dns。
I just add to checklist for this problem:
我只是为这个问题增加了检查清单:
- check domain controller is accessible
- 检查域控制器是可访问的
#6
1
I was having the same issue, which turned out to be caused by the Domain login that runs the SQL service being locked out in AD. The lockout was caused by an unrelated usage of the service account for another purpose with the wrong password.
我也遇到了同样的问题，这是由于运行SQL服务的域登录被锁定在AD中导致的。锁定是由于不相关的服务帐户使用错误的密码而引起的。
The errors received from SQL Agent logs did not mention the service account's name, just the name of the user (job owner) that couldn't be authenticated (since it uses the service account to check with AD).
从SQL代理日志中接收到的错误没有提到服务帐户的名称，只有无法验证的用户(作业所有者)的名称(因为它使用服务帐户来检查AD)。
#7
1
I had to connect to VPN for the publish script to successfully deploy the DB.
我必须连接到VPN才能成功部署DB。