使用域用户的 SQL 连接字符串? [英] SQL Connection String Using a Domain User?

问题描述

以前，对于我们所有的 asp.net 应用程序，我们一直使用 SQL Server 中的 sysadmin 用户来连接和添加/更新/删除/获取数据.我们的 SQL 管理员想要删除该帐户并创建一个域帐户，以便我们可以在 .net 应用程序中使用该帐户.

Previously for all our asp.net applications we have been using a sysadmin user within SQL Server to connect and add/update/delete/get data. Our SQL Admin wants to delete that account and create a Domain Account so we can use that account within our .net applications.

我当前的连接字符串是:

My current connection string is:

name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient"

使用域帐户的连接字符串是什么?

What would the connection string be for using a domain account?

我试过了:

name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=domain\user;Password=password" providerName="System.Data.SqlClient"

它不起作用.

是否有其他方法可以使用域帐户连接到 SQL Server?

Is there a different way to connect to SQL Server using a domain account?

推荐答案

查看 connectionstrings.com每个可能的变化 - 我一直使用的非常方便的资源

Have a look at connectionstrings.com for every possible variation - a very handy resource I use all the time

具体来说，您需要这种格式:

Specifically, you want this format:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

当然，这仅在相关域帐户是打开连接的帐户时才有效.

This, of course, only works if the domain account in question is the one opening the connection.

没有简单的方法可以连接任意凭据 - 但您可以模拟相关用户然后连接.

There's no easy way to connect with arbitrary credentials - but you can impersonate the user in question and then connect.

这可能有点痛苦.如果用户在本地网络上(或者您控制他们的浏览器配置)，另一种方法是在您的站点上使用 Kerberos 身份验证.这些页面将使用相关用户的权限提供服务 - 然后您可以使用上面的连接字符串，IIS 将使用每个用户的适当凭据连接到 Db.从安全角度来看，这特别有用，因为 Db 能够基于每个用户进行审核，并且权限可以是每个用户/行/列，而不仅仅是每个应用.

This can be a bit of a pain. An alternative if the users are on the local network (or you control their browser config) is to use Kerberos authentication on your site. The pages will be served with the relevant user's permissions - then you can use the connection string above and IIS will connect to the Db with the appropriate credentials for each user. This is particularly useful from a security perspective as the Db is able to audit on a per-user basis, and permissions can be per-user/row/column instead of only per-app.

这篇关于使用域用户的 SQL 连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！