SqlMembershipProvider where IIS and SQL are on different machines?

Question

We're moving one of my websites to production. Because of a firewall and the website and database being on different machines, the website can't connect directly to the SQL server. We've set it up so we're running all of the database access through a WCF service. All of this works fine.

The problem, however, I've discovered lies in the AspNetSqlProvider. I'm not too familiar with providers other than setting it up for your site, but from what I gather, you pass the provider object a connection string and it automatically handles ASP.NET accounts for your website. I can't have this on production, however, as I stated, the website and database can't see each other and the automatic linking of the IIS and database won't work.

Judging by this topic, this is not an original concept/issue. I've been researching it for a couple days, though, and haven't found anything helpful. I did find one or two articles which illustrated how to create a new inherited MembershipProvider class and override all of the methods. This is a TON of work, though, and I can't see the resolution to an issue I believe to be common being so involved.

How can you setup the AspNetSqlProvider when the IIS and database are on different machines? Is there a way to run the AspNetSqlProvider through a wcf service or other object so it doesn't connect directly to a SQL server?

Thanks in advance!

Answer 1

You can wrap a custom membership provider around this service. Something to keep well in mind is securing the endpoint! SECURE THE ENDPOINT!

Use certificates or AD or whatever AND SSL as you will be passing users credentials over the wire.

I have not tested every method in this class but previous implementations performed just fine so it will probably get you to where you need to go, eventurally.

SECURE THE ENDPOINT!

// <copyright project="Salient.ScriptModel" file="MembershipService.svc" company="Sky Sanders">
// http://skysanders.net/subtext
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web.Security;

namespace Salient.ScriptModel
{
    [ServiceContract(Namespace = "Salient.ScriptModel", Name = "MembershipProvider")]
    public interface IMembershipProvider
    {
        string ProviderName { get; set; }

        [OperationContract]
        DateTime TestInput(DateTime date);

        [OperationContract]
        ProviderProperties GetProviderProperties();

        [OperationContract]
        MembershipUser GetUserByKey(object providerUserKey, bool userIsOnline);

        [OperationContract]
        MembershipCreateResult CreateUser(string username, string password,
                                          string email, string passwordQuestion,
                                          string passwordAnswer, bool isApproved,
                                          object providerUserKey);

        [OperationContract]
        MembershipCreateResult AddUser(MembershipUser user);

        [OperationContract]
        bool ChangePasswordQuestionAndAnswer(string username, string password,
                                             string newPasswordQuestion, string newPasswordAnswer);

        [OperationContract]
        string GetPassword(string username, string answer);

        [OperationContract]
        bool ChangePassword(string username, string oldPassword, string newPassword);

        [OperationContract]
        string ResetPassword(string username, string answer);

        [OperationContract]
        void UpdateUser(MembershipUser user);

        [OperationContract]
        bool ValidateUser(string username, string password);

        [OperationContract]
        bool UnlockUser(string userName);

        [OperationContract]
        MembershipUser GetUserByName(string username, bool userIsOnline);

        [OperationContract]
        string GetUserNameByEmail(string email);

        [OperationContract]
        bool DeleteUser(string username, bool deleteAllRelatedData);

        [OperationContract]
        MembershipFindResult GetAllUsers(int pageIndex, int pageSize);

        [OperationContract]
        int GetNumberOfUsersOnline();

        [OperationContract]
        MembershipFindResult FindUsersByName(string usernameToMatch, int pageIndex,
                                             int pageSize);

