In This blog we will learn how to Connect C# applications with MS SQL Server Database using Active Directory user.

Normally we make connection to MS SQL Server using SQL Server Authentication or Windows Authentication

For SQL Server Authentication we have user in SQL Server it can be any username & password

While for Windows Authentication your database must be accessible to the user which you are logged-in or IIS-Application pool user.

I Windows Authentication ConnectionString we can provide different username and password, it will always take current logged-in user.

To Login-in to MS SQL Server database using Active Directory user we have to use windows Authentication but with additional settings in web.config file. that is to impersonate the application user implicitly instead of automatically taking current logged-in user.

User Identity Impersonating 

Below is one line code for impersonating specific user, it should be inside <system.web> tag

ConnectionString sample

Below is Windows Authentication ConnectionString sample

EntityFramework ConnectionString

Below is Windows Authentication ConnectionString sample for EntityFramework

Till here it will work in your local development server and you can debug your site in visual studio debugger by clicking F5 in Visualstudio.

But if you deploy your website on IIS it will not work because you have to impersonate that your there too.

IIS Configuration

1- Go to IIS and Create website as you do normally

2- Right Click on your website which you have impersonated go to Manage Website and then Advanced Settings

In General Section locate Physical Path Credentials double click it, select specific user and enter the same active directory user which you selected for database connection in web.config file

3- In IIS Click on Application pools and then right click on your website select advanced settings locate Identity under Process Model section and again select same active directory user.

4- After this if you run your website in browser you might get error saying that “The user (Active Directory user) do not have permission on .net framework folder.” copy that path go to that path and give access to your active Directory user.

For me till here it was done.

I was facing issue with slow database connection or query execution and solved it by adding connection timeout=120 in connection strings as below.