CODEWITHSUNDEEP
c#asp.netsql-serveriis
Steven
Steven

Reputation: 13769

ASPX Windows Authentication and Not

I want to provide the results of a SQL Server query to a specific set of users without giving those users access to the database itself.

Query the Database:

The following ASP.NET Web App code (when published) successfully connects to the database as IIS APPPOOL\DefaultAppPool and displays the query results.

<asp:GridView runat="server" AutoGenerateColumns="true" ID="myGridView"/>

string connStr  = "Server=MyHost;Database=MyDb;Trusted_Connection=Yes;";
string queryStr = "SELECT Name, DateCreated FROM MyTable;";

SqlConnection sqlConn     = new SqlConnection(connStr);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(queryStr, sqlConn);
DataSet ds = new DataSet();
sqlAdapter.Fill(ds);
myGridView.DataSource = ds.Tables[0];
myGridView.DataBind();

Attempt at authentication: I tried enabling Windows Authentication (<authentication mode="Windows"/> in web.config. My intention was to get the username with the following code and compare to my list of approved users:

System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

With Windows Authentication, the web app runs as the client user, and I can verify if that user is in my list. However, the web app fails to connect to the database, as that user does not have database permissions.

Question: How do I restrict this webpage so only a specific set of users can view it while still connecting to the database as IIS APPPOOL\DefaultAppPool?

Upvotes: 1

Views: 42

Answers (1)

mason
mason

Reputation: 32699

Do not use System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString(). That's how you get the current user the application pool is running as, not the logged in user. You can force them to be the same by enabling impersonation, but then it will use the impersonated user's account when you use Integrated Security to access the database. Which you've explicitly said you don't want.

  • Enable Windows Authentication
  • Disable Impersonation
  • Use Request.User.Identity.Name (when in a Web Forms control or page) or System.Web.HttpContext.Current.Request.User.Identity.Name elsewhere (or pass the username from your Web Forms context).
  • Use Integrated Security to access the database.

Upvotes: 1

Related Questions