Chapter 13: Internet - WCF and ASMX Client to Remote WCF Using Transport Security (Original Caller, HTTP)

patterns & practices Developer Center

Applies To

  • Microsoft® Windows Communication Foundation (WCF) 3.5

Scenario

In this scenario, your users do not have Microsoft Windows® accounts but use a Windows Forms client to make calls to the WCF service through either a WCF or ASP.NET Web services (ASMX) client proxy. User accounts are stored in Microsoft SQL Server®, and users are authenticated with username authentication.

The business logic called by the WCF service is backed by a SQL Server data store. The following figure illustrates the basic model for this application scenario.

Ff650724.CH13-Fig1(en-us,PandP.10).png

Figure 1
WCF and ASMX Client to Remote WCF Using Transport Security (Original Caller, HTTP) – Model

Key Characteristics

This scenario applies to you if:

  • Your users are WCF and ASMX clients.
  • Your user accounts are stored in SQL Server. Internet Information Services (IIS) authenticates users against the SQL Server membership provider, via a custom HTTP module.
  • Your user roles are stored in SQL Server. WCF authorizes users with ASP.NET roles.
  • Your application transmits user credentials and other sensitive data over the network and needs to be protected.
  • The service is compatible with legacy ASMX clients with prior versions of the Microsoft .NET Framework.

Solution

Ff650724.CH13-Fig1(en-us,PandP.10).png

Figure 2
WCF and ASMX Client to Remote WCF Using Transport Security (Original Caller, HTTP) – Solution

Solution Summary Table

In this solution, you will:

  • Authenticate clients by using the SQL Server membership provider.
  • Authenticate clients by using the SQL Server membership provider with IIS via a custom HTTP module.
  • Use WCF to authorize users with roles in SQL Server by using the ASP.NET role provider.
  • Use a service account to call the SQL Server from WCF.
  • Use transport security to protect user credentials and sensitive data passed between the clients and the WCF service.
  • Use basicHttpBinding with transport security to ensure that the service is compatible with legacy ASMX clients.
  • Authenticate clients using a custom HTTP module in order to transmit user credentials over the transport, to ensure that the service is compatible with legacy ASMX clients.

Clients

Checks / more information

Example

WCF proxy

The client needs to manually configure the authentication type as Basic. In a generated proxy, you will need to change the value from None to Basic.

If the proxy is generated, this value will be generated as None because the WCF service sets the authentication as None. The Basic authentication type is needed in order for authentication negotiation to occur, so the authentication header is sent to the service.

        
<security mode="Transport">
 <transport clientCredentialType="Basic"/>
</security>

      

The client has a WCF proxy reference to the WCF service.

The application has access to the WCF metadata in order to create a service reference. The client will be prompted with credentials to get the metadata.

 

The root certification authority (CA) certificate for the service is installed in Trusted Root Certification Authorities.

This is required for Secure Sockets Layer (SSL) authentication. All certificates that are signed with this certificate will be trusted by the client machine.

 

The proxy invokes the service, passing user credentials to the WCF proxy.

The UserName and Password properties must be set before the proxy invokes a WCF method.

        
WCFTestService.ServiceClient myService = new WCFTestService.ServiceClient();
myService.ClientCredentials.UserName.UserName = "username";
myService.ClientCredentials.UserName.Password = "p@ssw0rd";
myService.GetData(123);
myService.Close();

      

Checks & more information

Example

ASMX proxy

The client has an ASMX Web service proxy reference to the WCF service.

The application has access to the WCF metadata in order to create a service reference. The client will be prompted with credentials to get the metadata.

 

The root CA certificate for the service is installed in Trusted Root Certification Authorities.

All certificates that are signed with this certificate will be trusted by the client machine.

 

The proxy invokes the service, passing user credentials to the ASMX Web service proxy.

The proxy’s credentials need to be set with the username and password before invoking a WCF method.

        
NetworkCredential netCred = new NetworkCredential("username", " p@ssw0rd");
asmxwebservice.Service proxy = new asmxwebservice.Service();
proxy.Credentials = netCred;
proxy.GetData(21, true);

      

Application Server

Checks / more information

Example

IIS—configuration

A dedicated application pool is created and configured to run under a custom service account.

Use a domain account if possible.

 

The WCF service is configured to run under the service account.

