Problem with Impersonation and Trusted Connections

dmcollie

Hi

I have an app written in ASP.NET 2. It uses trusted database connections and impersonates the current user for database access rather than use the NETWORK SERVICE account. This works fine for all of my database related code.

However I have problems when calling the comparison library in SQL Toolkit. Instead of using the logged in user account it instead uses the NETWORK SERVICE account. It therefore fails because the NETWORK SERVICE account does not have any database rights.

The problem is occurring when I call the Register method of the Database class, having passed in a ConnectionProperty object that was created using the (server, database) constructor.

Any ideas if this is a bug or am I doing something wrong?

Thanks

Dave

Brian Donahue

Hi Dave,

The support for Windows authentication is provided transparently by the Windows operating system, so the Toolkit wouldn't be interfering with it in any way.

In ASP .NET, I believe that the web application's web.config file needs to be altered to turn on impersonation, otherwise any code for passing credentials on will not work. Somewhere in the system.web node, you should add an identity impersonate:

<System.web>
<identity impersonate="true" />...
<!-- or if you want to rule out your impersonation code as the cause, specify a user in the identity element: -- />
<identity impersonate="true" userName="MYDOMAIN\Bob.Dobbs" password="bobspassword" />

You may also want to check your security event logs -- someone may have revoked NETWORK SERVICE's Impersonate a client after authentication user right. If this doesn't cure the problem, let me know...

Brian Donahue

Actually there is something I've missed -- enabling impersonation in web.config will make the web application use the logged-in user's credentials automatically, but you have many more options by impersonating using ASP .NET code and that doesn't necessarily require the identity tag in the web.config.

I still think that setting identity impersonate in the web.config may be a useful troubleshooting step for you though. If that causes the web application to authenticate as the logged-in user, then you know it's probably something wrong with your ASP .NET code.

dmcollie

Brian

I am using <identity impersonate="true"/> in my web.config - that's how I have impersonation enabled.

This works fine for my own code when I connect to MSSQL using System.Data.SqlClient.SqlConnection with a SQL connection like "Data Source=localhost;Initial Catalog=some_db;Trusted_Connection=yes". Instead of connecting to the DB as the NETWORK SERVICE account under which ASP.NET is running it connects as the user that I am logged in as. I know this because I have traced the statements in the SQL Server Profiler and I do not get any security problems being raised by MSSQL.

However when I call RedGate.SQLCompare.Engine.Database.Register with a ConnectionProperties object created with two parameters (the server and databasename taken from the above connection string), MSSQL complains because the Register method is not impersonating the current logged in user but instead using the NETWORK SERVICE account. This implies that the Red Gate code is somehow ignoring the fact that it should be doing impersonation.

Any clues as to how to proceed to tract down what's wrong?

Thanks

Dave

Brian Donahue

Hi Dave,

I don't know. Could it be a threading issue? Maybe any threads spawned by your web application are not inheriting the credentials from the main thread?

dmcollie

Here's an example of the problem. Sample code:

string connectionString = "Data Source=localhost;Initial Catalog=db;Trusted_Connection=yes";
            using (SqlConnection cx = new SqlConnection(connectionString))
            {
                cx.Open();
                string databaseName = cx.Database;
                string serverName = cx.DataSource;
                cx.Close();
                Database dbi = new Database();
                dbi.Register(new ConnectionProperties(serverName, databaseName), Options.Default | Options.ForceColumnOrder);
            }

and here's the exception raised when the Register method is called:

System.Data.SqlClient.SqlException was unhandled by user code
  Message="Cannot open database \"db\" requested by the login. The login failed.\r\nLogin failed for user 'NT AUTHORITY\\NETWORK SERVICE'."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=11
  LineNumber=65536
  Number=4060
  Procedure=""
  Server="localhost"
  State=1
  StackTrace:
    Server stack trace: 
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at _16._1(SqlConnection )
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
       at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
       at _16._1.EndInvoke(IAsyncResult result)
       at _16._1()
       at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
       at iis.utils.db.sync.RedGateTestCase.Demonstrate() in C:\Documents and Settings\Administrator\My Documents\Source\dqars\trunk\web\iis.utils.db.sync.dll\RedGateTestCase.cs:line 21
       at iis.cap.ai.admin.web.Testing.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Source\dqars\trunk\web\iis.cap.ai.admin.web\Testing.aspx.cs:line 24
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Brian Donahue

Have you checked in IIS that the application has Integtated Authentication enabled? If not, of course the logged-in user's credentials will not get passed to IIS.

dmcollie

Yes, Integrated Windows Authentication is turned on for the IIS app. That's why the SqlConnection.Open() statement in the example code I posted worked. What's mystifiying is why in the same block of code the Database.Register() call fails with the authentication error.

I believe that this code example clearly illustrates the problem: calling SqlConnection.Open() impersonates the logged in user fine whereas the call to Database.Register does not. TBH it looks like a bug in the SQL Toolkit libraries.

