SQL Server/Windows 集成安全性对任何事情都有好处吗？

Is SQL Server/Windows integrated security good for anything?

提问by dkretz

The distinctions among Windows user permissions and any set of SQL Server GRANTs seem like unrelated concepts. As often as not, it seems to actually be implemented with pseudo-logins for database roles; but that doesn't map usefully back to Windows permissions. Assuming single-login identity verification, why not just go with the simplest possible database roles?

Windows 用户权限和任何一组 SQL Server GRANT 之间的区别似乎是不相关的概念。通常情况下，它似乎实际上是使用数据库角色的伪登录来实现的；但这并不能有效地映射回 Windows 权限。假设单点登录身份验证，为什么不使用最简单的数据库角色呢？

EDIT:
So far we've picked up the single benefit that you don't need to store a password in your application; but that seems more like a trivial beneficial consequence than a design goal; there are lots of other more direct ways to achieve that, without closely coupling the entire security apparati of both universes.

编辑：
到目前为止，我们已经获得了您不需要在应用程序中存储密码的单一好处；但这似乎更像是一个微不足道的有益结果，而不是设计目标；还有许多其他更直接的方法可以实现这一目标，而无需将两个领域的整个安全设备紧密耦合。

EDIT AGAIN:
Doesn't anyone else have any benefit to suggest, other than single login and ability for SD to maintain groups, thereby duplicating the capability for groups (based on the same user login) already existing in SQL Server?

再次编辑：
除了单一登录和 SD 维护组的能力，从而复制 SQL Server 中已经存在的组的功能（基于相同的用户登录）之外，其他任何人都没有任何建议吗？

The group issue has several flaws, including the assumption that the AD manager is assumed to be equally qualified to maintain both; and it excludes any network connections that aren't part of AD (thereby locking you into MS technology.)

组问题有几个缺陷，包括假定 AD 经理具有同等资格来维护两者；它排除了不属于 AD 的任何网络连接（从而将您锁定在 MS 技术中。）

And to put it in best-practice terms, you've built in coupling of systems, which is generally conceded to be a Bad Thing.

用最佳实践的术语来说，你已经建立了系统耦合，这通常被认为是一件坏事。

回答by user43332

Many of these have been said or are similar to previous answers... With AD integration:

其中许多已经说过或与以前的答案相似......通过AD集成：

a) I don't have to worry about the users who have access to any given application, I can pass that off to the security guys.

a) 我不必担心有权访问任何给定应用程序的用户，我可以将其传递给安全人员。

b) I can restrict access at a table by table level based on groups that already exists, as well as forcing standard users to only have the ability to call stored proc's.

b) 我可以根据已经存在的组，按表级别限制对表的访问，并强制标准用户只能调用存储过程。

c) When a developer leaves my group, we don't have to change all the DB passwords (i.e. if you care about data security...)

c) 当开发者离开我的小组时，我们不必更改所有数据库密码（即如果您关心数据安全...）

d) It's easy to do custom logging based on the user who makes the change. There are other ways to do this, but I'm all about being lazy.

d) 根据进行更改的用户进行自定义日志记录很容易。还有其他方法可以做到这一点，但我只是懒惰。

e) Integrates with IIS authentication. If you're already using IE & IIS for your intranet, this just makes life a lot easier.

e) 与 IIS 身份验证集成。如果您已经在 Intranet 中使用 IE 和 IIS，那么这会让您的生活变得轻松很多。

Note: There are far more reasons not to use it, and I never used it before my present position. Here where everything is lined up in AD already... It's just the easiest time I've ever had with database security.

注意：不使用它的原因要多得多，而且我在担任现职之前从未使用过它。在这里，所有东西都已经在 AD 中排列好了……这是我在数据库安全方面遇到的最简单的一次。

回答by Frustrating Developments