Assign the WCF service to the custom application pool.

 

A custom HTTP module is configured in Web configuration.

The custom HTTP module will authenticate the users against the SQL Server membership provider.

        
<httpModules>
…
  <add 
   name="BasicAuthentication Module"      
type="Module.UserNameAuthenticator,Authenticator" />

</httpModules>

An ASP.NET database is created for use with the SQL Server membership provider and SQL Server role provider.

Aspnet_regsql.exe creates the SQL database to store the user and role information.

        
aspnet_regsql -S .\SQLExpress -E -A r m

      

The connection string is configured to point to the user and role stored in SQL Server.

The database connection string includes Integrated Security=SSPI or Trusted Connection=Yes for Windows authentication.

        
<add 
   name="MyLocalSQLServer"    
   connectionString="Initial 
   Catalog=aspnetdb;data 
   source=localhost;Integrated 
   Security=SSPI;" />

      

The SQL Server membership provider is configured as a membership provider.

The membership feature helps protect credentials, can enforce strong passwords, and provides consistent APIs for user validation and secure user management.

        
<membership defaultProvider="MySqlMembershipProvider">
  <providers>
    <clear/>
    <add name= "MySqlMembershipProvider" connectionStringName="MyLocalSQLServer" applicationName="MyAppName" type="System.Web.Security.SqlMembershipProvider"/>
  </providers>
</membership>

      

The Role Manager feature is enabled and the SQL Server role provider is configured for roles authorization.

The Role Manager allows you to look up users’ roles without writing and maintaining custom code.

        
<roleManager enabled="true" defaultProvider="MySqlRoleProvider" >
  <providers>
     <clear/>
     <add name="MySqlRoleProvider" connectionStringName="MyLocalSQLServer" applicationName="MyAppName" type="System.Web.Security.SqlRoleProvider" />
  </providers>
</roleManager>

      

The WCF service process identity is given access permissions to the ASP.NET database.

Your WCF service process identity requires access to the Aspnetdb database.

        
-- Create a SQL Server login for the Network Service account
sp_grantlogin '<<Custom Service Account>>'

-- Grant the login access to the membership database USE aspnetdb GO sp_grantdbaccess '<<Custom Service Account>>', '<<Custom Service Account>>'

-- Add user to database role USE aspnetdb GO sp_addrolemember 'aspnet_Membership_FullAccess', '<<Custom Service Account>>'

sp_addrolemember 'aspnet_Roles_FullAccess', '<<Custom Service Account >>’

WCF service—configuration

The WCF Service is configured to use basicHttpBinding.

basicHttpBinding binding uses the HTTP protocol and provides compatibility with ASMX clients.

        
<services>
  <service behaviorConfiguration="ServiceBehavior" name="Service">
    <endpoint address="" binding="basicHttpBinding" bindingConfiguration="BindingConfiguration"
name="basicEndpoint" contract="IService" />
  </service>
</services>

      

Service metadata is configured in the service behavior to enable httpsGetEnabled and disable httpGetEnabled.

The service metadata entry is required in order to publish metadata to the clients.

        
<serviceBehaviors>
  <behavior name="ServiceBehavior">
    <serviceMetadata  httpGetEnabled="false" httpsGetEnabled="true" />
  </behavior>
</serviceBehaviors>

      

The service is configured for ASP.NET compatibility mode, both in configuration and in service implementation.

ASP.NET compatibility mode is necessary because IIS is performing authentication.

        
Configuration
<system.serviceModel>
 <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
…
</system.serviceModel>

Service Implementation [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)] public class Service : IService

WCF service—authentication

basicHttpBinding is configured to use transport security and no authentication.

The authentication will be performed by the ASP.NET HTTP module against the SQL Server membership provider.

        
<basicHttpBinding>
  <binding name= "BindingConfiguration">
   <security mode="Transport">
    <transport clientCredentialType="None" />
   </security>
  </binding>
</basicHttpBinding>

      

The SQL Server membership provider is configured to provide user authentication.

The membership feature automatically authenticates and creates the authentication ticket for you.

 

WCF service—authorization

The Role Manager feature is enabled with aspnetroles, and the provider is configured for roles authorization.

