C# 如何通过 windows-service 的 windows 身份验证连接到 sql-server？

How to connect to sql-server with windows authentication from windows-service?

提问by r?ph

I have programmed a Windows Service in C# which should connect to an SQL-Server 2005 Express Database with System.Data.SqlClient.

我用 C# 编写了一个 Windows 服务，它应该使用 System.Data.SqlClient 连接到 SQL-Server 2005 Express 数据库。

As Microsoft prefers to use Windows Authentication over SQL Authentication I tried to connect to the DB with Trusted Conenction / Integrated Security.

由于 Microsoft 更喜欢使用 Windows 身份验证而不是 SQL 身份验证，因此我尝试使用 Trusted Conenction / Integrated Security 连接到数据库。

However that doesn't work as I get a System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\LOCAL SERVICE'.

但是，这不起作用，因为我收到 System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\LOCAL SERVICE'。

Is it possible to login with a different Windows Account?

是否可以使用不同的 Windows 帐户登录？

采纳答案by mjv

At the moment, the service appears to currently run under the LocalService Accountand this service account is not currently authorized on SQL.

目前，该服务似乎当前在LocalService 帐户下运行，并且此服务帐户当前未在 SQL 上获得授权。

The situation can be fixed in one of two ways: running the account under an account whichis authorized in SQL or add the LocalService account as a login in SQL. Specifically:

这种情况可以通过以下两种方式之一解决：在 SQL 中授权的帐户下运行该帐户或在 SQL 中添加 LocalService 帐户作为登录名。具体来说：

• Change which account the service is ran as, in the Service management console. (ex: Computer Management | Services and Applications | Services then right-click for "Properties" on the service in question)
• Alternatively, in "Microsoft SQL Management Studio", add the LSA account as a login and set this new principal (login) in a way that it can access the desired database objects.

• 在服务管理控制台中更改运行服务的帐户。（例如：计算机管理 | 服务和应用程序 | 服务，然后右键单击相关服务的“属性”）
• 或者，在“Microsoft SQL Management Studio”中，添加 LSA 帐户作为登录名，并以可以访问所需数据库对象的方式设置此新主体（登录名）。

Edit: The first approach is probably preferable, because the LocalService account is so pervasively found in the system that granting it access to SQL would expose SQL and the databases would a particular service or driver using it become compromised.
Instead by introducing a specific account one has more control over who accesses SQL objects and how. This of course bring the issue of configuring such an account, with regards to the privileges it should be granted, at the level of the system (not of SQL), and depending on what the underlying Service does, on may need to make this account rather powerful, hence a potential liability in other ways....

编辑：第一种方法可能更可取，因为 LocalService 帐户在系统中无处不在，授予它对 SQL 的访问权限会暴露 SQL，并且数据库会破坏使用它的特定服务或驱动程序。
相反，通过引入特定帐户，人们可以更好地控制谁访问 SQL 对象以及如何访问。这当然带来了配置这样一个帐户的问题，关于它应该被授予的权限，在系统级别（而不是 SQL），并且取决于底层服务的作用，可能需要创建这个帐户相当强大，因此在其他方面潜在的责任......

回答by James

You need to grant access to the LOCAL SERVICE account in SQL Express. As per my comment, my advice would be to create a new account which your service can run under and then add the relevant permissions inside SQL Express, in other words, don't run your service under LOCAL SERVICE.

您需要授予对 SQL Express 中的 LOCAL SERVICE 帐户的访问权限。根据我的评论，我的建议是创建一个新帐户，您的服务可以在该帐户下运行，然后在 SQL Express 中添加相关权限，换句话说，不要在 LOCAL SERVICE 下运行您的服务。

回答by Neerav

If you want to use trusted windows authentication, the easiest thing to do is to run the service with a domain account (with the least privilleges required) that is permissioned to the sql server database.

如果要使用受信任的 Windows 身份验证，最简单的方法是使用有权访问 sql server 数据库的域帐户（需要最少的特权）运行该服务。

回答by BrianB

When a Windows Service is started by the Service Control Manager, the process executes as a particular user just as with any other process running on the OS.

当服务控制管理器启动 Windows 服务时，该进程以特定用户身份执行，就像操作系统上运行的任何其他进程一样。

There are a number of "built-in" user accounts that are used for running Windows Services. You can see the complete set of Windows Services and the account they run as (called "Log On As" in Windows 7) if you look at the "Services" node within Computer Management".

有许多用于运行 Windows 服务的“内置”用户帐户。如果您查看“计算机管理”中的“服务”节点，您可以看到完整的 Windows 服务集及其运行的帐户（在 Windows 7 中称为“登录身份”）。

From my experience, when we want a Windows Service to talk to the DB using integrated security we take the second approach below:

根据我的经验，当我们希望 Windows 服务使用集成安全性与数据库通信时，我们采用以下第二种方法：

1) Assign one of the built-in accounts as the "Log On As" account and add this account as a login on the SQL Server instance with the appropriate DB permissions

1) 将其中一个内置帐户指定为“登录为”帐户，并将此帐户添加为具有适当数据库权限的 SQL Server 实例上的登录名

2) Use/create a local or domain account for the Windows Service to use and then add this account as a login with the appropriate DB permissions. It is possible thru the installer to prompt for the user account credentials during the installation of the service.

2) 使用/创建供 Windows 服务使用的本地或域帐户，然后将此帐户添加为具有适当 DB 权限的登录名。可以通过安装程序在服务安装期间提示输入用户帐户凭据。

I can't claim to be expert enough to point out all pros and cons of each approach, however it's worth considering the following:

我不能声称自己足够专业，无法指出每种方法的所有优缺点，但值得考虑以下几点：

• with approach 1 all services and processes that run as the selected built-in account will have permission to access you database. This is not the case with approach 2.

• with approach 1 the password configuration is managed by the machine itself but with approach 2 the password can be managed by the administrators and also conform to any required security policies in place.

• 使用方法 1，作为选定内置帐户运行的所有服务和进程都将有权访问您的数据库。方法 2 不是这种情况。

• 对于方法 1，密码配置由机器本身管理，但对于方法 2，密码可以由管理员管理，并且还符合任何所需的安全策略。

I hope this helps

我希望这有帮助

回答by Waleed Al-Balooshi

As mjv said, you need to either give Local Service Account access to the database or use a different account to run the service. You asked how you can change the account programmatically, which is accomplished by creating an Installer for the service and changing the Account property to "User" then specifying the Username and Password to run the service.

正如 mjv 所说，您需要授予本地服务帐户访问数据库的权限，或者使用不同的帐户来运行该服务。您询问了如何以编程方式更改帐户，这是通过为服务创建安装程序并将帐户属性更改为“用户”然后指定用户名和密码来运行服务来完成的。

The following link contains information on creating the installer if you scroll to the bottom:

如果您滚动到底部，以下链接包含有关创建安装程序的信息：

http://msdn.microsoft.com/en-us/library/aa984464(VS.71).aspx

http://msdn.microsoft.com/en-us/library/aa984464(VS.71).aspx

While the following provides a little more detail about the Account property:

虽然以下提供了有关 Account 属性的更多详细信息：

http://msdn.microsoft.com/en-us/library/system.serviceprocess.serviceprocessinstaller.account(VS.71).aspx

http://msdn.microsoft.com/en-us/library/system.serviceprocess.serviceprocessinstaller.account(VS.71).aspx

Hope this helps.

希望这可以帮助。