        [OperationContract]
        MembershipFindResult FindUsersByEmail(string emailToMatch, int pageIndex,
                                              int pageSize);
    }

    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MembershipProvider : IMembershipProvider
    {
        private System.Web.Security.MembershipProvider Provider
        {
            get
            {
                if (string.IsNullOrEmpty(ProviderName))
                {
                    return Membership.Provider;
                }
                return Membership.Providers[ProviderName];
            }
        }

        #region IMembershipProvider Members

        public DateTime TestInput(DateTime date)
        {
            return date;
        }

        public string ProviderName { get; set; }

        public ProviderProperties GetProviderProperties()
        {
            var returnValue = new ProviderProperties
                                  {
                                      ApplicationName = Provider.ApplicationName,
                                      EnablePasswordReset = Provider.EnablePasswordReset,
                                      EnablePasswordRetrieval = Provider.EnablePasswordRetrieval,
                                      MaxInvalidPasswordAttempts = Provider.MaxInvalidPasswordAttempts,
                                      MinRequiredNonAlphanumericCharacters =
                                          Provider.MinRequiredNonAlphanumericCharacters,
                                      MinRequiredPasswordLength = Provider.MinRequiredPasswordLength,
                                      PasswordAttemptWindow = Provider.PasswordAttemptWindow,
                                      PasswordFormat = Provider.PasswordFormat,
                                      PasswordStrengthRegularExpression = Provider.PasswordStrengthRegularExpression,
                                      RequiresQuestionAndAnswer = Provider.RequiresQuestionAndAnswer,
                                      RequiresUniqueEmail = Provider.RequiresUniqueEmail
                                  };
            return returnValue;
        }

        public MembershipUser GetUserByKey(object providerUserKey, bool userIsOnline)
        {
            return Provider.GetUser(providerUserKey, userIsOnline);
        }

        public MembershipCreateResult CreateUser(string username, string password,
                                                 string email, string passwordQuestion,
                                                 string passwordAnswer, bool isApproved,
                                                 object providerUserKey)
        {
            MembershipCreateStatus status;
            MembershipUser user = Provider.CreateUser(username, password, email, passwordQuestion, passwordAnswer,
                                                      isApproved,
                                                      providerUserKey, out status);
            return new MembershipCreateResult(user, status);
        }

        public MembershipCreateResult AddUser(MembershipUser user)
        {
            return new MembershipCreateResult(user, MembershipCreateStatus.DuplicateEmail);
        }

        public bool ChangePasswordQuestionAndAnswer(string username, string password,
                                                    string newPasswordQuestion, string newPasswordAnswer)
        {
            return Provider.ChangePasswordQuestionAndAnswer(username, password, newPasswordQuestion, newPasswordAnswer);
        }

        public string GetPassword(string username, string answer)
        {
            return Provider.GetPassword(username, answer);
        }

        public bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            return Provider.ChangePassword(username, oldPassword, newPassword);
        }

        public string ResetPassword(string username, string answer)
        {
            return Provider.ResetPassword(username, answer);
        }

        public void UpdateUser(MembershipUser user)
        {
            Provider.UpdateUser(user);
        }

        public bool ValidateUser(string username, string password)
        {
            return Provider.ValidateUser(username, password);
        }

        public bool UnlockUser(string userName)
        {
            return Provider.UnlockUser(userName);
        }

        public MembershipUser GetUserByName(string username, bool userIsOnline)
        {
            return Provider.GetUser(username, userIsOnline);
        }

        public string GetUserNameByEmail(string email)
        {
            return Provider.GetUserNameByEmail(email);
        }

