locked
connectionstring to sqlmembershipprovider RRS feed

  • Question

  • User-1246852455 posted

    I have added the sqlmembershipprovider to my dynamic data project for logging in and registering and such.

    However, I am having problems with the connection string. The database is on a remote sql server 2008 database server.

    Below I have a connection string for the entities:

      <add name="xxEntities" connectionString="metadata=res://*;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=xx.xx.x.xxx;Initial Catalog=XX;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

    When I hit the membership provider, I get this error:

    Keyword not supported: 'metadata'.

    on this line of code: 

    System.Web.Security.Membership.GetAllUsers(0, 1, count)

    Do I need a separate connection string for the provider? Or should I be able to use the above string with all my connections, including my membership provider?

    thanks.

     

    Wednesday, November 12, 2008 12:20 PM

Answers

  • User-1005219520 posted

    Nannette  - the easiest way to solve this problem is to use SQL authentication. After you get it working you can encrypt the connection string.  For example, here a connection string I often use:

    <connectionStrings>
        <add name="AdventureWorksLT2008ConnectionString" connectionString="Data Source=bing0;Initial Catalog=AdventureWorksLT2008;
             Persist Security Info=True;User ID=sa;Password=*(IU89iu"
        providerName="System.Data.SqlClient" />
     </connectionStrings>
     <system.web>

    You need to make sure SQL authentication is enabled on the SQL server. 

    The error message is pretty good, Anon does not have login rights for the DB. What error do you get when you disable impersonation? You can disable impersonation via IIS manager or in web.config as you suggest.  The default for ASP.Net apps is disabled impersonation so you must have enabled it earlier.

    If you still have problems - create a new ASP.Net web site (not Dynamic Data) and add sqlmembershipprovider and get it working. Dynamic Data shouldn't have anything to do with the connection to the remote SQL Server.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 12, 2008 3:44 PM
  • User-1246852455 posted

    Thanks to the tenacity of ricka6 I got it working, he suggested:

    >>Disable impersonation and set the app pool identity to your account.

    I wrote a step by step instructon on this for those who also find this and have the problem:

     http://weblogs.asp.net/nannettethacker/archive/2008/11/14/membership-provider-connections-to-remote-database.aspx

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 14, 2008 3:43 PM

All replies

  • User660823006 posted

    The connection string you specified looks like a connection string for the Entity Framework. Can you try one that does not contain the Entity Framework specifc items and let me know if it works? I'll look into the issue if that works for you.

    Wednesday, November 12, 2008 12:41 PM
  • User-1246852455 posted

    Yes, it is an entity framework connection string. Should that not be used for a SQLMembershipProvider connection?

     Okay, I added a separate connection string to the same database and now I am getting this error:

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    I tried to debug this myself, but can't figure it out. I looked at this post:

    http://forums.asp.net/p/1324476/2640606.aspx#2640606

    He says:

         "So here's how you fix this:

         1.  turn off identity impersonation.  You know that line <identity impersonate="true"/> that's probably in your web.config?  Get rid of it.

         2.  If you want to see what users are coming your website, simply enable windows authentication in your web.config (without impersonation), and turn off anonymous access to your website in the IIS security configuration.

         3.  Either set the application pool of your application to run as a domain user with credentials to SQL server through IIS configuration -OR- give the BOXNAME\NETWORK SERVICE account permissions to your database.  If you pick the first option, beware that you also need to add the domain user to the IIS_WPG group on your ASP.NET web server, and also modify your local policies so that the domain user has permissions to "Log on as a service."  (Beware that if you do this you should really make sure you register service process names for your service account via the domain setspn command)."

    ---

    as far as step 1, I tried it with identity impersonation both on and off. I went to step 3 and added NT AUTHORITY/NETWORK SERVICE to my sql server 2008 web edition database (separate server). Neither of which worked. The web config has forms authentication.

     

    here are my membership web.config settings:

        <appSettings>
            <add key="MembershipConnectionStringName" value="XXConnectionString" />
        </appSettings>
       
      <connectionStrings>
      <add name="XXEntities" connectionString="metadata=res://*;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=xx.xx.xx.xx;Initial Catalog=XX;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
        <add name="XXConnectionString" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=XX;Integrated Security=True" providerName="System.Data.SqlClient" />
        </connectionStrings>

        <system.web>

            <membership defaultProvider="AspNetSqlMembershipProvider" hashAlgorithmType="SHA1" userIsOnlineTimeWindow="15">
                <providers>
                    <clear />
                    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="XXConnectionString" applicationName="/" passwordFormat="Hashed" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordStrengthRegularExpression="" passwordAttemptWindow="10" />
                </providers>
            </membership>

            <roleManager defaultProvider="AspNetSqlRoleProvider" cacheRolesInCookie="true" cookieName=".ASPXROLES" cookiePath="/" cookieProtection="All" cookieRequireSSL="false" cookieSlidingExpiration="true" cookieTimeout="30" createPersistentCookie="false" maxCachedResults="25" enabled="true">
                <providers>
                    <clear />
                    <add name="AspNetSqlRoleProvider" connectionStringName="XXConnectionString" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
                </providers>
            </roleManager>

        <compilation debug="true">

    ...snip....

          <customErrors mode="Off" />

          <authentication mode="Forms">
              <!--<forms name=".ASPXAUTH" loginUrl="~/Default.aspx" protection="All" timeout="999999"/>-->
              <forms cookieless="UseCookies" defaultUrl="~/Default.aspx" domain="" enableCrossAppRedirects="false" loginUrl="~/Default.aspx" name=".ASPXAUTH" path="/" protection="All" requireSSL="false" slidingExpiration="true" timeout="30" />
          </authentication>
          <identity impersonate="true" />

    Keep in mind that without the sql membership provider in my web.config, I can hit the same database just fine, so the anonymous user is assigned properly to this website and to my servers. I have the anonymous user setup and I can populate a grid and everything remotely. This same database is used by the dynamic data alone and works fine in the same application. It's just the log in and membership stuff that doesn't work.

    I am using VS2008, and Web Server 2008 Web Edition 3.5 SP1, and SQL Server 2008 Web Edition. And site has frontpage extensions installed for copying files from VS. This is a WebSite created as a Dynamic Data Entities Web Site.

    thanks.

     

     

    Wednesday, November 12, 2008 3:22 PM
  • User-1005219520 posted

    Nannette  - the easiest way to solve this problem is to use SQL authentication. After you get it working you can encrypt the connection string.  For example, here a connection string I often use:

    <connectionStrings>
        <add name="AdventureWorksLT2008ConnectionString" connectionString="Data Source=bing0;Initial Catalog=AdventureWorksLT2008;
             Persist Security Info=True;User ID=sa;Password=*(IU89iu"
        providerName="System.Data.SqlClient" />
     </connectionStrings>
     <system.web>

    You need to make sure SQL authentication is enabled on the SQL server. 

    The error message is pretty good, Anon does not have login rights for the DB. What error do you get when you disable impersonation? You can disable impersonation via IIS manager or in web.config as you suggest.  The default for ASP.Net apps is disabled impersonation so you must have enabled it earlier.

    If you still have problems - create a new ASP.Net web site (not Dynamic Data) and add sqlmembershipprovider and get it working. Dynamic Data shouldn't have anything to do with the connection to the remote SQL Server.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 12, 2008 3:44 PM
  • User-1005219520 posted

    The following should work for your default app pool FOR the local machine only since [NT AUTHORITY\NETWORK SERVICE]
    is the local machine account.

    use NorthwindEF
    go

    EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
    EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

    Wednesday, November 12, 2008 3:48 PM
  • User-1246852455 posted

    If I use forms authentication and set <identity impersonate="false" />, I get errors when trying to edit the dynamic data. If it is true, I can edit the dynamic data and that part works just fine.

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    I added the sql authentication and now I'm getting this error:

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'XXXXXXUser'.  Reason: The password of the account must be changed.

    I have this account setup on both the web server 2008 box and the sql server 2008 box which is a Vista Ultima OS. I normally setup this accounts in windows 2000 with the "password never changes" option and I don't find this as an option on either one of these operating systems.

     

     

     

     

    Wednesday, November 12, 2008 4:19 PM
  • User-1005219520 posted

    "password never changes" is part of group policy. Are you on a domain? password policies are typically set by domain policy. If you are not in a domain I think you can change this via local group policy. Looks like you're getting closer. Another good error message. It's not like the good old days when you'd get back "Error 0xFE0366346".

     

    Wednesday, November 12, 2008 4:27 PM
  • User-1246852455 posted

    I've never done such a thing before. How do I do that and why am I doing that and isn't it available in a gui interface?

    Are you doing that on the web server (which has the app pools), or the database server which has the role membership?

    That doesn't look like something I'd do in DOS (cmd).

    After seeing that, I tried granting access via the role membership to not only the dbdatareader and dbdatawrite for both the NETWORK SERVICE and the IISANONYMOUSUSER accounts, but I also added all the role memberships available for aspnet_Membership_BasicAccess and all the rest of the aspnet_ options. That still doesn't work though.

     

    Wednesday, November 12, 2008 4:33 PM
  • User-1005219520 posted

    [NT AUTHORITY\NETWORK SERVICE] refers to the local account.  The Network Service account from another server would be referred to a [domain\computername$]

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>For example if you want to allow my server redmond\bp0 Network Service account to access AdventureWorksLT2008 you would issue create login [redmond\bp0$] from windows
    <o:p></o:p>
    use AdventureWorksLT2008
    <o:p></o:p>
    create user [redmond\bp0$] <o:p></o:p>

     

    Wednesday, November 12, 2008 4:35 PM
  • User-1005219520 posted

    Can you try changing your SQL login and see if you can get it working?

    gpedit.msc for the GUI. You can google group policy password and get lots of hits. GP is a bit off topic.

    Wednesday, November 12, 2008 4:45 PM
  • User-1246852455 posted

    >>Can you try changing your SQL login and see if you can get it working?

    I did find the password policy on both machines: windows 2008 web server web edition and Windows Vista Ultima:

    Start / Programs/ Admin tools/ Local Security Policy

    brings up a screen: Account Policies / Password Policy
    set passowrd expires in 0 days to make it never expire.

    I rebooted both boxes and logged in as the IISUser and it did not make me change the password just to confirm.

    But when I go to run it with the sql authentication, I get the same error:

    >Login failed for user 'IISUser'.  Reason: The password of the account must be changed.

    :(

     

    P.S. Just one thing working today. Just one thing. That's all I want is that I can get one thing to work today. Sigh. :(

     

    Wednesday, November 12, 2008 5:14 PM
  • User-1246852455 posted

    I went ahead and changed the password for this account on both boxes. Then I changed it for the website in the anonymous user, and I still get the same message the the password must be changed.

     

    Wednesday, November 12, 2008 5:29 PM
  • User-1005219520 posted

    Keep in mind domain policy trumps local GP. See this post.  Your GP setting is requiring the PW to change when the login is first used. Can you try logging onto that account a couple times - or you could remove the must change GP.

    Wednesday, November 12, 2008 5:51 PM
  • User-1246852455 posted

    But I'm using Windows 2008 Web Server edition, which doesn't have an Active Directory domain controller. Is that what you're referring to?

    (Note to those looking for the answer, be sure to go to Page 2 of these forum posts to see the answer on the second page or you'll miss it!)

     

    Wednesday, November 12, 2008 6:29 PM
  • User-1005219520 posted

    No, I was asking if you Vista and Win08 servers were members of a domain. Win08 Web can't be a DC, but anything from NT 4 and up could be a DC for the two machines. Sounds like your not on a domain.

    Wednesday, November 12, 2008 7:11 PM
  • User-1246852455 posted

    Thanks to the tenacity of ricka6 I got it working, he suggested:

    >>Disable impersonation and set the app pool identity to your account.

    I wrote a step by step instructon on this for those who also find this and have the problem:

     http://weblogs.asp.net/nannettethacker/archive/2008/11/14/membership-provider-connections-to-remote-database.aspx

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 14, 2008 3:43 PM