设置订阅查询通知时出现问题

发布于 2024-10-31 22:47:15 字数 405 浏览 4 评论 0原文

我目前正在为我们的网站实现缓存机制。我想使用 SQL 缓存依赖功能。我正在管理工作室中运行以下命令，但它不起作用。

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "my_server_name\ASPNET"

我收到的错误是：

Cannot find the user 'my_server_name\ASPNET', because it does not exist or you do not have permission.

我尝试使用特定数据库的管理员登录，我正在为计算机管理员设置通知、sa 和 Windows 身份验证。还尝试以管理员身份运行管理工作室，但仍然不高兴。有人可以指出我正确的方向吗？谢谢你！

原文

I'm currently implementing a cache mechanisem for our site.
I want to use the SQL Cache dependancy feature.
I am running the following command in management studio and it's not working.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "my_server_name\ASPNET"

The error I'm getting is:

Cannot find the user 'my_server_name\ASPNET', because it does not exist or you do not have permission.

I tried signing in with the admin of the specific database I'm setting the notification for, sa, and windows authentication with the machine administrator.
Also tried running management studio as administrator and still not joy.
Can someone please point me in the right direction.
Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柠檬 2024-11-07 22:47:15

首先，您似乎正在尝试向运行该网站的帐户授予权限。在 IIS 6 和 IIS7 中，这些由应用程序池上设置的帐户控制。该帐户曾经是ASPNET，但不再是默认帐户。相反，默认值（我相信从 .NET 2.0 开始）是 NETWORK SERVICE。但是，如果您使用 IIS 7，情况又发生了变化。默认情况下，在 IIS7 中，它使用称为“ApplicationPoolIdentity”的东西，这是为每个站点创建的自己的特殊凭据。如果 SQL Server 与 Web 服务器位于不同的计算机上，您将遇到另一个问题，即凭据都是计算机本地的。

我的建议是根据您的设置执行以下操作：

两台服务器都位于域中，并且您想要使用可信连接：

创建一个域帐户并将其放入域用户中。
在 Web 服务器上，将此帐户放入 IIS_IUSRS 组中。
进入该站点的应用程序池，并将运行该站点的帐户更改为该域帐户。您还需要确保该帐户对站点文件具有适当的 NTFS 权限。如果该站点仅写入数据库，您可以为该帐户授予包含站点文件的文件夹的只读访问权限。
确保站点使用的连接字符串已形成以请求可信连接。（有关语法，请参阅 www.connectionstrings.com）
在数据库服务器上执行对此帐户的授权：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "domain name\MyIISAccount"

还可能存在与以下内容相关的其他 Kerberos 问题：事实上，域中的两台服务器都可能需要创建 SPN（服务主体名称）。

两台服务器都不在域中（即，两台服务器都是成员服务器），并且您想要使用可信连接：使用

相同的用户名和密码在 Web 服务器和数据库服务器上创建本地帐户。至关重要的是，他们都具有相同的用户名和密码。此技术涉及使用 NTLM“直通”身份验证，该身份验证与用户名和密码创建的散列相匹配，以确定用户是否在两个不同的服务器之间经过身份验证。在 Windows 2008 R2 上，您可能必须跳过一些本地策略环节才能确保在两台服务器之间启用 NTLM。
使用此帐户执行上述步骤#2 到#4。
在 SQL Server 上，确保此本地帐户具有登录名，并且此登录名映射到数据库中的用户。然后您将执行如下操作：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\AccountUsedBySite'

您希望使用 SQL 帐户而不是受信任的连接：

在这种情况下，站点使用的连接字符串来连接数据库将包含用户名和密码，它们映射到 SQL Server 数据库上的登录名，而 SQL Server 数据库上的登录名映射到数据库中的用户（通常放入 db_owner 角色以使其成为 dbo）。假设
凭据正确，您只需对该用户执行授予：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLUserAccountUsedBySite'

IIS 和 SQL Server 都在同一台计算机上，并且您希望使用可信连接

创建本地用户帐户并将其放入用户组中。
将此帐户放入本地 IIS_IUSRS 组。
进入该站点的应用程序池，并将运行该站点的帐户更改为该本地帐户。您还需要确保该帐户对站点文件具有适当的 NTFS 权限。如果该站点仅写入数据库，您可以为该帐户授予包含站点文件的文件夹的只读访问权限。
确保站点使用的连接字符串已形成以请求可信连接。（有关语法，请参阅 www.connectionstrings.com）
在 SQL Server 中，为此帐户创建一个登录名，然后在相应的数据库中为此帐户创建一个用户，并将其分配到适当的角色。
现在执行对此帐户的授权：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\MyIISAccount'

First, it appears you are attempting to grant permissions to the account under which the site is running. In IIS 6 and IIS7 these are control by the account set on the Application Pool. That account used to be ASPNET but no longer by default. Instead, the default (starting with .NET 2.0 I believe) is NETWORK SERVICE. However, if you are using IIS 7, that has changed yet again. By default in IIS7 it uses something called the "ApplicationPoolIdentity" which is its own special credential created for each site. If SQL Server is on a different machine than the web server, you will run into another problem which is the credentials are all local to the machine.

My recommendation would be to do the following depending on your setup:

Both servers are on a domain and you want to use trusted connections:

Create a domain account and drop it into Domain Users.
On the web server, drop this account into the IIS_IUSRS group.
Go into the Application Pool for the site and change the account under which the site is running to this domain account. You will also want to ensure that this account has the proper NTFS permissions to the site files. If this site only writes to the database, you can given the account read-only access the folder(s) with the site files.
Ensure the connection string used by the site is formed to request a trusted connection. (See www.connectionstrings.com for the syntax)
On the database server execute your grant to this account:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "domain name\MyIISAccount"

There may also be other Kerberos issues related to the fact that both servers on the domain and that might require creating a SPN (Service Principal Name).

Neither server is on a domain (i.e., both are member servers) and you want to use trusted connections:

Create a local account on both the web server and the database server with the same username and password. It is critical that they both have the same username and password. This technique involves using NTLM "pass-through" authentication which matches the hash created by the username and password to determine if the user is authenticated between the two desparate servers. On Windows 2008 R2, you may have to jump through a few local policy hoops to ensure that NTLM is enabled between the two servers.
Do steps #2 to #4 above with this account.
On the SQL Server, ensure that this local account has a Login and that this login maps to a User in the database. Then you would execute something like:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\AccountUsedBySite'

You want to use SQL accounts instead of a trusted connection:

In this scenario, the connection string used by the site to connect to the database will include a username and password which map to a Login on the SQL Server database which maps to a User in the database (typically put in the db_owner role to make it dbo). This
Assuming the credentials are correct, you need only execute your grant against this user:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLUserAccountUsedBySite'

Both IIS and SQL Server are on the same machine and you want to use trusted connections

Create a local user account and drop it into the Users group.
Drop this account into the local IIS_IUSRS group.
Go into the Application Pool for the site and change the account under which the site is running to this local account. You will also want to ensure that this account has the proper NTFS permissions to the site files. If this site only writes to the database, you can given the account read-only access the folder(s) with the site files.
Ensure the connection string used by the site is formed to request a trusted connection. (See www.connectionstrings.com for the syntax)
In SQL Server, create a login for this account then create a user in the appropriate database for this account dropping it into the appropriate roles.
Now execute your grant to this account:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO 'SQLServerMachineName\MyIISAccount'

回复收藏 0 原文