SQL Server集成安全性 [英] SQL Server Integrated Security
问题描述
我一直在努力寻找解决SQL Server中与安全性相关的问题的方法. 我们正在开发一个针对SQL Server 2008的.NET应用程序，我们想使用FileStream.
I've been searching hard to get my head around security related issues in a SQL Server. We're developing a .NET application that targets SQL Server 2008 and we want to use FileStream.
现在我发现，如果您使用集成安全性，则SQL Server仅允许通过Win32 API进行FileStream.问题是我们大约有80％的应用程序已完成，但是它完全基于SQL身份验证.因此，我们正在按照应用程序的形式直接执行INSERT，并且没有对每个CRUD操作都使用存储过程.
Now I've found out that SQL Server only allows FileStream through the Win32 API if you use Integrated Security. The problem is that we have around 80% of our application finished, but it is entirely based on SQL Authentication. So we are doing INSERT's straight form our application and are not using Stored Procedures for every CRUD operation.
这是相对安全的，因为我可以以加密形式存储SQL用户名和密码.我知道密码是以明文形式传输的，但是我愿意接受.
This is relatively safe because I can store the the SQL username and password in an encrypted form. I know the password is transported in Clear Text, but I'm willing to accept that.
我们希望最终用户能够通过诸如Crystal Reports之类的工具连接到数据库，为此，我们有一个额外的SQL登录名，该登录名仅授予SELECT权限.
We want end-users to be able to connect to the databse through tools such as Crystal Reports and for that we have an extra SQL login that has only SELECT-rights granted.
现在，如果我们更改为集成安全性，我们将必须授予单个用户(通过AD组等)权限来执行应用程序可以执行的操作.否则，应用程序将无法完成其工作.但是，当最终用户直接连接到数据库时，他也将拥有这些权利.
Now, if we change to Integrated Security we would have to give individual users (via AD groups, etc.) rights to do the things the application can do. Otherwise the application would not be able to do it's work. But then the end-user would also have these rights when he connects straight to the DB.
我看到有人说您应该对每个CRUD操作都使用存储过程，并将EXEC权限仅授予AD组，但是我该怎么做?我看不到用户直接连接或通过应用程序连接时如何获得不同的授权.任何人都可以启发我.
I see people saying that you should use Stored Procedures for every CRUD operation and grant the EXEC-rights only to the AD-group, but how would I do this? I do not see how a user would have different authorizations when he connects directly or through the application... Can anybody enlighten me on this.
一个额外的奖励积分问题:据我所知，集成安全性不适用于工作组.人们如何使FileStream在工作组中工作?还是这被认为是不可能的?
An extra question for bonus-points: Intergrated Security will not work on a Workgroup as far as I understand. How do people get FileStream to work in a Workgroup then? Or is this considered an impossibility?
推荐答案
-
集成的安全性将使用旧版机制在工作组中工作，在旧版机制中，两台计算机上具有匹配的用户名和密码.此外，如果服务器具有匹配的用户帐户，则域用户可以使用旧版机制登录到非域服务器.
Integrated security WILL work in a workgroup, using the legacy mechanism, where you have a matching username and password on the two machines. Also, a domain user can use the legacy mechanism to log into a non-domain server if the server has a matching user account.
集成的安全性甚至可以使用不匹配的用户名和密码.这可能会帮助您解决您的情况.
Integrated security can even work with non-matching usernames and passwords. This may help you in your scenario.
尝试一下:
NET USE \\DBSERVER /USER:DOMAIN\USERNAME
将提示您输入密码.这将与数据库服务器建立NetBIOS会话.完成此操作后，您应该能够在数据库服务器上看到共享文件夹和共享打印机.
You will be prompted for your password. This establishes a NetBIOS session with the database server. You should be able to see the shared folders and shared printers on the database server once you have done that.
一旦在客户端计算机和数据库服务器之间建立了netbios会话，您将可以使用集成安全性而无需提示输入密码.
Once a netbios session has been established between the client computer and the database server, you will THEN be able to use integrated security without being prompted for a password.
您可能必须指定命名管道"作为要使用的网络协议，如果它不适用于TCP(但我认为可以).命名管道继承了您现有的NetBIOS会话，因此只要您可以列出可能很合适的共享即可.
You may have to specify "named pipes" as the network protocol to usem, if it doesn't work with TCP (but I think it will). Named Pipes inherits your existing NetBIOS session, so provided you can list the shares you are probably good to go.
您还可以使用Windows API函数NetUseAdd和包含密码的USE_INFO_2(第2级)信息来建立登录会话.
You can also establish the logon session using the windows API function NetUseAdd with USE_INFO_2 (level 2) information which incorporates the password.
我想答案很短，那就是您可以为您的应用程序进行特殊的Windows登录，并让用户使用该登录名.但是请注意，它们也不能使用自己的用户名和密码连接到同一服务器.
I guess the short answer then is that you can have a special Windows logon for your application and have the users log in using that. However note that they cannot also be connected to the same server using their own username and password.
这篇关于SQL Server集成安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
