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 ?
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.
None
0 Points
1 Post
How to configure SQL Server to work with Windows authentication?
Aug 05, 2019 09:29 AM|jboinembalome|LINK
Hello,
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:
<connectionStrings> <add name="Context" connectionString="data source=ServerName;initial catalog=DbName;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" /> </connectionStrings>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?
All-Star
45840 Points
16682 Posts
Re: How to configure SQL Server to work with Windows authentication?
Aug 05, 2019 09:38 AM|PatriceSc|LINK
Hi,
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 ?
All-Star
53554 Points
13305 Posts
Re: How to configure SQL Server to work with Windows authentication?
Aug 05, 2019 02:17 PM|bruce (sqlwork.com)|LINK
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.