locked
How to use SQL Server for Websecurity instead of the StarterSite.sdf RRS feed

  • Question

  • User2129316869 posted

    Please I have not been able to successful do this: I am trying to use my SQL Database for the WebSecurity instead of the default.

    This is my WebConfig connection string

    
    
    <connectionStrings>
        
      <add name="AccountContext" connectionString="Data Source=CLIENT100-PC;Initial Catalog=AccountDB;Integrated Security=True" providerName="System.Data.SqlClient" />
      </connectionStrings>

    This is my _Appstart.cshtml WebSecurity Initializer

    WebSecurity.InitializeDatabaseConnection("AccountContext", "UserProfile", "UserId", "Email", autoCreateTables: true);

    When I run this Code, the table gets created and everything seems to work fine but where the problem comes in is when i try to use the register form, onPost it throws: Provider encountered an unknown error

    Thursday, July 3, 2014 5:40 AM

Answers

  • User-1454326058 posted

    Hi Chris17,

    The reason of that issue is that you are using WebSecurity.CreateAccount instead of WebSecurity.CreateUserAndAccount.

    For the first method, it creates a new entry in the membership table of the membership database. It presumes that matching user information (represented by userName) already exists in the user profile table.

    Since you haven’t any record in the user profile table, you should use the second method.

    More information, please refer to:

    # WebSecurity.CreateAccount Method

    http://msdn.microsoft.com/en-us/library/webmatrix.webdata.websecurity.createaccount(v=vs.111).aspx

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 9, 2014 10:08 PM

All replies

  • User938738290 posted

    Your connection string has Integrated Security set to true.  Does the Windows account running the IIS process have permission to access your SQL DB?

    Thursday, July 3, 2014 10:18 AM
  • User2129316869 posted

    I do not know Sir. Please how can I check. I am using Windows Authentication. Thanks for your response

    Thursday, July 3, 2014 11:37 AM
  • User-1454326058 posted

    Hi Chris17,

    I suggest that you could use the SQL authentication. Create a SQL account and grant the necessary permission. Then you could use this account to log on the SQL Server.

    <add name="AccountContext" connectionString="Data Source=CLIENT100-PC;Initial Catalog=AccountDB;User ID=[user name]; Password=[user password]" providerName="System.Data.SqlClient" />
      

    Otherwise, I think you should implement impersonation.

    More information, please refer to:

    # How to implement impersonation in an ASP.NET application

    http://support.microsoft.com/kb/306158/en-us

    Note: In my opinion, you'd better not let the IIS account have the premission to log on.

    Thanks

    Best Regards

    Friday, July 4, 2014 5:39 AM
  • User2129316869 posted

    I just tried the connection type you gave me using sa as user ID but i still get the same issue. It is really frustrating cause I have my app running but cant set up the web security. Thanks for all contribution.

    Hi Chris17,

    I suggest that you could use the SQL authentication. Create a SQL account and grant the necessary permission. Then you could use this account to log on the SQL Server.

    <add name="AccountContext" connectionString="Data Source=CLIENT100-PC;Initial Catalog=AccountDB;User ID=[user name]; Password=[user password]" providerName="System.Data.SqlClient" />
      

    Otherwise, I think you should implement impersonation.

    More information, please refer to:

    # How to implement impersonation in an ASP.NET application

    http://support.microsoft.com/kb/306158/en-us

    Note: In my opinion, you'd better not let the IIS account have the premission to log on.

    Thanks

    Best Regards

    Sunday, July 6, 2014 2:34 PM
  • User-1454326058 posted

    Hi Chris17,

    Please connect to the database using wizard to check whether you could connect to it. (WebMatrix: New Database, VS: SQL Server Object Explorer)

    Thanks

    Best Regards

    Monday, July 7, 2014 3:02 AM
  • User2129316869 posted

    Hi Starain. Of Course I do connect. All my data are stored in the database and i have been using it for my application. Its Just the WebSecurity thing that has issues. Thanks a lot.

    Monday, July 7, 2014 4:28 AM
  • User-1454326058 posted

    Hi Chris17,

    Is there Email property in the UserProfile class?

    On the other hand, please share the project to us (OneDrive).

    Thanks

    Best Regards

    Monday, July 7, 2014 10:09 PM
  • User2129316869 posted

    On the other hand, please share the project to us (OneDrive).

    Ok I appreciate your help.

    I created a test a application which has the same error. 

    TestAccount

    Thanks a lot again.

    Tuesday, July 8, 2014 5:28 AM
  • User-1454326058 posted

    Hi Chris17,

    Base on your project, I found that your database file is StarterSite.sdf, I don’t have your AccountDB database file. Please share the database file to me.

    Thanks

    Best Regards

    Tuesday, July 8, 2014 9:49 PM
  • User2129316869 posted

    Thanks a lot. This is the LINK TO THE DB

    Wednesday, July 9, 2014 4:58 AM
  • User-1454326058 posted

    Hi Chris17,

    The reason of that issue is that you are using WebSecurity.CreateAccount instead of WebSecurity.CreateUserAndAccount.

    For the first method, it creates a new entry in the membership table of the membership database. It presumes that matching user information (represented by userName) already exists in the user profile table.

    Since you haven’t any record in the user profile table, you should use the second method.

    More information, please refer to:

    # WebSecurity.CreateAccount Method

    http://msdn.microsoft.com/en-us/library/webmatrix.webdata.websecurity.createaccount(v=vs.111).aspx

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 9, 2014 10:08 PM