Roles authorization can be performed declaratively or imperatively in the operation contract.

        
<serviceBehaviors>
  <behavior name="ServiceBehavior">
    <serviceAuthorization principalPermissionMode="UseAspNetRoles"           roleProviderName="MySqlRoleProvider" />
  </behavior>
</serviceBehaviors>

      

A class that derives from IAuthorizationPolicy is implemented to set the principal of the current thread to do declarative authorization, and to set the identity so that it is available in a WCF security context.

The authorization policy class will allow you to assign the principal to the context of WCF, so that users can be authorized. It will also allow you to assign the identity to the context of WCF, so that it can be retrieved from the WCF security context.

 

The authorization policy is included in the configuration file.

        
<authorizationPolicies>
   <add policyType="AuthorizationPolicy.HttpContextPrincipalPolicy, AuthorizationPolicy" />
</authorizationPolicies>

      

Perform role checks declaratively by using a Windows Identity Token, for checking Microsoft Active Directory group membership.

A declarative role check is preferred over an imperative role check for a service operation.

        
[PrincipalPermission(SecurityAction.Demand, Role = "accounting")]
public string GetData(string message)
{
    return "hello";
}

      

Perform role checks imperatively using a Windows Identity Token, for checking Active Directory group membership.


If you need more fine-grained authorization control, you can use imperative role checks in the code itself. Use a call to Roles.IsUserInRole to perform the check.

        
public string GetData(string myValue)
{            if(Roles.IsUserInRole(@"Accounting"))
{
//Do something for Accounting role              
}
else
{
//Do something for non-accounting role or throw an error
} 
}

      

WCF service—SQL

The connection string for the database is configured to use Windows authentication.

The database connection string includes Integrated Security=SSPI or Trusted Connection=Yes.

        
SqlConnection sqlcon = new SqlConnection("Server=SqlServer;Database=Northwind;IntegratedSecurity=SSPI");

      

A database connection is opened by using the WCF process identity’s security context.

This happens by default.

 

Database Server

Checks / more information

Example

Configuration

A SQL Server login is created for the WCF service account (process identity).

This grants access to the SQL Server.

        
exec sp_grantlogin 'Custom Service Account'

      

The login is granted access to the target database.

This grants access to the specified database.

        
use targetDatabase 
go 
exec sp_grantdbaccess 'Custom Service Account' 
go 

      

A database role is created in the target database.

This allows access control and authorization to the database.

        
use targetDatabase 
go 
exec sp_addrole 'DB Role Name' 
go 

      

The login is added to the database role.

Grant minimum permissions. For example, grant execute permissions to selected stored procedures, and provide no direct table access.

        
use targetDatabase
go
exec sp_addrolemember 'DB Role Name', 'Custom Service Account'
go

      

Authentication

SQL Server is configured to use Windows authentication.

Communication Security

Checks / more information

Application server to database

You can use Internet Protocol security (IPSec) or SSL between the application server and database server to protect sensitive data in transit.

Analysis

Clients

WCF Proxy

  • The client configuration file is configured to use Basic authentication in order to allow the authentication negotiation to occur.
  • The user’s credentials are required in the ASP.NET HTTP module for authentication. Username credentials are set on the WCF proxy, and all calls to the WCF service are made through that proxy instance.
  • The user’s credentials are required in WCF for authorization. Username credentials are set on the WCF proxy, and all calls to the WCF service are made through that proxy instance.
  • For validating the service certificate, the Root CA certificate is installed on the client machine in the Trusted Root Certification Authorities location.

ASMX Web Service Proxy

  • The user’s credentials are required in the ASP.NET HTTP module for authentication. \Network credentials are set on the ASMX Web service proxy, and all calls to the WCF service are made through that proxy instance.
  • The user’s credentials are required in WCF for authorization. Username credentials are set on the ASMX Web service, and all calls to the WCF service are made through that proxy instance.
  • For validating the service certificate, the Root CA certificate is installed on the client machine in the Trusted Root Certification Authorities location.

Application Server

Authentication

  • Because the users communicate with the WCF service over the Internet and you cannot assume that they have a Windows account, the user information is stored in SQL Server. Since WCF does not support transport security with username authentication, a custom HTTP module is created that will authenticate the user against the SQL Server membership provider. This will support both WCF and ASMX Web services clients.
  • WCF is configured to use no authentication because the ASP.NET HTTP module will handle authentication.
  • To protect the user credentials in transit, a service certificate is installed and is configured to be used as service credentials in WCF.

