asp.net form authentication impersonate with SQL Server

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

Re: asp.net form authentication impersonate with SQL Server

m_fuad67
with a desired account for each AD Users

put

<identity impersonate=true

in web.config
http://msdn.microsoft.com/en-us/library/72wdk8cc%28v=vs.71%29.aspx

Re: asp.net form authentication impersonate with SQL Server

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

Re: asp.net form authentication impersonate with SQL Server

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.

Re: asp.net form authentication impersonate with SQL Server

Hi Steven,

I did the impersonation using advapi32.dll but the sql server connection stril still taking the NT Service Username.

Re: asp.net form authentication impersonate with SQL Server

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.

Re: asp.net form authentication impersonate with SQL Server

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.

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
            {
            }

Re: asp.net form authentication impersonate with SQL Server

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?

Re: asp.net form authentication impersonate with SQL Server

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???

Re: asp.net form authentication impersonate with SQL Server

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).