How To - Use SQL Role Provider with Username Authentication in WCF calling from Windows Forms

- J.D. Meier, Jason Taylor, Prashant Bansode, Carlos Farre, Madhu Sundararajan, Steve Gregersen

Applies to

  • Microsoft® Windows Communication Foundation (WCF) 3.5
  • Microsoft Visual Studio® 2008

Summary

This How To article walks you through the process of using username authentication over wsHttpBinding to authenticate your users against a Microsoft SQL Server Roles Provider. The article shows you how to configure the Role Provider, configure WCF, and test the service with a sample WCF client. Use of the SQL Server Roles Provider requires that you first set up and use the SQL Server Membership provider.

Contents

  • Objectives
  • Overview
  • Summary of Steps
  • Step 1 – Create a WCF Service with Username Authentication using SQL Membership Provider
  • Step 2 – Create a Role Store for SQL Role Provider
  • Step 3 – Grant Access Permission to WCF Service Process Identity
  • Step 4 – Enable and Configure Role Provider
  • Step 5 – Create and Assign Users to the Roles
  • Step 6 – Implement Declarative role-based security
  • Step 7 – Create a Test Client
  • Step 8 – Add WCF Service Reference to the Client
  • Step 9 – Configure the Client to Set RevocationMode to NoCheck
  • Step 10 – Test the Client and WCF Service
  • Additional Resources

Objectives

  • Configure the SQL Server Membership Provider.
  • Configure the SQL Server Roles Provider.
  • Create a WCF service hosted in Microsoft Internet Information Services (IIS).
  • Create and configure a certificate for the service.
  • Expose the WCF service through wsHttpBinding.
  • Call the service from a test client.

Overview

Username authentication is suited for scenarios in which your users do not have domain credentials. In the scenario described in this How To article, users are stored in SQL Server and are authenticated against the SQL Server Membership Provider and then authorized against the SQL Server Roles Provider. The wsHttpBinding is used in order to provide support for message-based security, reliable messaging, and transactions, while also allowing the possibility that legacy clients can consume the service. WCF message security is used to support the scenario in which there may be intermediaries inspecting the message before final delivery. In general, you should always use transport security unless you need the additional flexibility that message security affords you.

In order to use the SQL Server Membership Provider, you will first create a user store and populate it with your users. You will then configure the membership store to allow the WCF service process identity to have access. You will set the clientCredentialType attribute to UserName on the wsHttpBinding in order to configure the WCF service to use UserName authentication. You will then install a certificate on the server and configure it for WCF so that messages sent between client and server are encrypted. You will create a role store and populate it with your users and then configure the role store to allow the WCF process identity to have access. You will use the PrincipalPermissionAttribute in your WCF service code to specify which roles are allowed to access specific operations in your WCF service. For test purposes, you will set the revocationMode attribute to NoCheck so that the temporary test certificate works properly.

Summary of Steps

  • Step 1 – Create a WCF Service with Username Authentication using SQL Membership Provider
  • Step 2 – Create a Role Store for SQL Role Provider
  • Step 3 – Grant Access Permission to WCF Service Process Identity
  • Step 4 – Enable and Configure Role Provider
  • Step 5 – Create and Assign Users to the Roles
  • Step 6 – Implement Declarative role-based security
  • Step 7 – Create a Test Client
  • Step 8 – Add WCF Service Reference to the Client
  • Step 9 – Configure the Client to Set RevocationMode to NoCheck
  • Step 10 – Test the Client and WCF Service

Step 1 – Create a WCF Service with Username Authentication using SQL Membership Provider

In this step you will create a WCF Service with Username Authentication using SQL Membership Provider and message security.
  1. Create a user store for the SQL Membership provider with the following command:
aspnet_regsql -S .\SQLExpress -E -A m
  1. Grant the WCF service process identity permission to the Aspnetdb database. You can accomplish this by creating a new SQL Server login for the WCF process identity (Network Service on Windows 2003 or ASPNET on Windows XP), create a new user in the Aspnetdb database, and then add the user to the aspnetMembershipFullAccess database role.
  2. Create a sample WCF service in Visual Studio 2008 by creating a new web site project and selecting the WCF Service project template.
  3. Configure the WCF service to use username authentication and message security by using the WCF Configuration Editor.
  4. Configure the SQL Server Membership Provider to use username authentication by adding a connection string to the database in the service’s web.config file and then adding a membership element to specify usage of the SQL Membership Provider.
  5. Create and install a temporary certificate for the service.
  6. Configure WCF to use the certificate by adding modifying the Service Credentials element in the WCF Configuration Editor.
For more information on these steps, see How To - Use Username Authentication with the SQL Membership Provider and Message Security in WCF from Windows Forms and follow steps 1 through 7.

Step 2 – Create a Role Store for SQL Role Provider