Authorization

  • For coarse-grained access control, authorization checks are performed declaratively in the WCF service at the operation level. Unless fine-grained access control is needed, declarative authorization should be preferred over imperative authorization.
  • For fine-grained access control or for implementing business logic, authorization checks are made within the operations programmatically.
  • The Role Manager is a good choice for this scenario because it allows you to look up users’ roles without writing and maintaining custom code.

Data Access

  • To reduce the risk of stolen database credentials, the database connection string is configured to use Windows authentication. This eliminates the need to store credentials in files and pass credentials over the network to the database server.
  • The WCF service accesses the database by using the WCF process identity. As a result, all calls use the single process account and the designated database connection pooling.

Configuration

  • Since all of the clients communicate over the Internet, the best transport protocol for this scenario is the HyperText Transfer Protocol (HTTP). Additionally, since compatibility with ASMX Web services clients is required, basicHttpBinding is an ideal choice.
  • Because basicHttpBinding is supported by IIS 6.0, the WCF service is hosted in IIS.
  • In order to reduce the attack surface and minimize the impact of a compromise, the WCF service runs under the security context of the service account, using a least-privileged account.
  • In order to reduce the attack surface and minimize the impact of a compromise, the Windows service runs under the security context of the service account, using a least-privileged account.

Database Server

  • SQL Server database user roles are preferred over SQL Server application roles in order to avoid the password management and connection pooling issues associated with the use of SQL Server application roles. Applications activate SQL Server application roles by calling a built-in stored procedure with a role name and a password. Therefore, the password must be stored securely. Database connection pooling must also be disabled when you use SQL Server application roles, which severely impacts application scalability.
  • Creating a new user-defined database role, and adding the database user to the role, lets you give specific minimum permissions to the role. Therefore, if the database account changes, you do not have to change the permissions on all database objects.

Communication Security

  • Transport security protects sensitive data between the thick client and the WCF service.
  • You can use IPSec or SSL between the WCF service and the database server in order to protect sensitive data in transit.

Example

Clients

Application Server

IIS

Code

  • A class that derives from IHttpModule is implemented. This class authenticates the users against the SQL Server membership provider.
  • Initially, the class checks to determine if there is an authorization header in the request from the client. If the header is not present, the status of the context is assigned as 401(not authorized) and a WWW-Authenticate header is created and sent in the response to the client. This is the “handshake” for the authentication process. The client will know that it needs to send credentials for authentication.
  • Once the credentials have been sent by the client, they are extracted from the authorization header so that they can be used to call the SQL Server membership provider.
  • The class authenticates the user, calling Membership.ValidateUser(username, password) to validate the user against the SQL Server membership provider.
  • If the user is authenticated, an identity is created and assigned to the HttpApplication.Context.User property.
  • If the user is not authenticated, a 401 status is returned to the client and the user is denied access.

HTTP Module Code

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Security.Principal;

namespace Module
{
  public class UserNameAuthenticator : IHttpModule    
  {
     public void Dispose()
     {
     }

     public void Init(HttpApplication application)
     {
      application.AuthenticateRequest += new 
        EventHandler(this.OnAuthenticateRequest);
      application.EndRequest += new 
        EventHandler(this.OnEndRequest);
     }