When using integrated security your app doesn't need to know anything or handle anything about security, also, if the user is already logged in to your domain they don't need to log into your app as well (assuming you've got impersonation set up correctly).

使用集成安全性时，您的应用程序不需要了解或处理有关安全性的任何事情，此外，如果用户已经登录到您的域，他们也不需要登录到您的应用程序（假设您已被冒充）正确设置）。

The biggest advantage has to be using an AD group as a login in SQL Server. This way you can give access to everyone in the "Accounts" group access to a set of sprocs, tables etc and everyone in the "Managers" groups access to a different set, all with AD. Your system admins don't have to jump into Management Studio to give users access rights to your app.

最大的优势是使用 AD 组作为 SQL Server 中的登录名。通过这种方式，您可以向“帐户”组中的每个人授予对一组 sproc、表等的访问权限，以及“经理”组中的每个人对不同组的访问权限，所有这些都使用 AD。您的系统管理员无需进入 Management Studio 即可授予用户对您的应用程序的访问权限。

回答by Joe Pineda

I guess it basically down to "not reinventing the wheel" and taking advantage of the "many eyes" effect.

我想这基本上归结为“不重新发明轮子”并利用“多眼”效应。

Using Windows authentication you leverage the power of Windows integrated security, on top of which you can add your own stuff if so you wish. It's an already matured system which has been tested millions of times, sparing you the effort (and on your clients, the cost) of making your own mistakes and discovering/solving them later.

使用 Windows 身份验证，您可以利用 Windows 集成安全性的强大功能，如果愿意，您可以在此基础上添加自己的内容。这是一个已经成熟的系统，已经过数百万次的测试，让您省去了自己犯错和以后发现/解决错误的努力（以及您的客户的成本）。

And then plenty of people are constantly scanning the Windows authentication process, checking it for vulnerabilities, exploring ways to bypass it, etc. When a vulnerability is openly disclosed and a fix for it gets created, your application just got a "free" security enhancement.

然后很多人不断地扫描 Windows 身份验证过程，检查它是否存在漏洞，探索绕过它的方法等。当一个漏洞被公开披露并创建修复程序时，您的应用程序就得到了“免费”的安全增强.

In my current work we have AD groups as SQL logins, so we assign SQL permissions based on membership to AD groups. So all members of the sys engineering group have some permissions, the DBAs have other, normal users others, supervisors others, etc. Adding new users or changing their permissions is a simple thing to do, only done once at AD and they immediately get the permissions they should get at the database.

在我目前的工作中，我们将 AD 组作为 SQL 登录名，因此我们根据成员身份为 AD 组分配 SQL 权限。所以sys工程组的所有成员都有一些权限，DBA有其他，普通用户其他，主管其他等等。 添加新用户或更改他们的权限是一件简单的事情，只需在AD做一次，他们立即得到他们应该在数据库中获得的权限。

Post Edit:

帖子编辑：

Expanding a bit on the "reinventing the wheel": To an AD account I can deny the right to login to a specific machine - or lock it out of everymachine save one or two. I can stop them from loging in at more than 2 workstations at the same time. I can force them to change passwords after some time, plus enforcing some minimal strenght in them. And some other tricks, all of which improve security in my system.

在“重新发明轮子”上扩展一点：对于 AD 帐户，我可以拒绝登录特定机器的权利 - 或者将其锁定在每台机器之外，只保留一两个。我可以阻止他们同时登录 2 个以上的工作站。我可以强迫他们在一段时间后更改密码，并在其中强制执行一些最小强度。以及其他一些技巧，所有这些技巧都可以提高我系统的安全性。

With SQL S. users, you've got none of this. I've seen people trying to enforce them with either complicated SQL jobs or a sort of home-brewn daemon, which in my opinion is reinventing a wheel already invented.

对于 SQL S. 用户，您将一无所有。我见过有人试图通过复杂的 SQL 作业或某种自制的守护程序来强制执行它们，在我看来，这是重新发明一个已经发明的轮子。

And then you can't stop user SA (or a privileged user) loging in from any machine. I was told once of a clever way to stop a brute-force attack over a SQL S. which had its port for remote login open over the Internet - administrators of the site implemented a job that changed SA's password every half an hour. Had it been SQL + Windows, they could've simply said they wanted administrator to login only from certain boxen, or outright use only the Windows authentication, thereby forcing anyone to go thru the VPN first.

然后你不能阻止用户 SA（或特权用户）从任何机器登录。曾经有人告诉我，有一种聪明的方法可以阻止对 SQL S 的暴力攻击，该 SQL S 的远程登录端口在 Internet 上打开 - 该站点的管理员实施了一项工作，每半小时更改一次 SA 的密码。如果是 SQL + Windows，他们可以简单地说他们希望管理员只能从某些 boxen 登录，或者完全只使用 Windows 身份验证，从而迫使任何人首先通过 VPN。

回答by Kevin Fairchild

Since everyone else has discussed the benefits of Windows Authentication, I guess I'll play Devil's Advocate...

既然其他人都讨论了 Windows 身份验证的好处，我想我会玩恶魔的代言人......

Allowing the account 'Joe User' to have access to the server means that not only can be connect with your app, but he can also connect via any other means (SQL Tools, Excel, malware, etc.).

允许帐户“Joe User”访问服务器意味着不仅可以连接到您的应用程序，而且他还可以通过任何其他方式（SQL 工具、Excel、恶意软件等）进行连接。

With SQL Authentication, your app can run as a certain user and then the app can handle the database access. So when 'Joe User' runs your app, the app has SQL access... But 'Joe User' himself doesn't, which means that the aforementioned apps wouldn't be able to have implicit access to the database.

使用 SQL 身份验证，您的应用程序可以作为特定用户运行，然后应用程序可以处理数据库访问。因此，当“Joe User”运行您的应用程序时，该应用程序具有 SQL 访问权限……但“Joe User”本身没有，这意味着上述应用程序将无法隐式访问数据库。

回答by Charles Bretana

yes of course, If you have your application level data access layer running as a service, you can use integrated security to talk to the databasem using an Application "Service Account" to log in to the server... Then you don;t have to store user passwords in the applications config file, and you are not passing passwords over the network fir each new connection made to the database

是的，当然，如果您将应用程序级数据访问层作为服务运行，则可以使用集成安全性与使用应用程序“服务帐户”登录到服务器的数据库进行通信...然后您就没有了将用户密码存储在应用程序配置文件中，并且您不会在每次与数据库建立新连接时通过网络传递密码

回答by John MacIntyre

Integrated security is only really useful for intranet apps. The pseudo logins I've seen are mostly for internet web applications.

集成安全仅对 Intranet 应用程序真正有用。我见过的伪登录主要用于 Internet Web 应用程序。

Anyway, It's more than just not storing a password in your app, since hopefully you'd be salting and hashing your password anyway. There's also:

无论如何，这不仅仅是不在您的应用程序中存储密码，因为希望您无论如何都会对密码进行加盐和散列处理。还有：

1. The user doesn't have to log in, which is a big deal, if you are jumping into a webapp sparadically throughout the day, or work somewhere that has multiple internal webapps.

2. User management is free, since the IT admin only has to edit the user in Active Directory.

3. User names and Role names are consistent throughout the organization.

4. User impersonation is a more secure method when accessing an internal webservice. (for example; an internet website accesses an intranet webservice)

5. The web application doesn't need to do anything extra user authorization on the database, since it's all handled seamlessly.

6. [EDIT] You also know the user in your database objects. So you can have a view only return rows associated to them. (unless you create a new SQLServer user for each app user, this would be impossible, and creating a new SQLServer user for each app user is also unreasonable)

1. 用户不必登录，这很重要，如果您一整天都在偶尔跳入一个 web 应用程序，或者在有多个内部 web 应用程序的地方工作。

2. 用户管理是免费的，因为 IT 管理员只需在 Active Directory 中编辑用户。

3. 用户名和角色名在整个组织中保持一致。

4. 在访问内部网络服务时，用户模拟是一种更安全的方法。（例如；一个 Internet 网站访问一个 Intranet Web 服务）

5. Web 应用程序不需要对数据库进行任何额外的用户授权，因为这一切都是无缝处理的。

6. [编辑] 您还知道数据库对象中的用户。所以你可以让一个视图只返回与它们关联的行。（除非你为每个app用户创建一个新的SQLServer用户，否则这是不可能的，为每个app用户创建一个新的SQLServer用户也是不合理的）

Integrated security isn't right for everything, but for the enterprise, there's a lot of value add.

集成安全性并非适用于所有情况，但对于企业而言，有很多附加值。

回答by John MacIntyre

SQL Logins: Obfuscated cleartext passwords over the wire

SQL 登录：通过网络混淆的明文密码

Windows Integrated Login with NTLM: Hashes passed over the wire

使用 NTLM 的 Windows 集成登录：通过网络传递的哈希

Windows Integrated Login with Kerberos: Proper secure authentication.

Windows Integrated Login with Kerberos：正确的安全身份验证。

There is only one choice if you care about security.

如果您关心安全，则只有一种选择。

回答by cecil

It has been my experience that the response time of queries run against a database from most applications are significantly faster when connecting via SQL authentication. Removes the delay that occurs from constantly asking AD for security validation.

根据我的经验，当通过 SQL 身份验证连接时，大多数应用程序对数据库运行的查询的响应时间要快得多。消除因不断要求 AD 进行安全验证而发生的延迟。

Performance wise SQL authentications >> windows integrated security.

性能明智的 SQL 身份验证 >> Windows 集成安全性。

回答by Webjedi

Integrated security gives greater flexibility in user access IMO. For example if my organization wants to limit the hours during which the developer group can access the server, integrated security is my best bet.

集成安全性为用户访问 IMO 提供了更大的灵活性。例如，如果我的组织想要限制开发人员组可以访问服务器的时间，集成安全是我最好的选择。

But it's not right for everything.

但这并不适合所有情况。

[EDIT] Also it's great for logging access. If I had to create a SQL login for each new developer in a large organization...it would probably stop happening and logins would get shared, then I'd never have confidence in the ability to point the finger at the knucklehead who dropped a table.

[编辑] 它也非常适合记录访问。如果我必须为大型组织中的每个新开发人员创建一个 SQL 登录......它可能会停止发生并且登录会被共享，那么我永远不会有信心将手指指向掉下一个指节的指关节桌子。

回答by Orion Edwards

Disregarding the grant table/etc side of things, the login side of things is very useful, because your app can connect to SQL server using windows authentication, which means

不考虑授权表/等方面，登录方面非常有用，因为您的应用程序可以使用 Windows 身份验证连接到 SQL 服务器，这意味着

You don't have to put your database username and password in a file in your application somewhere

您不必将数据库用户名和密码放在应用程序中的某个文件中

Any time you put a password in a plain text file, that's a security risk. Avoiding that is great.

任何时候将密码放入纯文本文件中，都会存在安全风险。避免这种情况很棒。