Zum Hauptinhalt gehen

Migrate Identity Server from SQL Server Compact 4.0 file (.SDF) to (external) SQL Server database

Permanently deleted user
Hello,

 

We work with Geocortex Essentials v4.5.1 & v4.8.2.

 

Does anybody have experience with migrating Identity Server from a SQL Server Compact 4.0 file (.SDF) to an (external) SQL Server database?

 

Which steps do you have to take, to configure Identity Server from SDF to SQL Server?

 

Can you migrate the existing users, roles and configuration (IdentityServerUsers.sdf & IdentityServerConfiguration.sdf)?. Is the encryption the same between these two 'databases' (Windows File Encryption)?

 

We used the following tool to export SDF to sql: https://orchardtricks.dotnest.com/blog/migrate-from-sql-compact-edition-to-sql-server and imported the file in a SQL Server database:

 

_img_ alt="Schema Identity Server in SQL Server" src="https://latitudegeo--c.na53.content.force.com/servlet/rtaImage?eid=906f2000000XcBn&feoid=Body&refid=0EMf2000000YE5H"_/_img_

 

Is this a correct method of should we start from scratch and create an (empty) schema first? See: https://docs.microsoft.com/en-us/aspnet/web-forms/overview/older-versions-security/membership/creating-the-membership-schema-in-sql-server-cs

 

We also modified the following files (Geocortex Essentials Administrator Guide):

 

1) connectionStrings.config

 

2) membership.config

 

3) roleManager.config

 

After opening 'Users and Roles' of Identity Server in the Manager, we get the following error:

 

_img_ alt="Error after opening 'Users and Roles'" src="https://latitudegeo--c.na53.content.force.com/servlet/rtaImage?eid=906f2000000XcBn&feoid=Body&refid=0EMf2000000YE5M"_/_img_

 

   at Geocortex.Essentials.RestManager.Controllers.SecurityUsersAndRolesController.a(String A_0, String A_1, Int32 A_2, Int32 A_3, String A_4, String A_5)

 

   at Geocortex.Essentials.RestManager.Controllers.SecurityUsersAndRolesController.Index(String userIssuerId, String roleIssuerId, Int32 usersPageIndex, Int32 rolesPageIndex, String userFilter, String roleFilter)

 

   at lambda_method(Closure , ControllerBase , Object[] )

 

   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)

 

   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)

 

   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()

 

   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

 

   at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)

 

Any ideas?

 

I appreciate your help with this question.

 

 

Erik
0

Kommentare

5 Kommentare

Datum Stimmen
  • Arjan Wilkens
    Hi Erik,

     

    We have resolved error 500 issues with connecting to the SQL Server instance by opening the endpoint of the identity server itself (https://yourserver/geocortex/youridentityserverinstance). If the configuration is not correct it will most likely show the 500 error there and if you open the page on the server, the error message will be shown aswell.

     

    There can be many issues why a connection is not working properly, but we can make connections to SQL Servers using the following configuration settings:
    • Connectionstrings.config <add name="SqlConnString" connectionString="Server=tcp:your_sqldatabase_instance,1433;Initial Catalog=Yourdatabasewiththeidentities;Persist Security Info=False;User ID=YourUser;Password=YourPassWord;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" providerName="System.Data.SqlClient"/>
    • Membership.config
    <membership defaultProvider="SqlMembershipProvider"> <providers> <add name="SqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="SqlConnString" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>
    • RoleManager.config
    <roleManager enabled="true" defaultProvider="SqlRoleProvider"> <providers> <add name="SqlRoleProvider" connectionStringName="SqlConnString" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>

     

    To my knowledge it is not sufficient to only copy the tables and schemas from the SDF database to the SQL server. The identityserver will call several procedures runtime as well, for example when creating new users and roles. Normally the GeoCortex Manager will report issues in connecting and creating users when you add them using the interface.

     

    We usually create the SQL Server database, and run the Aspnet_regiis.ex tool to create the appropiate database structure, so I have no experience with migrating an existing SDF file to a SQL Server instance. I don't think however that the encryption of the data itself is an issue, as I would assune that the encryption moves with the migration to the new database, and as the calling identity instance does not change, this should not become an issue.

     

    As a final note: I have seen errors in creating procedures using the tool because of collation issues. The default collation of the database is (in newer SQL databases) set to SQL_Latin1_General_CP1_CI_AS. The Aspnet_regiis.exe tool creates some tables with fields with collation Latin1_General_CI_AS, and also reverts to the default collation for others. Several procedures contain join and compare syntaxes, which will fail because of the incompatibility of these collations. I think if you set the default collation of the database to Latin1_General_CI_AS, you might avoid these...
    0
  • Permanently deleted user
    Thank you Arjan,

     

    I get an error when I opened the endpoint of the Identity Server (https://yourserver/geocortex/youridentityserverinstance).

     

    When I look at the web.config of Identity Server, I see the following Database Provider:    

     

    <DbProviderFactories>

     

          <remove invariant="System.Data.SqlServerCe.4.0" />

     

    Instead of SQL Server provider.

     

    How do I change this?
    0
  • Permanently deleted user
    I just realize I have to  install Identity Server with the new .config files, that wil change the web.config
    0
  • Arjan Wilkens
    Hi erik,

     

    I haven't had the need to change this setting. We're running quite happily with this setting in place with a (verified) connection to a SQl Server instance.
    0
  • Permanently deleted user
    Ok, got it. Thank you Arjan.
    0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.

Sie haben nicht gefunden, wonach Sie suchten?

Neuer Post