Cheers,

Dave

Brian Donahue

Hi Dave,

Seriously, there is nothing in the libraries that would change the security context. Windows is not passing the credentials down to the thread that's registering the database.

I've got a reproduction of the problem now, so I'll have a closer look tomorrow. I'm pretty sure this is specific to IIS.

dmcollie

Brian

I'm glad you can reproduce it (although I am sure you are not ;-) ). Let me know if there's anything more I can do to help.

Cheers

Dave

Brian Donahue

Hi Dave,

I think my original suspicion has been proved correct. SQLCompare Engine starts its' own thread, and IIS only passes the impersonation to threads that it controls. The solution is to apply a token to the thread. Here is some code that does the trick. Note that there is a class called 'DBRegistrar' that wraps up the SQL Compare functionality -- this was done because of Toolkit licensing implications that require the Red Gate components be be in a pre-compiled assembly outside of the web application code.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SCImpersonationDll;
using System.Security.Principal;
using System.Threading;


public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        /* Pass our credentials to the DbRegistrar class */
        DbRegistrar dr = new DbRegistrar(WindowsIdentity.GetCurrent().Token);
        /* DbRegistrar will now run the register code using your token */
        Thread t = new Thread(new ThreadStart(dr.RegisterDatabase));
        t.Start();
        /* synchronously wait for the thread to complete */
        t.Join();
        /* Write a list of tables from the registered database */
        Response.Write("Comparison run as " + WindowsIdentity.GetCurrent().Name+"<BR>");
        foreach (string s in dr.Tables)
        {
            Response.Write(s+"<BR>");
        }
    }
}
class DbRegistrar
{
    protected IntPtr token;
    public string[] Tables = null;
    public DbRegistrar(IntPtr token)
    {
        this.token = token;
    }
    public void RegisterDatabase()
    {
        WindowsIdentity wi = new WindowsIdentity(token);
        WindowsImpersonationContext ctx = wi.Impersonate();
        SCImpersonationDll.DatabaseEngine e = new SCImpersonationDll.DatabaseEngine();
        Tables=e.GetTables("BRIAN","WidgetStaging");

    }

}

For reference, this is the code used to create the wrapper dll around SQL Compare Engine:

using System;
using System.Collections.Generic;
using System.Text;
using RedGate.SQLCompare.Engine;

namespace SCImpersonationDll
{
    public class DatabaseEngine
    {
        public string[] GetTables(string server,string database) {
            Database d = new Database();
            d.Register(new RedGate.SQLCompare.Engine.ConnectionProperties(server, database), Options.Default);
            int i=0;
            string[] tables = new string[d.Tables.Count];
            foreach (Table t in d.Tables)
            {
                tables[i]=t.FullyQualifiedName;
                i++;
            }
        d.Dispose();
            return tables;
        }
    }
}

I hope that this works for you!

Brian Donahue

Hi,

On second thought, this doesn't seem to work either. The SQL Toolkit uses an AsyncDelegate to register the database and the impersonation is not inherited from the application pool. The only way around this I can see is to specifically change the SQL Compare Engine so that the AsyncDelegate supports IIS impersonation.

All I can think to do is call the command-line version of SQL Compare (sqlcompare.exe) to create a snapshot file and then do all of the comparisons in your ASP .NET code from snapshot. If you use an external process you can call it using your impersonated credentials using Process.Start.

dmcollie

Brian

What're the chances of getting the SQL Compare libraries to actually exhibit the 'expected' behaviour under ASP.NET?

Thanks

Dave

Brian Donahue

Sorry, but I can't comment on the feasability -- I think any way of working around it would be an exploit or require a pretty thorough redesign of the SQL Compare engine. IIS is not passing along the impersonated credentials to the AsyncDelegate thread that SQL Compare uses to register the database, so the engine would either need to do everything on the same thread or hack IIS somehow to make it work.

gromm

Has this been resolved? I am bumping up against the same issue of impersonation not working properly when the sdk is used in a web api. I have tried to put the app in its own app pool and have it run by a user with access everywhere, and it still does not work. Any thoughts?

Andrius

It's 2017 and Red Gate did not fix this but... there is a workaround. ASP.NET has a configuration setting that "Specifies that the Windows identity always flows across asynchronous points, regardless of how impersonation was performed" named "alwaysFlowImpersonationPolicy" as described in https://docs.microsoft.com/en-us/dotnet/framework/configure-apps/file-schema/runtime/alwaysflowimpersonationpolicy-element.

Basically, in aspnet.config (which can be found under <Windows Folder>\Microsoft.NET\Framework\vx.x.xxxx directory) you would need to set these two values:
<legacyImpersonationPolicy enabled="false"/>
<alwaysFlowImpersonationPolicy enabled="true"/>

As an alternative, you can set this setting per application pool as described in https://weblogs.asp.net/owscott/setting-an-aspnet-config-file-per-application-pool