IIS Hosted WCF Service & SQL Queries Using Windows Authentication

Question

I'm fairly new to WCF but I have a WCF Service hosted in IIS that has several queries to our SQL Server. I am consuming the WCF service with a WPF application. What I'm trying to do is allow windows authentication to pass from the WPF Client, To The WCF Service, To The SQL Server so that the SQL Queries are executed as the clients user. I've been trying to configure the website and host in various ways with no luck so far.

On my WCF Service website, I have Anonymous Authentication=true(for MEX), ASP.NET Impersonation=true and Windows Authentication=true.

In my WCF Service Web.config:

<configuration>
  <system.web>
    <customErrors mode="Off"/>
    <authentication mode="Windows"/>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </assemblies>
    </compilation>
  </system.web>
  <system.serviceModel>
    <bindings>
      <wsHttpBinding>
        <binding maxReceivedMessageSize="5000000" name="WindowsSecurity">
          <readerQuotas maxDepth="200"/>
          <security mode="Transport">
            <transport clientCredentialType="Windows" />
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>
    <services>
      <service name="ADATrackingService" behaviorConfiguration="ServiceBehavior">
        <endpoint address="" binding="wsHttpBinding" bindingConfiguration="WindowsSecurity"
          name="wsHttpEndpoint" contract="IADATrackingService" />
        <endpoint address="mex" binding="mexHttpsBinding" name="MexHttpsBindingEndpoint"
          contract="IMetadataExchange" />
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="ServiceBehavior">
          <serviceMetadata httpGetEnabled="false" httpsGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="true" />
          <serviceAuthorization impersonateCallerForAllOperations="true" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true" />
  </system.webServer>
  <connectionStrings>
    <add name="ADATrackingEntities" connectionString="metadata=res://*/EntityModel.ADATrackingModel.csdl|res://*/EntityModel.ADATrackingModel.ssdl|res://*/EntityModel.ADATrackingModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MYSERVER;initial catalog=ADATracking;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Then in my WPF client App.Config I have:

<configuration>
    <system.serviceModel>
      <behaviors>
        <endpointBehaviors>
          <behavior name="WindowsAuthentication">
            <clientCredentials>
              <windows allowedImpersonationLevel="Delegation"/>
            </clientCredentials>
          </behavior>
        </endpointBehaviors>
      </behaviors>
        <bindings>
            <wsHttpBinding>
                <binding name="wsHttpEndpoint" closeTimeout="00:01:00" openTimeout="00:01:00"
                    receiveTimeout="00:10:00" sendTimeout="00:01:00" bypassProxyOnLocal="false"
                    transactionFlow="false" hostNameComparisonMode="StrongWildcard"
                    maxBufferPoolSize="524288" maxReceivedMessageSize="5000000"
                    messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true"
                    allowCookies="false">
                    <readerQuotas maxDepth="200" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <reliableSession ordered="true" inactivityTimeout="00:10:00"
                        enabled="false" />
                    <security mode="Transport">
                        <transport clientCredentialType="Windows" proxyCredentialType="None"
                            realm="" />
                        <message clientCredentialType="Windows" negotiateServiceCredential="true" />
                    </security>
                </binding>
            </wsHttpBinding>
        </bindings>
        <client>
            <endpoint address="https://MyService.svc"
                binding="wsHttpBinding" behaviorConfiguration="WindowsAuthentication" bindingConfiguration="wsHttpEndpoint"
                contract="ADATrackingService.IADATrackingService" name="wsHttpEndpoint">
                <identity>
                    <servicePrincipalName value="host/MyServer.com" />
                </identity>
            </endpoint>
        </client>
    </system.serviceModel>
</configuration>

My service calls are just returning simple queries from SQL using the metadata for allowing impersonation. Every time I run the client and call something from my service i'm just getting an error opening the data connection for "NT Authority/ANONYMOUS LOGIN" even with AnonymousAuthentication=false set in IIS??? Any help would be greatly appreciated. Thanks!

[OperationBehavior(Impersonation = ImpersonationOption.Required)]
public List<IndividualDisability> GetIndividualDisabilities()
{
    WindowsIdentity callerWindowsIdentity = ServiceSecurityContext.Current.WindowsIdentity;
    if (callerWindowsIdentity == null)
    {
        throw new InvalidOperationException
         ("The caller cannot be mapped to a Windows identity.");
    }
    using (callerWindowsIdentity.Impersonate())
    {
        using (var context = new ADATrackingEntities())
        {
            return context.IndividualDisabilities.OfType<IndividualDisability>().Include("ADACode").Include("Individual").Include("Disability").ToList();
        }
    }
}

ccorrin · Accepted Answer · 2012-01-26 19:03:05Z

5

Well, after browsing around some more today. I've finally got it working! The issue was that in active directory, I needed to allow Delegation to the SQL Server box. There is a setting in AD that you have to set on the web server box to allow it to delegate to the SQl Service on your SQl Server box on port 1433. I also had to make sure I was setup for kerebos authentication on the webserver. This blog post explained my situation exactly and helped me get it working from start to finish:

ASP.Net Impersonation

answered Jan 26, 2012 at 19:03

ccorrin

5021 gold badge5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CodeWarrior · Accepted Answer · 2012-01-24 15:49:14Z

0

In IIS, have you explicitly removed Anonymous authentication? Do the following:

Open the IIS Manager.
Navigate to your WCF service application.
In the Features View, Under IIS, click Authentication.
Remove any authentication scheme except Windows Authentication. (Anonymous is enabled by default.)

To help ensure that your WPF application is not interfering in any way, first test with wcftestclient.

Open a Developers Command Window (Start Menu>Programs>Microsoft Visual Studio 2010>Visual Studio Tools>Visual Studio Command Prompt (2010))
wcftestclient https://url.to/myservice.svc

answered Jan 24, 2012 at 15:49

CodeWarrior

7,4787 gold badges54 silver badges80 bronze badges

10 Comments

ccorrin Over a year ago

Thanks for the reply. I think ive got everything configured properly in IIS and the service. When running with the WCF test client im now getting error: "Either a required impersonation level was not provided, or the provided impersonation level is invalid."

CodeWarrior Over a year ago

Ah ha! Have you tried hard coding your impersonation just for the heck of it? Add the following to web.config to be sure that from the service to the SQL server you are good: <identity impersonate="true" userName="contoso\Jane" password="pass"/>

ccorrin Over a year ago

I'm getting an error even when hard coding the values in the web.config. "Login failed to SQL for NT Authority/Anonymous Login". I'm thinking there may be something I need to configure on my SQL box??

ccorrin Over a year ago

After double checking my config, im now getting a new error. Login failed to SQL for mydomain/machinename$ and not the mydomain/username i entered into the web.config to impersonate. I wonder why its trying to connect as the machine name to SQL Server??

ccorrin Over a year ago

For clarification my Webserver & SQL Servers are on different machines.

|

Collectives™ on Stack Overflow

IIS Hosted WCF Service & SQL Queries Using Windows Authentication

2 Answers 2

Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related