How to configure SQL Server to work with Windows authentication? RRS feed

  • Question

  • User1702975116 posted


    I developed a website in Asp.NET MVC that uses a SQL connection string but Windows authentication is not taken into account.

    My website is published on an IIS7 server and the SQL Server is on other server.

    Here is the connection string I am currently using:

        <add name="Context" connectionString="data source=ServerName;initial catalog=DbName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

    When I insert data into a table, SQL server uses the IIS server session or I would like to use the user's session which inserts data from my site.

    Can you help me please?

    Monday, August 5, 2019 9:29 AM

All replies

  • User753101303 posted


    Windows authentication just ensures the current Windows account is used to connect to SQL Server. In a web app, this is is moft often the account under which the application runs. To get the user information on the SQL Server side you would need to enable also user impersonation so that each db connection is done using the user identity (but AFAIK it will create a connection pool for each user)

    My personal preference is to do that when really needed (ie using SQL Server side permissions for example). Here I would likely just pass the user name or id explicitely as an entity property (possibly generalized through some interface or whatever).

    Edit: this is to keep track of who have created/changed each row and when ?

    Monday, August 5, 2019 9:38 AM
  • User-474980206 posted
    You do what you want will take several steps

    1) enable Kerberos authentication (ntlm does not support this) on the iis and Sqlserver box’s
    2) enable server delegation of the iis server to Sqlserver.
    3) enable impersonation in the web config.
    4) in iis change the windows authentication provider to Kerberos.

    Monday, August 5, 2019 2:17 PM