Problem with Impersonation and Trusted Connections

Forum for users of SQL Toolkit 3,4,and 5

Problem with Impersonation and Trusted Connections

Postby dmcollie » Wed Dec 05, 2007 3:29 pm

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
dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Sun Dec 09, 2007 10:05 am

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:
Code: Select all
<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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Dec 10, 2007 11:52 am

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

I am using the web.config impersonation setting

Postby dmcollie » Tue Dec 11, 2007 3:01 pm

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
dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Tue Dec 11, 2007 3:31 pm

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?
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Example of the problem

Postby dmcollie » Wed Dec 12, 2007 4:56 pm

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

Code: Select all
            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:

Code: Select all
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)

dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Wed Dec 12, 2007 5:22 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby dmcollie » Wed Dec 12, 2007 5:32 pm

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
dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Wed Dec 12, 2007 7:20 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby dmcollie » Thu Dec 13, 2007 9:32 am

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
dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Thu Dec 13, 2007 4:30 pm

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.
Code: Select all
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:
Code: Select all
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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Fri Dec 14, 2007 10:58 am

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby dmcollie » Tue Jan 08, 2008 1:59 pm

Brian

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

Thanks

Dave
dmcollie
 
Posts: 6
Joined: Wed Dec 05, 2007 3:20 pm

Postby Brian Donahue » Tue Jan 08, 2008 4:42 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby gromm » Thu May 08, 2014 12:08 pm

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?
gromm
 
Posts: 1
Joined: Thu May 08, 2014 12:06 pm
Location: United States


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests