I've a problem with asp.net forms authentication (with sqlserver membership provider) that the impersonation is not well for our AD Users, which is required for auditing database transactions
when running the web applications sql server system_user becomes NT AUTHORITY\SYSTEM; by the way the connection string is like:
Data Source=10.50.1.21;Initial Catalog=tmpIIMS;Integrated Security=True;Persist Security Info=False;Trusted_Connection=Yes;
whic is authenticated by Integrated security with AD at IIS.
so could any one help me to retreive at audit with a desired account for each AD Users
From your description, your ASP.NET web application is using forms authentication, however, you want to impersonate your web page's server-side code under specific user account (for accessing the backend SQL server database) ,correct?
If so, I think you need to consider the following things first:
* whether the user accounts used for the forms authentication are the same account set (same username/password) as used in your ASP.NET server's windows domain? For example, ASP.NET support using ActiveDirectory membership provider to validate username/password
credentials against windows AD. In such case, the forms authentication username/password credential can be used to programmmatically impersonate and run ASP.NET code under the certain account:
If not (the forms authentication accounts are just custom accounts that have no relationship to windows accounts in AD), then you can only make your ASP.NET web application to run under a specific account. You can either configure the IIS application pool
identity to change the account under which your ASP.NET runs:
First thanks for your cooperation, you are right our web application authentication is based on custom forms authentication (related to policy we cannot us AD or Windows auth,) but the usersnames will be the same of AD Accounts; the imersonate is set to
true from beginning.
After you impersonate via the security token (obtained by calling LogonUser API ), are you putting the SQL server accessing code right after you successfully impersonated the current context? Also, make sure you have turn off the original impersonation in
web.config file. For testing, you can also try adding code to create a txt file on remote machine's share folder and check the created file's owner to see if the impersonated user account is used.
I've check after the security token (by calling LogonUser API ) it seems it is not delegated for all web application only at login page, and the problem still with Application Pool my impersonation did not override the IIS Pool Auth.
Login Code:
IntPtr token = IntPtr.Zero;
WindowsImpersonationContext impersonatedUser = null;
try
{
bool result = LogonUser(LoginUser.UserName, "ba", LoginUser.Password,
LogonSessionType.Network, LogonProvider.Default, out token);
if (result)
{
WindowsIdentity id = new WindowsIdentity(token);
impersonatedUser = id.Impersonate();
// Log the new identity
string secConn = "Data Source=10.50.1.21; Initial Catalog=sec;Persist Security Info=False; Trusted_Connection=True; User ID=" + WindowsIdentity.GetCurrent().Name;
string dataConn = "Data Source=10.50.1.21; Initial Catalog=tmp; Persist Security Info=False; Trusted_Connection=True; User ID=" + WindowsIdentity.GetCurrent().Name;
// Resource access here uses the impersonated identity
if (Request.QueryString["ReturnUrl"] != null)
{
FormsAuthentication.RedirectFromLoginPage(LoginUser.UserName, false);
}
else
{
FormsAuthentication.SetAuthCookie(LoginUser.UserName, false);
Response.Redirect("~/default.aspx");
}
}
else
{
Response.Write("</p>LogonUser failed: " +
Marshal.GetLastWin32Error().ToString());
}
}
catch
{
}
Yes, you' are right. Actually, the impersonation context just begins after the impersonate method get called (based on the logonUser API obtained token) and ends after the impersonation context is closed. And if you refer to the MSDN reference I mentioend,
the impersonation scope is just within the try{....}finally{...} block.
In other words, if you want to do programmtic impersonation, you need to do it at the place when you need to execute the certain privileged code. And after that , finish impersonation, you cannot impersonate one place and let the entire web application execute
under that security context. Based on your requirement, you will need to keep the username/password credentials if you want to impersonate the user later (after perform login in the login page), but this is really not a good idea(saving clear text username/password).
What do you think?
Yes, your are right, actually I can't do imersonation with saving at clear test username/pwd) especially because I'm the security consultant at this project; we have different solutions but the client wants authenticaton by mixing with ad and forms authentication,
and this big effort just to take the identity whic is logged in for database audit triggers.
My opinion is: I WILL CHANGE THE AUDIT CONCEPT COMPLETELY.
Thus, you will need to change your database side audit to not use the login identity (from the ASP.NET ) since it will not reflect the actual forms authenticated user identiy. Will you create some store-procedure (which explicitly take identity info as parameter)
for the db side auditing? Then, the ASP.NET side just call the SP to trigger the auditing (when data accessing occurs).
m_fuad67
Member
1 Points
5 Posts
asp.net form authentication impersonate with SQL Server
May 13, 2012 12:50 PM|LINK
Dear Guys,
I've a problem with asp.net forms authentication (with sqlserver membership provider) that the impersonation is not well for our AD Users, which is required for auditing database transactions
when running the web applications sql server system_user becomes NT AUTHORITY\SYSTEM; by the way the connection string is like:
Data Source=10.50.1.21;Initial Catalog=tmpIIMS;Integrated Security=True;Persist Security Info=False;Trusted_Connection=Yes;
whic is authenticated by Integrated security with AD at IIS.
so could any one help me to retreive at audit with a desired account for each AD Users
ignatandrei
All-Star
134832 Points
21599 Posts
Moderator
MVP
Re: asp.net form authentication impersonate with SQL Server
May 13, 2012 12:51 PM|LINK
put
<identity impersonate=true
in web.config
http://msdn.microsoft.com/en-us/library/72wdk8cc%28v=vs.71%29.aspx
Steven Cheng...
Contributor
4197 Points
547 Posts
Microsoft
Moderator
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 03:57 AM|LINK
Hi m_fuad67,
From your description, your ASP.NET web application is using forms authentication, however, you want to impersonate your web page's server-side code under specific user account (for accessing the backend SQL server database) ,correct?
If so, I think you need to consider the following things first:
* whether the user accounts used for the forms authentication are the same account set (same username/password) as used in your ASP.NET server's windows domain? For example, ASP.NET support using ActiveDirectory membership provider to validate username/password credentials against windows AD. In such case, the forms authentication username/password credential can be used to programmmatically impersonate and run ASP.NET code under the certain account:
#How To: Use Impersonation and Delegation in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ff647404.aspx
#Programmatically Impersonate a user in ASP.NET h
ttp://www.thescarms.com/dotnet/impersonate.aspx
If not (the forms authentication accounts are just custom accounts that have no relationship to windows accounts in AD), then you can only make your ASP.NET web application to run under a specific account. You can either configure the IIS application pool identity to change the account under which your ASP.NET runs:
#Application Pool Identities
http://learn.iis.net/page.aspx/624/application-pool-identities/
or you can still use impersonation, but specify a fixed account in web.config (also mentioend in the impersonation related articles above).
In addition, here are some MSDN articles which can gives detailed explanation on how the ASP.NET forms and windows authentication work.
#Explained: Forms Authentication in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ff647070.aspx
#Explained: Windows Authentication in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ff647076.aspx
Feedback to us
Microsoft One Code Framework
m_fuad67
Member
1 Points
5 Posts
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 06:30 AM|LINK
Hi Steven Cheng,
First thanks for your cooperation, you are right our web application authentication is based on custom forms authentication (related to policy we cannot us AD or Windows auth,) but the usersnames will be the same of AD Accounts; the imersonate is set to true from beginning.
I'll try advapi32.dll to try more.
m_fuad67
Member
1 Points
5 Posts
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 08:24 AM|LINK
Hi Steven,
I did the impersonation using advapi32.dll but the sql server connection stril still taking the NT Service Username.
Steven Cheng...
Contributor
4197 Points
547 Posts
Microsoft
Moderator
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 08:40 AM|LINK
Thanks for the quick reply m_fuad67,
After you impersonate via the security token (obtained by calling LogonUser API ), are you putting the SQL server accessing code right after you successfully impersonated the current context? Also, make sure you have turn off the original impersonation in web.config file. For testing, you can also try adding code to create a txt file on remote machine's share folder and check the created file's owner to see if the impersonated user account is used.
Feedback to us
Microsoft One Code Framework
m_fuad67
Member
1 Points
5 Posts
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 09:23 AM|LINK
I've check after the security token (by calling LogonUser API ) it seems it is not delegated for all web application only at login page, and the problem still with Application Pool my impersonation did not override the IIS Pool Auth.
Login Code:
IntPtr token = IntPtr.Zero; WindowsImpersonationContext impersonatedUser = null; try { bool result = LogonUser(LoginUser.UserName, "ba", LoginUser.Password, LogonSessionType.Network, LogonProvider.Default, out token); if (result) { WindowsIdentity id = new WindowsIdentity(token); impersonatedUser = id.Impersonate(); // Log the new identity string secConn = "Data Source=10.50.1.21; Initial Catalog=sec;Persist Security Info=False; Trusted_Connection=True; User ID=" + WindowsIdentity.GetCurrent().Name; string dataConn = "Data Source=10.50.1.21; Initial Catalog=tmp; Persist Security Info=False; Trusted_Connection=True; User ID=" + WindowsIdentity.GetCurrent().Name; // Resource access here uses the impersonated identity if (Request.QueryString["ReturnUrl"] != null) { FormsAuthentication.RedirectFromLoginPage(LoginUser.UserName, false); } else { FormsAuthentication.SetAuthCookie(LoginUser.UserName, false); Response.Redirect("~/default.aspx"); } } else { Response.Write("</p>LogonUser failed: " + Marshal.GetLastWin32Error().ToString()); } } catch { }Steven Cheng...
Contributor
4197 Points
547 Posts
Microsoft
Moderator
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 09:31 AM|LINK
Yes, you' are right. Actually, the impersonation context just begins after the impersonate method get called (based on the logonUser API obtained token) and ends after the impersonation context is closed. And if you refer to the MSDN reference I mentioend, the impersonation scope is just within the try{....}finally{...} block.
In other words, if you want to do programmtic impersonation, you need to do it at the place when you need to execute the certain privileged code. And after that , finish impersonation, you cannot impersonate one place and let the entire web application execute under that security context. Based on your requirement, you will need to keep the username/password credentials if you want to impersonate the user later (after perform login in the login page), but this is really not a good idea(saving clear text username/password). What do you think?
Feedback to us
Microsoft One Code Framework
m_fuad67
Member
1 Points
5 Posts
Re: asp.net form authentication impersonate with SQL Server
May 14, 2012 09:38 AM|LINK
Yes, your are right, actually I can't do imersonation with saving at clear test username/pwd) especially because I'm the security consultant at this project; we have different solutions but the client wants authenticaton by mixing with ad and forms authentication, and this big effort just to take the identity whic is logged in for database audit triggers.
My opinion is: I WILL CHANGE THE AUDIT CONCEPT COMPLETELY.
I've the same question for you???
Steven Cheng...
Contributor
4197 Points
547 Posts
Microsoft
Moderator
Re: asp.net form authentication impersonate with SQL Server
May 16, 2012 02:14 AM|LINK
Thanks for followup.
Thus, you will need to change your database side audit to not use the login identity (from the ASP.NET ) since it will not reflect the actual forms authenticated user identiy. Will you create some store-procedure (which explicitly take identity info as parameter) for the db side auditing? Then, the ASP.NET side just call the SP to trigger the auditing (when data accessing occurs).
Feedback to us
Microsoft One Code Framework