SQL 无法获取有关 Windows NT 组用户的信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1234570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Could not obtain information about Windows NT group user
提问by Raj More
I am creating a SQL Server Replication using a script. When I try to execute
我正在使用脚本创建 SQL Server 复制。当我尝试执行
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)).
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?
我该如何调试？
采纳答案by Remus Rusanu
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 代理。代理应在 STAR 域控制器识别的帐户下运行。
回答by Craig Celeste
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.
如果计算机名称从 OLD 更改为 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.
在这种情况下，我认为这是正确的。我们向 VM 克隆/部署过程添加了一个脚本，用于重新创建 SQL 登录。
回答by Derreck Dean
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 下运行并且失败并显示错误消息"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)).为了解决这个问题，我将每个失败作业的所有者更改为sa. 在那之后工作完美无缺。这些作业与复制清理有关，但我不确定它们是手动添加的还是作为复制设置的一部分添加的 - 我没有参与其中，所以我不确定。
回答by Bacon Bits
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.
就我而言，我在尝试IS_ROLEMEMBER()在 SQL Server 2008 R2 上使用该函数时遇到此错误。此函数在 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.
明显更冗长，但它完成了工作。
回答by Rail
Just solved this problem. In my case it was domain controller is not accessible, because both dns servers was google dns.
刚刚解决了这个问题。在我的情况下，域控制器无法访问，因为两个 dns 服务器都是 google dns。
I just add to checklist for this problem:
我只是添加到此问题的清单中：
- check domain controller is accessible
- 检查域控制器是否可访问
回答by mniles
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）。
回答by Pete
I had to connect to VPN for the publish script to successfully deploy to the DB.
我必须连接到 VPN 才能将发布脚本成功部署到数据库。
回答by Michael Ross
In our case, the Windows service account that SQL Server and SQL Agent were running under were locked out in Active Directory.
在我们的例子中，运行 SQL Server 和 SQL 代理的 Windows 服务帐户在 Active Directory 中被锁定。
回答by Ken
I just got this error and it turns out my AD administrator deleted the service account used by EVERY SQL Server instance in the entire company. Thank goodness AD has its own recycle bin.
我刚刚收到此错误，结果我的 AD 管理员删除了整个公司中每个 SQL Server 实例使用的服务帐户。谢天谢地，AD 有自己的回收站。
See if you can run the Active Directory Users and Computersutility (%SystemRoot%\system32\dsa.msc), and check to make sure the account you are relying on still exists.
查看您是否可以运行Active Directory 用户和计算机实用程序 (%SystemRoot%\system32\dsa.msc)，并检查以确保您所依赖的帐户仍然存在。
