locked
Access database using client credentials RRS feed

  • General discussion

  • I have created a small wcf project that comprises the following

     TZ_PI_Interface_WCF - This is a WCF service hosted in IIS
     TZ_PI_Interface_Client - This is a test client

    The service is running in as much that my client communicates with it and executes code in the service.

    My client app executes the GetProcessData_GetSearch_Client_Detail of my service.  This method should go off to a database and return some data to the client.
    For the purposes of this test I am catching exceptions in the GetProcessData_GetSearch_Client_Detail method and returnind the error message as a piece of data.

    When the GetProcessData_GetSearch_Client_Detail is executed, a connection should be made the a database and data should be returned to the client
    The results I get depend on my connection string.
    These are the scenarios I am trying along with the results of executing GetProcessData_GetSearch_Client_Detail

    1) Local DB Test using Integrated Security
     Connection Error: System.Data.SqlClient.SqlException: Login failed for user 'N075650\ASPNET'

    2) Local DB Test using SQL Login
     Data correctly returned

    3) Remote DB using Integrated Security
     Connection Error: System.Data.SqlClient.SqlException: Login failed for user '(null)'

    4) Remote DB using SQL Login
     Data correctly returned


    In the live environment I will have the service hosted on an IIS server (6.0) and the database will be hosted on a remote sql server (2005). 
    I will need to used Integrated Security to connect to database (we have kerberos relationship between IIS server and SQL Server)


    Ive been trying (for a couple of days now!) to get this to work by modifying the <system.serviceModel> sections of service and client config files but I am not having much luck.
    Im sure that what I need to do is achievable (hopefully by config settings).
    I'd really appreciate some step by step assisstance on this

    Thanks in advance

    Terry

     

    Code and COnfig files below

    Client app.config
    =================
    <configuration>
     <system.serviceModel>
      <behaviors>
       <endpointBehaviors>
        <behavior name="NewBehavior">
         <clientCredentials>
          <windows allowedImpersonationLevel="Impersonation" />
         </clientCredentials>
        </behavior>
       </endpointBehaviors>
      </behaviors>
      <client>
       <endpoint address="http://localhost/TZ_WebServices/ExternalData/core.svc"
        behaviorConfiguration="NewBehavior" binding="wsHttpBinding" bindingConfiguration="clientConfig"
        contract="TZ_CONTRACT.ExternalData.IServiceContract" name="ExternalData.RBKC" />
      </client>
      <bindings>
       <wsHttpBinding>
        <binding name="clientConfig">
         <security mode="Message">
          <message clientCredentialType="Windows"/>
         </security>
        </binding>
       </wsHttpBinding>
      </bindings>
     </system.serviceModel>
    </configuration>


    Service Web.config
    ==================
    <?xml version="1.0"?>

    <configuration>

     <appSettings>
      <!--Local DB Test using Integrated Security-->
      <!--<add key="PI_Connection" value="Data Source=(local);Initial Catalog=TerryTest;Integrated Security=True"/>
      <add key="PI_SQL" value="Select * FROM Person"/>-->

      <!--Local DB Test using SQL Login-->
      <!--<add key="PI_Connection" value="Data Source=(local);Initial Catalog=TerryTest;User ID=TZTest;Password=TZTest"/>
      <add key="PI_SQL" value="Select * FROM Person"/>-->

      <!--Remote DB using Integrated Security-->
      <!--<add key="PI_Connection" value="Data Source=RemoteServer;Initial Catalog=TerryTest;Integrated Security=True"/>
      <add key="PI_SQL" value="Select * FROM Person"/>-->

      <!--Remote DB using SQL Login-->
      <add key="PI_Connection" value="Data Source=RemoteServer;Initial Catalog=TerryTest;User ID=TZTest;Password=TZTest"/>
      <add key="PI_SQL" value="Select * FROM Person"/>
     </appSettings>

     <system.serviceModel>
      <services>
       <service behaviorConfiguration="thisDebug" name="TZ_PI_Interface_WCF.ExternalData">
        <endpoint address="" behaviorConfiguration="" binding="wsHttpBinding"
         bindingConfiguration="serviceConfig" contract="TZ_CONTRACT.ExternalData.IServiceContract" />
       </service>
      </services>
      <bindings>
       <wsHttpBinding>
        <binding name="serviceConfig">
         <security mode="Message">
          <message clientCredentialType="Windows"/>
         </security>
        </binding>
       </wsHttpBinding>
      </bindings>
      <behaviors>
       <serviceBehaviors>
        <behavior name="thisDebug">
         <serviceDebug includeExceptionDetailInFaults="true" />
         <serviceMetadata httpGetEnabled="true" />
        </behavior>
       </serviceBehaviors>
      </behaviors>
     </system.serviceModel>
    </configuration>

     

    TZ_PI_Interface_Client
    ======================
     Function WCF() As String

            'Dim pd As New TZ_CONTRACT.AddressLookup.clsProcessData_GetAddressLookupList
            Dim crit As clsSearch_Client_DetailCriteria = clsSearch_Client_DetailCriteria.NewSearch_Client_DetailCriteria
            crit.Forename = "WCF"
            Dim pd As clsProcessData_GetSearch_Client_Detail
            pd = clsProcessData_GetSearch_Client_Detail.GetProcessData_GetSearch_Client_Detail(crit)

            'Initialise the client proxy object
            Dim cp As New TZ_CONTRACT.ExternalData.clsClientProxy("ExternalData.RBKC")

            Dim sb As New System.Text.StringBuilder

            For Each result As clsPersonKey In cp.GetSearch_Client_Detail(pd)
                sb.AppendLine(result.ExternalDataID)
            Next

            Return sb.ToString

        End Function
     

    TZ_PI_Interface_WCF Code
    ========================
        Public Function GetSearch_Client_Detail(ByVal processData As TZ_CONTRACT.ExternalData.clsProcessData_GetSearch_Client_Detail) As TZ_CONTRACT.ExternalData.clsPersonKey() Implements TZ_CONTRACT.ExternalData.IServiceContract.GetSearch_Client_Detail
            Dim lst As New List(Of clsPersonKey)

            Dim strConnect As String = AppSettings("PI_Connection")
            lst.Add(clsPersonKey.NewPersonKey(-1, "Pi_Connection: " & strConnect, enuDataSource.ExternalClient))

            Dim callerWindowsIdentity As System.Security.Principal.WindowsIdentity = System.ServiceModel.ServiceSecurityContext.Current.WindowsIdentity
            lst.Add(clsPersonKey.NewPersonKey(-1, "callerWindowsIdentity: " & callerWindowsIdentity.Name, enuDataSource.ExternalClient))

         
            Try
                Using cnn As New SqlClient.SqlConnection(AppSettings("PI_Connection"))
                    cnn.Open()
                    Using cmm As New SqlClient.SqlCommand
                        cmm.Connection = cnn
                        cmm.CommandType = CommandType.Text
                        cmm.CommandText = AppSettings("PI_SQL")

                        Using dr As SqlClient.SqlDataReader = cmm.ExecuteReader

                            While dr.Read
                                lst.Add(clsPersonKey.NewPersonKey(dr("PersonNumber"), dr("Title") + " " + dr("Forename") + " " + dr("Surname"), enuDataSource.ExternalClient))
                            End While
                        End Using
                    End Using
                End Using

            Catch ex As Exception
                lst.Add(clsPersonKey.NewPersonKey(-1, "Connection Error: " & ex.ToString, enuDataSource.ExternalClient))

            End Try


            Return lst.ToArray

        End Function

     

     

    My Development configuration
    ============================
    Configuration: win xp pro sp3, vs2005, .net 3.0, iis 5.1

     

     

    Wednesday, September 8, 2010 12:01 PM

