perform SQL Server operations with windows user id from an ASP.NET page

Question

I have an ASP.NET application that authenticates users using Ldap against active directory. This is, the users enter their same windows credentials on the webform to login to this application. The application is basically a SQL Server database frontend, and in my limited experience, I use the standard connectionStrings label in the web.config to enter the information to login to the database (using a SQL Server login as of right now). I want to change that so instead, each time the user connects then he or she perform the database operations with his or her windows user. Of course the database has setup those permission already to the users, but I have no idea what connection string am I suppose to use, since now there is not going to be a static user/password combo, and I don't want to create SQL Server logins to everybody that is going to use the application.

Thanks!

Answer 1

Use the ConnectionStringBuilder class and provide it your initial connection string from the web.config. From there, change the username and password properties to match those of your current user.

public String GetUsersConnectionString(CurrentUser user) {
  string connectString = ConfigurationManager.ConnectionStrings["LocalSqlServer"]
                        .ConnectionString;

  SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectString);
  builder.UserId = user.UserId;
  builder.Password = user.Password;

  return builder.ToString();
}

Answer 2

You would have to configure the ASP.NET machine for delegation. See this knowledge base article for SQL 2000, or a newer one for SQL 2008.

We have tried it a couple of times at work, but did not manage to get it running smoothly.