The SQL Server role provider stores user information in a SQL Server database. You can create your SQL Server role store manually by using Aspnet_regsql.exe from the command line.

From a Visual Studio 2008 command prompt, run the following command.

aspnet_regsql -S .\SQLExpress -E -A r
  • -S specifies the server, which is (.\SQLExpress) in this example.
  • -E specifies to use Windows authentication to connect to SQL Server.
  • -A r specifies to add only the role provider feature.
  • For a complete list of the commands, run Aspnet_regsql /?

Step 3 – Grant Access Permission to WCF Service Process Identity

In Step 1 you granted access to the aspnetdb database, in this step you will add the Network Service database user to the aspnetRolesFullAccess role. You can do this by using Enterprise Manager or you can run the following script in SQL Query Analyzer.

You can perform these steps by using Enterprise Manager or you can run the following script in SQL Query Analyzer.
-- Add user to database role
USE aspnetdb
GO
sp_addrolemember 'aspnet_Roles_FullAccess', 'Network Service'

Note:
  • If you are running on Microsoft Windows® XP, add the ASPNET database user instead of Network Service as the IIS process runs under the ASPNET account in Windows XP.
  • If you don’t have Enterprise Manager or Query Analyzer you can use “Microsoft SQL Server Management Studio Express” available

Step 4 – Enable and Configure Role Provider

In this step you will configure the use of the role provider in your WCF service.
  1. In the web.config file, verify that you have a connection string similar to the following:
<connectionStrings>
  <add name="MyLocalSQLServer"
       connectionString="Initial Catalog=aspnetdb;
      data source=.\sqlexpress;Integrated Security=SSPI;" />
</connectionStrings>
  1. Add a <roleManager> element inside the <system.web> element as shown in the following example. Note the use of the <clear/> element, prevents the default provider from being loaded and then never used.
...
<system.web>
  <roleManager *enabled="true"* defaultProvider="MySqlRoleProvider" >
    <providers>
      <clear/>
      <add name="MySqlRoleProvider"
           connectionStringName="MyLocalSQLServer"
           applicationName="MyAppName"
           type="System.Web.Security.SqlRoleProvider" />
    </providers>
  </roleManager>
</system.web>
...
  1. Save the Web.Config file, else the changes might get lost during executing the following steps.
  2. Right click on the Web.config file of the WCF Service and choose the option, Edit WCF Configuration...
  3. If you do not see the Edit WCF Configuration option, click the Tools menu and select WCF Service Configuration Editor. Close the WCF Service Configuration Editor tool that appears. The option should now appear on the web.config context menu.
  4. In the configuration editor, expand the Advanced node, then expand the Service Behaviors folder.
  5. Select the default behavior created "ServiceBehavior".
  6. In the Behavior: ServiceBehavior section, click the Add button.
  7. In the Adding Behavior Element Extension Sections dialog box select serviceAuthorization and click Add button.
  8. In the Configuration section select serviceAuthorization option, under Service Behaviors.
  9. Set the principalPermissionMode attribute to UseAspNetRoles choosing from the drop down.
  10. Set the roleProviderName attribute to “MySqlRoleProvider” which you created above.
  11. On the configuration editor dialog, go to the File menu and select Save.
  12. In Visual Studio, verify your configuration. The configuration should look as follows.
….
<behavior name="ServiceBehavior">
  <serviceMetadata httpGetEnabled="true" />
    <serviceDebug includeExceptionDetailInFaults="false" />
       ….
       <*serviceAuthorization* *principalPermissionMode="UseAspNetRoles" 
             roleProviderName="MySqlRoleProvider*" />
       ….
 </behavior>
….

Step 5 – Create and Assign Users to the Roles

In this step you will create roles for your application and assign users to those roles by using the ASP.NET Web Site Configuration Tool.
  1. In the solution explorer, choose the WCF service project, and from the Website menu select ASP.NET Configuration.
  2. In the ASP.NET Web Site Administration Tool page, select the Security tab, and click the Select authentication type link.
  3. On the page displayed select the From the internet radio button and click the Done button.
  4. Then click the Create user link.
  5. In the Create User page give the details of the user you want to create in the SQL store and click the Create User button. If successful it will create a new user.
  6. Then click the Create or Manage roles link.
  7. Enter the New role name for example “Managers” and click the Add Role button. If successful it will create a new role.
  8. In the Roles creation page click the Manage link choose the user created in previous steps and assign it to the role by checking the User Is In Role checkbox.

Step 6 – Implement Declarative role-based security

In this step you will provide authorized access to the GetData method only for users in the Managers role
  1. Open the Service.cs file and add statement for using the System.Security.Permissions namespace

using System.Security.Permissions;
  1. Add the PrincipalPermissionAttribute to authorize users on Managers role with the SecurityAction as Demand to the GetData method.