All replies

  • When you authenticate to the sql server, do you always use the same username or depending on the client who accesses the WCF service you want to use the client name?

    In the former case the question does not particularly relate to WCF - are you able to create any client application (even winform) that can access the DB from that server?


    http://webservices20.blogspot.com/
    WCF Security, Interoperability And Performance Blog
    Wednesday, September 8, 2010 12:26 PM
  • When you authenticate to the sql server, do you always use the same username or depending on the client who accesses the WCF service you want to use the client name?

    In the former case the question does not particularly relate to WCF - are you able to create any client application (even winform) that can access the DB from that server?


    http://webservices20.blogspot.com/
    WCF Security, Interoperability And Performance Blog


    I would want to connect to the database using the windows credentials of the user who is using the client application. 

    I am able to connect to database on local and remote sql server direct from a client application (winform) using any of the connection strings in initial posting.  The problem is that when I execute code in WCF hosted service, the connection to the database is made under the user MyMachine\ASPNET rather than MyDomain\Terry.  In the local/integrated scenario (scenarion 1 above), this causes an exception because MyMachine\ASPNET does not have access to the database.

    Im convinced that my problem is wcf related because I cant seem to tell wcf to execute under as the user who is using the client application.

    In the following code in TZ_PI_Interface_WCF, callerWindowsIdentity.Name contains MyDomain\Terry.  My question is 'How do I get the credentials of this user passed on to connection to database when using wcf?

    Dim callerWindowsIdentity As System.Security.Principal.WindowsIdentity = System.ServiceModel.ServiceSecurityContext.Current.WindowsIdentity
            lst.Add(clsPersonKey.NewPersonKey(-1, "callerWindowsIdentity: " & callerWindowsIdentity.Name, enuDataSource.ExternalClient))

    Wednesday, September 8, 2010 1:55 PM
  • check out this article or any other on the subject of impersonation and delegation in WCF:

    http://msdn.microsoft.com/en-us/library/ms730088.aspx

    if the sql server is on the same machine as the WCF service you need impersonation. Otherwise you need delegation which is harder.


    http://webservices20.blogspot.com/
    WCF Security, Interoperability And Performance Blog
    Wednesday, September 8, 2010 9:26 PM
  • check out this article or any other on the subject of impersonation and delegation in WCF:

    http://msdn.microsoft.com/en-us/library/ms730088.aspx

    I had briefly looked here before and thought that there must be an easier way

    if the sql server is on the same machine as the WCF service you need impersonation. Otherwise you need delegation which is harder.

    I have been developing for approx 12 years and I must say that in all that time, I have never experienced a scenario where IIS and SQL Server sit on the same box except in very small organisations and in development environments.  Considering this and bearing in mind that "best practice" as advocated by Microsoft is to use Integrated Security if possible, why have the team not made it easier to use delegation in wcf easier? 

    Thanks anyway - I will soldier on :-)

    Terry

    Thursday, September 9, 2010 9:58 AM
  • And here's the official documentation:

    http://msdn.microsoft.com/en-us/library/ff647248.aspx#ImpersonationDelegation2


    http://webservices20.blogspot.com/
    WCF Security, Interoperability And Performance Blog
    Thursday, September 9, 2010 11:33 AM
  • Hi Terry,

    Did you solve the problem?

     

    Thanks


    michaelfallas@gmail.com Michael Hidalgo Fallas
    Tuesday, February 8, 2011 6:56 PM