SQL Server 2008 R2 中缺少安全主体 [英] Security Principals missing in SQL Server 2008 R2

问题描述

Windows Server 2012 R2 上的 SQL Server 2008 R2，在尝试查看用户帐户和分配权限时，没有任何内置安全主体出现.

SQL Server 2008 R2 on Windows Server 2012 R2, none of the built in security principals are appearing when trying to view user accounts and assigning permissions.

它们可在服务上用于将 LOGON 分配为值.具体来说，我正在寻找 NETWORK SERVICE.

They are available on the service to assign the LOGON as value. Specifically I am looking for NETWORK SERVICE.

澄清:我指的是本地帐户 IUSER、NETWORK SERVICE、LOCAL SYSTEM 等.

FOR CLARIFICATION: I am referring to the local accounts IUSER, NETWORK SERVICE, LOCAL SYSTEM, etc.

我刚刚在演示服务器上完成了并行安装，两个 2012 R2 机器都没有问题.有问题的是虚拟机.

I just completed a parallel installation on a demo server with no issues both 2012 R2 boxes. The one with issue is a VM.

为了确认，帐户确实存在于服务器上.所以它们是在操作系统安装期间创建的.

And just to confirm, the accounts DO IN FACT exist on the server. So they were created during the OS install.

我卸载了重新安装的 SQL Server 和 SSMS，两者都备份并运行，同样的问题.

I uninstalled an reinstalled both SQL Server and SSMS, both back up and running, same issue.

关于如何取回这些有什么想法吗?

Any ideas on how to get those back?

推荐答案

IUSR 不是默认主体.这是 IIS 7 中用于匿名访问的 IIS 用户帐户.SQL Server 不假定 IIS 需要访问实例，更不用说 IIS 匿名用户需要访问权限了.从 IIS 7.5 开始，它被认为是旧帐户 IIRC，默认情况下甚至不会创建.如果您有一个实际名为IUSER"的帐户，那么我想这是一个特定于您的环境的帐户.

IUSR isn't a default principal. That's an IIS user account in IIS 7 for anonymous access. SQL Server doesn't assume IIS needs access to the instance, let alone that IIS anonymous users need access. Beginning with IIS 7.5, that was considered a legacy account, IIRC, and isn't even created by default. If you have an account actually named "IUSER", then I imagine that's an account specific to your environment.

您可能看不到 NETWORK SERVICE 或 LOCAL SERVICE，因为这些帐户是在 Windows Server 2008 R2 及更高版本中虚拟化的.SQL Server 2012 doc 做到了这一点更清楚(向下滚动到Windows 7 和 Windows Server 2008 R2 可用的新帐户类型"部分).编写 SQL Server 2008 R2 文档时可能不会完全考虑对 Windows Server 2008 R2 的更改.我似乎无法找到有关 SQL Server 2008 R2 在这些系统上的作用的明确文档，但至少在我的安装中(安装在 2008 R2 SP1 上的 SQL 2008 R2 SP1 媒体，稍后更新)这些旧帐户不存在虚拟帐户一直存在.

You may not see NETWORK SERVICE or LOCAL SERVICE since those accounts were virtualized in Windows Server 2008 R2 and later. SQL Server 2012 doc makes this more clear (scroll down to the bit about "New Account Types Available with Windows 7 and Windows Server 2008 R2"). SQL Server 2008 R2 doc likely wouldn't have been written with changes to Windows Server 2008 R2 wholly in mind. I can't seem to find clear documentation about what SQL Server 2008 R2 does on these systems, but on my installations at least (SQL 2008 R2 SP1 media installed on 2008 R2 SP1 installations, updated later) these older accounts aren't present at all while the virtual accounts are.

您应该看到 NT AUTHORITY\SYSTEM、NT SERVICE\MSSQLSERVER(或 NT SERVICE\MSSQL$InstanceName)组和 NT SERVICE\SQLSERVERAGENT(或 NT SERVICE\SQLAgent$InstanceName)组.最后两个是虚拟帐户，它们是对用于启动在 SQL Server 配置管理器中配置的服务的服务帐户的引用.您还应该看到 ##MS_PolicyTsqlExecutionLogin## 和 ##MS_PolicyEventProcessingLogin##，但它们可能已被禁用.

You should see NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER (or NT SERVICE\MSSQL$InstanceName) group, and NT SERVICE\SQLSERVERAGENT (or NT SERVICE\SQLAgent$InstanceName) group. Those last two are the virtual accounts that are references to the service accounts used to start those services configured in SQL Server Configuration Manager. You should also see ##MS_PolicyTsqlExecutionLogin## and ##MS_PolicyEventProcessingLogin##, but they're probably disabled.

如果您包含 SQL 身份验证，您也应该看到 sa 帐户.您还应该看到您在安装期间明确添加的任何帐户.从 SQL Server 2008 开始，我认为这是默认设置.如果您升级现有实例，您可能会看到不同的内容，否则我希望这些帐户是在其他服务器上安装期间或之后显式添加的.

If you included SQL authentication, you should see the sa account, too. You should also see whatever accounts you added explicitly during the install. Beginning in SQL Server 2008, I think that's it for defaults. If you upgraded an existing instance, you may see different things, otherwise I would expect those accounts were explicitly added during or after the install on the other server.

最重要的是，您需要创建缺少的服务器自己登录和关联的数据库用户，但您可能无法创建网络服务或本地服务，因为它们甚至可能不存在.

Bottom line, you need to create missing server logins and associated database users yourself, but you may not be able to create NETWORK SERVICE or LOCAL SERVICE since they may not even exist.

这篇关于SQL Server 2008 R2 中缺少安全主体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！