     public void OnAuthenticateRequest(object source, EventArgs eventArgs)
     {
       HttpApplication app = (HttpApplication)source;
       //the Authorization header is checked if present
       string authHeader = app.Request.Headers["Authorization"];

       if (!string.IsNullOrEmpty(authHeader))
       {
         string authStr = app.Request.Headers["Authorization"];

         if (authStr == null || authStr.Length == 0)
         {
          // No credentials; anonymous request
          return;
         }



         authStr = authStr.Trim();
         if (authStr.IndexOf("Basic", 0) != 0)
          {
           //header not correct we do not authenticate
           return;
          }

          authStr = authStr.Trim();
          string encodedCredentials = authStr.Substring(6);
          byte[] decodedBytes = Convert.FromBase64String(encodedCredentials);
          string s = new ASCIIEncoding().GetString(decodedBytes);

          string[] userPass = s.Split(new char[] { ':' });
          string username = userPass[0];
          string password = userPass[1];

          //the user is validated against the SqlMemberShipProvider
          //If it is validated then the roles are retrieved from the
          //role provider and a generic principal is created
          //the generic principal is assigned to the user context
          // of the application


          if (Membership.ValidateUser(username, password))
          {
           string[] roles = Roles.GetRolesForUser(username);
           app.Context.User = new GenericPrincipal(new
           GenericIdentity(username, "Membership Provider"), roles);
          }
          else
          {
            DenyAccess(app);
            return;
          }

        }  //end of-  if (!string.IsNullOrEmpty(authHeader))
        else
        {
          //the authorization header is not present
          //the status of response is set to 401 and it ended
          //the end request will check if it is 401 and add
          //the authentication header so the client knows
          //it needs to send credentials to authenticate

          app.Response.StatusCode = 401;
          app.Response.End();

          //context.Response.StatusCode = 401;
          //context.Response.End();
         }
        }  //End class function

        public void OnEndRequest(object source, EventArgs eventArgs)
        {
            if (HttpContext.Current.Response.StatusCode == 401)
            {

            //if the status is 401 the WWW-Authenticated is added to 
            //the response so client knows it needs to send credentials 

            HttpContext context = HttpContext.Current;
            context.Response.StatusCode = 401;
            context.Response.AddHeader("WWW-Authenticate", "Basic Realm");
            }   
        }

        private void DenyAccess(HttpApplication app)
        {
          app.Response.StatusCode = 401;
          app.Response.StatusDescription = "Access Denied";

          // error not authenticated
          app.Response.Write("401 Access Denied");

          app.CompleteRequest();
        }
    } // End Class
}  //End Namespace

Configuration

  • The custom module is configured in the web.config file, in the HTTP modules section.
  • The service configuration file has an entry with a connection string pointing to the SQL Server store for authentication and authorization.
  • The service configuration file has an entry for SqlRoleProvider under system.web to define which role provider is being used.
  • The service configuration file has an entry for SqlMemberShipProvider under system.web to define the SQL Server membership provider for authentication.
<configuration>
…
  <connectionStrings>
    <add name="MyLocalSQLServer"
         connectionString="Initial Catalog=aspnetdb;data source=10.3.19.60;Integrated Security=SSPI;"/>
  </connectionStrings>

   <system.web>
      <membership defaultProvider="MySqlMembershipProvider" >
        <providers>
          <clear/>
          <add name="MySqlMembershipProvider"
               connectionStringName="MyLocalSQLServer"
               applicationName="MyAppName"
               type="System.Web.Security.SqlMembershipProvider" />
        </providers>
      </membership>

      <roleManager enabled="true" defaultProvider="MySqlRoleProvider" >
        <providers>
          <clear/>
          <add name="MySqlRoleProvider"
               connectionStringName="MyLocalSQLServer"
               applicationName="MyAppName"
               type="System.Web.Security.SqlRoleProvider" />
        </providers>
      </roleManager>

<httpModules>
…
<add name="BasicAuthenticationModule" type="Module.UserNameAuthenticator,Authenticator" />
</httpModules>
    </system.web>

</configuration>

WCF

Code

  • The service performs imperative authorization checks, calling Roles.IsUserInRole.
  • If auditing is required, the service retrieves the identity of the caller.
  • The AuthorizationPolicy class is developed to set the security principal to the WCF context. This way, when the business logic runs in the operation contract, it is possible to do authorization checks and auditing with the identity.
  • It is possible to do either imperative or declarative authorization checks in the business logic, because the security principal of the caller is in the WCF context when executing the business logic.
  • The service calls SQL Server by using Windows authentication.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IdentityModel.Claims;
using System.IdentityModel.Policy;
using System.Web;
using System.Security.Principal;

namespace AuthorizationPolicy
{
    // syncs Thread.CurrentPrincipal and identity in WCF with whatever is set 
    // by the HTTP pipeline on Context.User (optional)

    public class HttpContextPrincipalPolicy : IAuthorizationPolicy
    {
        public bool Evaluate(EvaluationContext evaluationContext, 
                             ref object state)
        {
            HttpContext context = HttpContext.Current;

            if (context != null)
            {
                evaluationContext.Properties["Principal"] = context.User;
                evaluationContext.Properties["Identities"] =
                   new List<IIdentity>() { context.User.Identity };
            }
            return true;
        }

