SqlMembershipProvider 其中IIS 和SQL 位于不同的机器上？

Question

我们正在将我的一个网站投入生产。由于防火墙以及网站和数据库位于不同的机器上，网站无法直接连接到SQL服务器。我们已对其进行设置，以便通过 WCF 服务运行所有数据库访问。所有这些都运行良好。

然而，我发现问题出在 AspNetSqlProvider 中。除了为您的网站设置提供程序之外，我对提供程序不太熟悉，但根据我收集的信息，您向提供程序对象传递一个连接字符串，它会自动处理您网站的 ASP.NET 帐户。我无法在生产中使用此功能，但是，正如我所说，网站和数据库无法互相看到，并且 IIS 和数据库的自动链接将无法工作。

根据此主题判断，这不是一个原始的概念/问题。不过我已经研究了几天了，但没有发现任何有用的东西。我确实找到了一两篇文章，其中说明了如何创建新的继承的 MembershipProvider 类并重写所有方法。不过，这是一项繁重的工作，而且我认为如此复杂的问题也无法得到解决。

当 IIS 和数据库位于不同的计算机上时，如何设置 AspNetSqlProvider？有没有办法通过 wcf 服务或其他对象运行 AspNetSqlProvider，这样它就不会直接连接到 SQL 服务器？

提前致谢！

Answer 1

您可以围绕此服务包装自定义会员资格提供程序。需要牢记的一点是保护端点！确保端点安全！

使用证书或 AD 或任何和 SSL，因为您将通过线路传递用户凭据。

我没有测试此类中的每个方法，但以前的实现表现得很好，因此它最终可能会让您到达您需要去的地方。

确保端点安全！

// <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; }
    }

}

编辑：必须删除注释以适应。

ps 确保此端点的安全！