在 winforms 应用程序中连接数据库的服务帐户 [英] Service account to connect database in winforms app

问题描述

这看起来是一个很常见的问题，但找不到任何正确的答案.

This looks a very common question but could not find any proper answer.

我们有许多 WinForms 应用程序目前正在使用 sql server 帐户连接 sql.现在我们计划使用服务帐户(活动目录)连接到数据库.有一种选择，我们可以为每个用户提供对数据库的访问权限并使用我们当然不想要的集成安全性.考虑到应用程序不需要/最少更改，最好的解决方案是什么?

We have many WinForms apps that are currently using sql server account to connect sql. Now we are planning to use service accounts (active directory) to connect to DB. There is one option where we could provide each user access to the database and use integrated security which certainly we don't want. What could be the best solution considering that no/minimum change is required in the app?

非常感谢.

推荐答案

我想我明白你想要做什么.

I think I understand what you're trying to do.

Sql 帐户在这种情况下不太理想，因为您必须随应用程序分发帐户凭据.您可以使用加密的配置文件部分或其他技巧来做一些事情，但最终用户名/密码会暴露出来并且不容易维护.

Sql accounts are less desirable in this situation because you have to distribute the account credential with your app. You can do things with encrypted config file sections or other tricks, but ultimately that username/password are exposed and not easily serviceable.

集成安全性通过使用 Active Directory 中内置的身份验证基础结构改进了这一点.您不再需要为您的应用分发密码.

Integrated security improves on this by using the authentication infrastructure built into Active Directory. You no longer need to distribute passwords with your app.

但是，集成安全仍然存在两个弱点.

However, there are still two weaknesses with Integrated Security.

首先是账户管理.为每个 Active Directory 用户设置 Sql Server 登录可能很乏味，而且随着时间的推移用户流失，管理它们也很笨拙.

The first is account management. It can be tedious to set up Sql Server logins for each of your Active Directory users, and awkward and unwieldy to manage them as you have user churn over time.

谢天谢地，这并没有你想象的那么糟糕.您可以通过为 Active Directory 组而不是单个用户创建 Sql Server 登录来解决这个问题.将 Active Directory 用户设置为此类组的成员就足以授予他们组访问权限.您甚至可以使用少量组，一个代替每个服务帐户".在您的原始提案中，以支持分层访问.使用此方案，实际登录用户仍可用于 Sql Server(因此也可用于应用程序)，以便在需要时在应用程序内分配更具体的权限.

Thankfully, this isn't as bad as you might think. You can get past the issue by creating Sql Server logins for Active Directory groups instead of individual users. Setting an Active Directory user as a member of such a group is enough to grant them the group access. You can even use a small number of groups, one in place of each "Service Account" in your original proposal, in order to support tiered access. Using this scheme, the actual logged in user is also still available to Sql Server (and therefore to the application) for assigning more specific permissions within the app if needed.

另一个弱点是您直接向用户授予原始数据库权限，而不仅仅是授予使用应用程序的权限.这是一个真正的问题，如果 Joe 用户可以下载 Sql Server Management Studio(或任何可能甚至不需要安装的类似应用程序)并针对授权使用该应用程序的数据库部分运行他们想要的任何查询.

The other weakness is you are granting raw database permissions directly to users, instead of merely granting permission to use an application. This is a real problem, if Joe User can download Sql Server Management Studio (or any similar app that may not even require installation) and run whatever query they want against the section of your database authorized for use of the app.

很遗憾，您提议的解决方案(Active Directory 中的服务帐户)不起作用.这些服务帐户仍然需要使用集成安全性，并且集成安全性仅在相关帐户实际运行应用程序时才起作用.这永远不会按照您希望的方式工作.

Unfortunately, your proposed solution (Service Accounts in Active Directory) won't work. Those service accounts still need to use integrated security, and integrated security only works when the account in question is actually running the application process. That's never going to work the way you want it to.

那我们能做什么?

一个最佳实践"正在使用 应用程序角色结合基于组的集成安全.不幸的是，这需要更改您的应用程序，以便每个数据库连接都可以调用 sp_setapprole.

One "best practice" is using Application Roles in combination with group-based integrated security. Unfortunately, this requires changes to your application so every database connection can call sp_setapprole.

这也让您重新为您的应用程序分发凭证.它更安全一些，因为这个分布式凭证需要集成安全登录才能使用；如果这样的凭据泄露给全世界，您的数据库仍然只会在您自己的用户群中受到损害.

This also has you back to distributing a credential with your application. It's somewhat safer because this distributed credential requires the integrated security login before it can be used; if such a credential leaks to the world, your database is still only compromised among your own user base.

但是请记住，当凭据公开时，任何有动机并访问 Google 的用户仍然可以找到一个应用程序，该应用程序可以让他们复制您的应用程序正在执行的操作，以使用相同的权限运行他们想要的任何查询应用程序，就像单独使用基于组的集成安全一样.您仍然需要保护凭证，以及随之而来的所有挑战.

Keep in mind, though, when the credential is exposed any of your users with motivation and access to Google can still find an app that will let them replicate what your application was doing to run any query they want with the same permissions as the application, just as with group-based integrated security alone. You still need to protect the credential, and all the challenges going with that.

换句话说，这最终仍然具有向用户授予原始数据库权限的集成安全弱点和需要保护凭据的 sql 身份验证弱点.我不是粉丝.

In other words, this still ultimately has both the integrated security weakness of granting raw database permissions to your users and the sql authentication weakness of needing to protect a credential. I'm not a fan.

我知道还有另外两个选项，但都需要对应用程序进行重大修改.

There are two other options I know, but both requires significant modifications to the application.

第一个选项涉及重写应用程序以使用服务层(通常通过 Web API 使用 JSON)而不是直接访问数据库.这是有效的，因为您控制服务层机器，但这是对应用程序的巨大重写，并且需要额外的服务器资源.

The first option involves re-writing the application to use a service layer (usually JSON via web API) instead of direct database access. This works because you control the service layer machine(s), but it's a huge rewrite of the application and requires additional server resources.

第二个选项涉及将所有数据库访问转移到存储过程中.然后将 Active Directory 组帐户设置为仅对相应的存储过程具有执行权限.通过这种方式，存储过程构成了事实上的 API.这里的弱点是它可以限制报告选项.

The second option involves moving all database access into stored procedures. Then you set up the Active Directory group accounts to only have execute permissions to the appropriate stored procedures. In this way, the stored procedures comprise a de-facto API. The weakness here is it can limit reporting options.

那么我会在你的鞋子里做什么?可能会选择基于组的集成安全选项，而不使用特殊的应用程序角色(没有 sp_setapprole... 使用 Sql Server 角色为您的组分配权限仍然是一个好主意).但是，我会尽量限制对这些组的权限.这样，您的应用程序最初需要的唯一更改是连接字符串.然后我们可以随着时间的推移转向存储过程选项.这可以在多个发布周期内在应用程序中完成，因此您无需等待对集成安全性进行更改.

So what would I do in your shoes? Probably go for the group-based integrated security option, without using the special Application Roles (no sp_setapprole... using Sql Server roles for assigning permissions to your groups is still a good idea). However, I would try to restrict permissions on those groups as much as possible. This way, the only change needed initially in your application is to the connection string. Then we can move towards the stored procedure option over time. This can be done in the application over several release cycles, so you don't need to wait to make the change to integrated security.

这篇关于在 winforms 应用程序中连接数据库的服务帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！