Access sql server without directly permission in domain from ASP.NET

Question

Hi all

Here is the situation. All the machines and users are in the same domain.We are in a domain enviroment. There are some sql server 2005/2008 storing data. There is a ASP.NET site in the domain using the Window Authentication. Now, we need read the data from the sql server and display them using SqlDataSource and GridView. But most of users do not have the direct permission to access the database. Is there any solution to get the data from database and display them on the site without granting users permission?

Best Regards,

Answer 1

Yes - create a SQL user on the database server, and use this one SQL user account to access the database for all your users (who use their domain accounts to authenticate to your system).

This is a very common situation, and it allows you to easily fine grain the access available to that one account, rather than trying to manage multiple domain style accounts on your SQL server.

Plenty of connection string options here:

http://www.connectionstrings.com

Also a pretty decent MSDN article on the same here:

http://msdn.microsoft.com/en-us/library/ms998300.aspx

Answer 2

I may have misunderstood your question, but if what you are trying to avoid is explicitly having to grant database permissions each user individually, you can get around this by granting permissions to a Active Directory security group of which all the users you need to grant access to are a member. The individual users will inherit permissions from the group.

However, there's another problem here which may mean that enabling SQL authentication is a simpler solution. Unless Kerberos authentication is enabled in the domain (or the web server is on the same physical machine as the database server), the web server will not be able to pass the users' credentials on to the database server for authentication - the so-called double hop problem. Google for "windows double hop" for more information.