        public System.IdentityModel.Claims.ClaimSet Issuer
        {
            get { return ClaimSet.System; }
        }

        public string Id
        {
            get { return "HttpContextPrincipalPolicy"; }
        }
    }
}

The service does imperative or declarative authorization, as shown in the following sections.

Imperative

using System.Data.SqlClient;
using System.Web.Security;

public string GetData(int value)
{
     if (Roles.IsUserInRole(@"accounting"))
     {
      SqlConnection sqlcon = new SqlConnection("Server=sqlServer;
Database=testdb;Integrated Security=SSPI");
      sqlcon.Open();

      string identity = 
HttpContext.Current.User.Identity.Name;
      return “data”
     }
     else return "not authorized";
}

Declarative

using System.Data.SqlClient;
using System.Web.Security;

PrincipalPermission(SecurityAction.Demand, Role = "accounting")]

public string GetData(int value)
{
 SqlConnection sqlcon = new SqlConnection("Server=sqlServer;
Database=testdb;Integrated Security=SSPI");
 sqlcon.Open();

 string identity = HttpContext.Current.User.Identity.Name;
 return “data”     
}

Configuration

  • The service has a binding endpoint that uses basicHttpBinding with binding configuration that enables transport security and no authentication.
  • The service behavior is configured with the serviceMetadata element to allow publishing metadata.
  • The service behavior is configured with the serviceAuthorization element to use ASP.NET roles for authorization.
<system.serviceModel>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />

    <bindings>
      <basicHttpBinding>
        <binding name="BindingConfiguration">
          <security mode="Transport">
            <transport clientCredentialType="None" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>

    <services>
      <service behaviorConfiguration="ServiceBehavior" name="Service">
        <endpoint address="" binding="basicHttpBinding"
                  bindingConfiguration="BindingConfiguration"
                  name="basicEndpoint" contract="IService" />
      </service>
    </services>

    <behaviors>
      <serviceBehaviors>
        <behavior name="ServiceBehavior">
          <serviceMetadata  httpGetEnabled="false" httpsGetEnabled="true" />
          <serviceAuthorization principalPermissionMode="UseAspNetRoles" 
                                roleProviderName="MySqlRoleProvider"> 
            <authorizationPolicies>
             <add policyType="AuthorizationPolicy.HttpContextPrincipalPolicy, AuthorizationPolicy" />
            </authorizationPolicies>
          </serviceAuthorization>
        </behavior>
      </serviceBehaviors>
    </behaviors>

  </system.serviceModel>

WCF Client

Code

  • The client passes user credentials explicitly when making calls to the service.
  • The client needs to provide credentials when creating a service reference.
WCFTestService.ServiceClient myService = new
WCFTestService.ServiceClient();
myService.ClientCredentials.UserName.UserName = "username";
myService.ClientCredentials.UserName.Password = "p@ssw0rd";
myService.GetData(123);
myService.Close();

Configuration

  • The client is configured to use Basic authentication.
<security mode="Transport">
  <transport clientCredentialType="Basic" 
             proxyCredentialType="None" 
             realm="" />
</security>

ASMX Web Service Client

Code

  • The client passes user credentials explicitly when making calls to the service.
  • The client needs to provide credentials when creating a service reference.
NetworkCredential netCred = new NetworkCredential("username", " p@ssw0rd");
asmxwebservice.Service proxy = new asmxwebservice.Service();
proxy.Credentials = netCred;               
proxy.GetData(21, true);

Database Server

Configuration

  • A SQL Server login is created for the WCF service account.
  • The WCF login name is given access to the application database.
  • The role is created in the application database.
  • The WCF login name is added to the role.
-- Create a SQL Server login  that matches the WCF machine name
EXEC SP_GRANTLOGIN 'npscode\perfpres02$'

-- Grant the login access to the application database
use testdb 
go 
exec sp_grantdbaccess 'npscode\perfpres02$' 

-- Create the new database role
use testdb
go
exec sp_addrole 'myrole2','db_owner' 

-- Add the new login to the role
use testdb
go
exec sp_addrolemember 'myrole2','npscode\perfpres02$' 

Additional Resources