        public bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            return Provider.DeleteUser(username, deleteAllRelatedData);
        }

        public MembershipFindResult GetAllUsers(int pageIndex, int pageSize)
        {
            int totalRecords;
            MembershipUserCollection users = Provider.GetAllUsers(pageIndex, pageSize, out totalRecords);
            var list = new List<MembershipUser>();
            foreach (MembershipUser user in users)
            {
                list.Add(user);
            }
            return new MembershipFindResult(list, totalRecords);
        }

        public int GetNumberOfUsersOnline()
        {
            return Provider.GetNumberOfUsersOnline();
        }

        public MembershipFindResult FindUsersByName(string usernameToMatch, int pageIndex,
                                                    int pageSize)
        {
            int totalRecords;
            MembershipUserCollection users = Provider.FindUsersByName(usernameToMatch, pageIndex, pageSize,
                                                                      out totalRecords);
            var list = new List<MembershipUser>();
            foreach (MembershipUser user in users)
            {
                list.Add(user);
            }
            return new MembershipFindResult(list, totalRecords);
        }

        public MembershipFindResult FindUsersByEmail(string emailToMatch, int pageIndex,
                                                     int pageSize)
        {
            int totalRecords;
            MembershipUserCollection users = Provider.FindUsersByEmail(emailToMatch, pageIndex, pageSize,
                                                                       out totalRecords);
            var list = new List<MembershipUser>();
            foreach (MembershipUser user in users)
            {
                list.Add(user);
            }
            return new MembershipFindResult(list, totalRecords);
        }

        #endregion
    }

    [DataContract]
    public class MembershipFindResult
    {
        [DataMember] public int RecordCount;
        [DataMember] public IEnumerable<MembershipUser> Users;

        public MembershipFindResult()
        {
        }

        public MembershipFindResult(IEnumerable<MembershipUser> users, int recordCount)
        {
            Users = users;
            RecordCount = recordCount;
        }
    }

    [DataContract]
    public class MembershipCreateResult
    {
        [DataMember] public MembershipCreateStatus CreateStatus;
        [DataMember] public MembershipUser User;

        public MembershipCreateResult()
        {
        }

        public MembershipCreateResult(MembershipUser user, MembershipCreateStatus createStatus)
        {
            User = user;
            CreateStatus = createStatus;
        }
    }

    [DataContract]
    public class ProviderProperties
    {
        public ProviderProperties()
        {
        }

        public ProviderProperties(bool enablePasswordRetrieval, bool enablePasswordReset, bool requiresQuestionAndAnswer,
                                  int maxInvalidPasswordAttempts,
                                  int passwordAttemptWindow, bool requiresUniqueEmail,
                                  MembershipPasswordFormat passwordFormat, int minRequiredPasswordLength,
                                  int minRequiredNonAlphanumericCharacters,
                                  string passwordStrengthRegularExpression, string applicationName)
        {
            EnablePasswordRetrieval = enablePasswordRetrieval;
            EnablePasswordReset = enablePasswordReset;
            RequiresQuestionAndAnswer = requiresQuestionAndAnswer;
            MaxInvalidPasswordAttempts = maxInvalidPasswordAttempts;
            PasswordAttemptWindow = passwordAttemptWindow;
            RequiresUniqueEmail = requiresUniqueEmail;
            PasswordFormat = passwordFormat;
            MinRequiredPasswordLength = minRequiredPasswordLength;
            MinRequiredNonAlphanumericCharacters = minRequiredNonAlphanumericCharacters;
            PasswordStrengthRegularExpression = passwordStrengthRegularExpression;
            ApplicationName = applicationName;
        }

        [DataMember]
        public bool EnablePasswordRetrieval { get; set; }

        [DataMember]
        public bool EnablePasswordReset { get; set; }

        [DataMember]
        public bool RequiresQuestionAndAnswer { get; set; }

        [DataMember]
        public int MaxInvalidPasswordAttempts { get; set; }

        [DataMember]
        public int PasswordAttemptWindow { get; set; }

        [DataMember]
        public bool RequiresUniqueEmail { get; set; }

        [DataMember]
        public MembershipPasswordFormat PasswordFormat { get; set; }

        [DataMember]
        public int MinRequiredPasswordLength { get; set; }

        [DataMember]
        public int MinRequiredNonAlphanumericCharacters { get; set; }

        [DataMember]
        public string PasswordStrengthRegularExpression { get; set; }

        [DataMember]
        public string ApplicationName { get; set; }
    }

}

EDIT: had to remove comments to fit.

p.s. SECURE THIS ENDPOINT!