2

I have a SL4 app that uses WCF to communicate with a backend SQL Server 2008 database. One of the WCF services needs to connect to the database with a dedicated system account due to the database permissions required by the stored procedure that is called. I have attempted to implement a solution using impersonation within the service code e.g.

int result = LogonUser(userName, domain, password,
    LOGON32_LOGON_NETWORK, LOGON32_PROVIDER_DEFAULT, out _token);

if (result > 0)
{
    ImpersonateLoggedOnUser(_token);
    //Code here to call NHibernate data access code
}

My connection string for this service is:

<add name="MyConnection" connectionString="Data Source=servername\instance;Initial Catalog=MyDatabase;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>

However, the data access routine is still failing with the following message:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

The impersonation is being ignored in the database connection. Any ideas?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Myles J
  • 2,839
  • 3
  • 25
  • 41
  • 1
    You should always check the return value of ImpersonateLoggedOnUser. Only if the call succeeded should you run the code which is intended to be run in the impersonated context. If the call failed the error code (`new Win32Exception().NativeErrorCode`) will tell you why. You should also have a try block around the impersonation code and always call RevertToSelf in the finally block. – Chris Dickson May 11 '11 at 10:32
  • I had all of this. I kept my code sample short just to get to the main issue. Thanks anyway. – Myles J May 11 '11 at 10:51
  • can you please unflag my answer as answer? I think I did not read the question correctly. SOrry. – Aliostad May 11 '11 at 11:49
  • @Aliostad - Done. Sorry if there has been any confusion with this question. All I wanted to do was to use a fixed domain account to access our database from within a WCF service. My solution set out below is now working fine. – Myles J May 11 '11 at 11:55

2 Answers2

2

Change LOGON32_LOGON_NETWORK to LOGON32_LOGON_NETWORK_CLEARTEXT in your call to LogonUser.

This caches the logon credentials in the local security provider, which should enable a successful SSPI handshake with SQL Server.

Chris Dickson
  • 11,964
  • 1
  • 39
  • 60
0

I've actually managed to get this to work by getting rid of the impersonation API code and adding the following to my web.config:

  <location path="Services/MyServiceThatNeedsHigherPermissions.svc">
    <system.web>
      <identity impersonate="true" userName="domain\MyAccountWithElevatedPermissions" password="******"/>
    </system.web>
  </location>

The service runs under the context of my dedicated system account and connects to SQL using the same context.

Myles J
  • 2,839
  • 3
  • 25
  • 41
  • 1
    But that's not really impersonating anybody, it's running as a specific account you specified. – Tridus May 11 '11 at 10:01
  • OK but this is the end result that I was looking for. – Myles J May 11 '11 at 10:23
  • OP wants the users to authenticate against the sql server using their own credentials, hence all the impersonation saga... this is setting a fixed account. – Aliostad May 11 '11 at 11:21
  • @aliostad: No, OP wants to use a fixed account to access the database, as he said in his question. – Chris Dickson May 11 '11 at 11:32
  • @Chris you are right. The question seems to have been changed! – Aliostad May 11 '11 at 11:35
  • @Tridus: Actually this is technically impersonation (the clue is in `impersonate=true`) since it is specifying that the service will run in a context different to the worker process identity. It is asking ASP.NET to do more or less what the OP started doing in his own code, but covering the entire service implementation, not just the data access code. If running the entire service code in this context is appropriate, this is a neater solution than hand coding the login and impersonation. – Chris Dickson May 11 '11 at 12:14