我如何才能登录的用户名时，不同的服务器的Web服务器上的SQL Server数据库？ [英] How can I log user name when SQL Server database on different server than web server?

问题描述

我已经创建ASP.NET Web窗体有关联的用户名，日期审计表，并改变了使用触发器数据填充。 SQL Server数据库是不同的服务器在Web服务器上。的要求是，各个用户名在审计表被记录。我发现，要做到这一点的唯一方法如下：
•SQL连接字符串指定集成安全性，以及
•IIS身份验证指定ASP.NET模拟和基本身份验证。
为了使用该应用程序，用户输入他们的Windows凭据登录到Web服务器和访问底层的SQL Server数据库，必须是具有读/写/执行存储过程的权限组的一部分。
这是为了做到这一点，而无需使用表单认证和相关的用户表的唯一途径？对于内部网站，这被认为是足够安全？

I have created ASP.NET web forms for editing data and the underlying database tables have associated audit tables with the user name, date, and changed data that are filled using triggers. The SQL Server database is on a different server than the web server. The requirement is that the individual user names be logged in the audit tables. The only way I found to do this is the following: • SQL Connection string specifies Integrated Security, and • IIS Authentication specifies ASP.NET Impersonation and Basic Authentication. In order to use the application, a user enters their windows credentials to log on to the web server and to access the underlying SQL Server database and must be part of a group that has read/write/execute stored procedure permissions. Is this the only way to accomplish this without using Forms Authentication and an associated 'Users' table? For an internal web site, is this considered secure enough?

推荐答案

如果你想使用窗体身份验证，需要使用的模拟。

If you want to use Forms Authentication, you'll need to use Impersonation.

例表：

create table foo (
  id int primary key,
  bar varchar(255) not null,
  inserted_by_user varchar(255) default current_user
);

这需要从你的数据库连接池一定的帮助。

This requires some help from your database connection pool.

当你从池中的连接，运行此：

When you get your connection from the pool, run this:

execute as user = 'alice'; -- where alice is the logged in user's username

然后运行，你是打算在运行正常的SQL查询：

Then run your normal SQL queries that you were planning on running:

insert into foo (id, bar) values (1, 'whatever');

当你释放你的连接返回到池中运行这样的：

When you release your connection back to the pool run this:

revert;

当你看着表富，你应该看到这一点：

When you look at table foo, you should see this:

id  bar       inserted_by_user
------------------------------
1   whatever  alice

这篇关于我如何才能登录的用户名时，不同的服务器的Web服务器上的SQL Server数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！