[PrincipalPermission(SecurityAction.Demand, Role="Managers")]
public string GetData(int value)
{
	return string.Format("You entered: {0}", value);
}

Step 7 – Create a Test Client

In this step, create a Windows Form application to test WCF Service.
  1. Right-click your Solution, click Add and then click New Project.
  2. In the Add New Project dialog box, select Windows Application from the Templates section.
  3. In the Name field, type Test Client and click Ok button. It will create a windows forms application.

Step 8 – Add WCF Service Reference to the Client

In this step, you add a reference to your WCF Service.
  1. Right-click your Client project and select Add Service Reference.
  2. In the Add Service Reference dialog box, set the url to your WCF Service, for example http://localhost/WCFTestService/Service.svc and click the Go button
  3. In the Namespace field, change ServiceReference1 to WCFTestService and Click Ok button.
  4. In your Client project, a reference to WCFTestService should appear beneath Service References.

Step 9 – Configure the Client to Set RevocationMode to NoCheck

This step is required because you are using temp service certificates as part of Step 1.
  1. Right click client configuration file (App.config) file and select Edit WCF Configuration.
  2. In the configuration editor, expand the Advanced node and select New Endpoint Behavior Configuration
  3. Click the Add button.
  4. In the Adding Behavior Element Extension Sections dialog box, select clientCredentials and click Add button.
  5. Expand the clientCredentials node and then expand the serviceCertificate node and then select authentication below it.
  6. Set the RevocationMode attribute to NoCheck, choosing from the drop down.
  7. In the configuration editor, expand the Client node and then expand the Endpoints node and select the WsHttpEndpoint node.
  8. Set the BehaviorConfiguration attribute to NewBehavior, choosing from the drop down, this is the endpoint behavior you just created.
  9. On the configuration editor dialog, go to the File menu and select Save.
  10. In Visual Studio, verify your configuration. The configuration should look as follows.
…
<behaviors>
    <endpointBehaviors>
        <behavior *name="NewBehavior"*>
            <clientCredentials>
                <serviceCertificate>
                    <authentication *revocationMode="NoCheck"* />
                </serviceCertificate>
            </clientCredentials>
        </behavior>
    </endpointBehaviors>
</behaviors>
<client>
    <endpoint address="http://<<fully qualified machine name>>/WCFTestService/Service.svc"
        *behaviorConfiguration="NewBehavior"* binding="wsHttpBinding"
        bindingConfiguration="wsHttpEndpoint" contract="WCFTestService.IService"
        name="wsHttpEndpoint">
        <identity>
            <certificate encodedValue="SomeEncodeValue" />
        </identity>
    </endpoint>
</client>
…

Important - This should be done in development only, when using the makecert utility for creating the certificates. In production environment, don't overwrite the revocationMode settings.

Note - If you have your client app on a separate machine, you will have to install the Root Authority certificate created in Step 6 on your client machine as well.

Step 10 – Test the Client and WCF Service

In this step you access the WCF Service as a legacy ASMX Web Service and make sure it works.
  1. In your Client project, drag a Button control to your Form.
  2. Double-click the Button control to show the code behind.
  3. In the code behind of the button click, create an instance of the proxy; pass the credentials of a user with Managers role created in above steps, and call GetData operation of your WCF Service. The code should look as follows:
private void button1_Click(object sender, EventArgs e)
{
      WCFTestService.ServiceClient myService = new
                    WCFTestService.ServiceClient();
      //pass the credentials of a user in Manager’s role
      myService.ClientCredentials.UserName.UserName = "username";
      myService.ClientCredentials.UserName.Password = "p@ssw0rd";
      MessageBox.Show(myService.GetData(123));
      myService.Close();
}
  1. Right click on the Client project and select Set as Startup Project
  2. Run the Client application using F5 or Ctrl+F5, when you click the Button on the form it should display a message “You entered: 123
  3. Now test the application by passing the credentials of a user belonging to a different role (e.g., Employee) and you should receive Access Denied, a security exception. This is because the GetData operation is accessible only by the users who belong to Managers role.

Additional Resources

Contributors and Reviewers

  • External Contributors and Reviewers:
  • Microsoft Consulting Services and PSS Contributors and Reviewers:
  • Test team: Rohit Sharma, Chaitanya Bijwe, Parameswaran Vaideeswaran.
  • Edit team: Dennis Rea.
  • SEO team: Rob Boucher.

Last edited Mar 28, 2008 at 11:31 PM by prashantbansode, version 4

Comments

PaulMouchet Mar 4, 2013 at 5:30 PM 
I know this has been around for while, but this is one of the best documents I've seen on how to implement SQL role provider through a WCF service. My question is, the last comment of Step 9 doesn't make sense (to me). There is no mention in Step 6 about installing a Root Authority certificate.

Perhaps somebody can explain this better?