Skip to content

Database migrations in Thinktecture.IdentityServer

February 3, 2013

This is post 2 in a short 3 part series on describing the database support in v2 of Thinktecture.IdentityServer. The parts of this series are:

  1. Database support in Thinktecture IdentityServer.
  2. EF migrations in Thinktecture IdentityServer (this post).
  3. Integrating Thinktecture IdentityServer database with an existing database.

In IdentityServer we use EF as the data access technology. Since we anticipate the possibility of database schema changes in the future, we’ve also then started using EF’s code first migrations feature to try to provide a smooth upgrade path for anyone that needs to upgrade to future versions.

As I pointed out in the previous post, we also support any EF compatible database. The unfortunate aspect of this is that code first migrations are database/provider specific. So this means we need a migration for each possible database that would be used. This is unfortunate and tedious for us since we don’t know all of the database providers that might be used, but we made some safe assumptions that SqlCe 4.0 and SqlServer would be the main ones so those are the migrations we have checked into the source code repository. We’re assuming/hoping that AzureSql will be compatible with SqlServer and thus don’t have a specific one for SqlAzure. If SqlAzure is in fact different than SqlServer or if you have other database providers then we’d be happy for you to contribute those migrations.

To see the migrations you will need to open the solution and navigate to the “Repositories” project. It contains directories for the two supported database providers (SqlCe and SqlServer). Checked in is the actual code migration (.cs file) and also the equivalent SQL (.sql file). This way you can run the migration either from within Visual Studio or you can use the SQL file and run it directly against your database.

migrations

To run the migration from Visual Studio you need to run the package manager console by choosing the menu from Tools –> Library Package Manager –> Package Manage Console.

packagemanageconsole

And this is what the console window looks like. You’ll need to pick the “Repository” project as the Default Project (notice the drop-down in the top right corner of the window):

pakagemgrconsole

When you run commands since we have different database providers we’ll need to specify which one we want to use. This is why the connection strings configuration in the WebSite project has multiple entries (~/Configuration/connectionStrings.config):

<connectionStrings>
  <!-- configuration data like endpoints, protocol config, relying parties etc... -->
  <add name="IdentityServerConfiguration"
       connectionString="server=localhost;database=IdentityServerConfiguration;trusted_connection=yes;"
       providerName="System.Data.SqlClient" />

  <add name="SqlServer"
       connectionString="server=localhost;database=IdentityServerConfiguration;trusted_connection=yes;"
       providerName="System.Data.SqlClient" />
  <add name="SqlCe"
       connectionString="Data Source=|DataDirectory|\IdentityServerConfiguration.sdf"
       providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

The IdentityServerConfiguration entry is for runtime and the SqlServer and SqlCe entries are for running migrations from within the package manager console.

Once the console is up and you know which database you’d like to configure, you can then create and/or upgrade it to a particular migration (which usually would be the latest one). To do so run the command:

Update-Database -TargetMigration:InitialMigration -ConnectionStringName:SqlServer -ConfigurationTypeName:SqlServerConfiguration

TargetMigration indicates the name of the migration (which is the class name inside the .cs migration file). ConnectionStringName is which connection and database provider to use from the .config file. ConfigurationTypeName is which migration to use (SqlCeConfiguration or SqlServerConfiguration). You should get output like this:

runmigration

You could use the code first approach for auto-creating the database, but using the migrations is the preferred way of creating and managing the database for IdentityServer, mainly because the migrations allow more control over the schema (including indexes, etc.).

HTH

25 Comments leave one →
  1. March 20, 2013 3:20 pm

    I didn’t use the package manager, I just ran the sql scripts from the Migrations.SqlServer folder directly in Sql Server, and had no issue. But when I then run the app I get a ‘Sequence contains no elements..’ error in ConfiguratioRepository.cs when it calls to get the GlobalConfiguration. Looks like it’s expecting there to be a row in there, but I don’t see any insert commands in the sql scripts, just schema stuff. Any ideas?

    • March 20, 2013 3:36 pm

      You ran them to create a new database? If so, then yea.. there is one “seed” row that needs to be in there that the EF migrations don’t know how to emit into the .sql files.

      I’d suggest creating the DB from VS’ migrations — that will add the needed row.

  2. andysmithliazon permalink
    March 20, 2013 3:23 pm

    I didn’t use the package manager, I just ran the sql scripts directly in sql server, which ran without error. But when I run the app, I get a ‘Sequence contains no elements…’ error in ConfigurationRepository.cs as it attempts to get the GlobalConfiguration. Looks like it’s expecting a row there, but I don’t see any insert statements in these sql scripts, where is that row supposed to be populated initially?

    • April 15, 2013 4:31 pm

      the following SQL script should fix your missing seed data, and I think that should be added to

      $\src\Libraries\Thinktecture.IdentityServer.Core.Repositories\Migrations.SqlServer\Sql\201303212116088_AdfsIntegration.sql

      >>>>>
      USE [IdentityServerConfiguration]
      GO
      SET IDENTITY_INSERT [dbo].[AdfsIntegrationConfiguration] ON

      GO
      INSERT [dbo].[AdfsIntegrationConfiguration] ([Id], [Enabled], [UsernameAuthenticationEnabled], [SamlAuthenticationEnabled], [JwtAuthenticationEnabled], [PassThruAuthenticationToken], [AuthenticationTokenLifetime], [UserNameAuthenticationEndpoint], [FederationEndpoint], [IssuerUri], [IssuerThumbprint], [EncryptionCertificate]) VALUES (1, 1, 1, 1, 1, 0, 60, N’https://server/adfs/services/trust/13/usernamemixed’, N’https://server/adfs/services/trust/13/issuedtokenmixedsymmetricbasic256′, NULL, NULL, NULL)
      GO
      SET IDENTITY_INSERT [dbo].[AdfsIntegrationConfiguration] OFF
      GO
      <<<<<<

  3. May 29, 2013 3:44 pm

    I can’t get the migrations to run. Instead, I get the following exception:

    PM> Update-Database -TargetMigration:InitialMigration -ConnectionStringName:SqlServer -ConfigurationTypeName:SqlServerConfiguration
    Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
    System.InvalidOperationException: No connection string named ‘SqlServer’ could be found in the application config file.
    at System.Data.Entity.Infrastructure.DbConnectionInfo.GetConnectionString(AppConfig config)
    at System.Data.Entity.Internal.LazyInternalConnection.get_ConnectionHasModel()
    at System.Data.Entity.Internal.LazyInternalContext.OverrideConnection(IInternalConnection connection)
    at System.Data.Entity.Infrastructure.DbContextInfo.ConfigureContext(DbContext context)
    at System.Data.Entity.Infrastructure.DbContextInfo..ctor(Type contextType, DbProviderInfo modelProviderInfo, AppConfig config, DbConnectionInfo connectionInfo)
    at System.Data.Entity.Infrastructure.DbContextInfo..ctor(Type contextType, DbConnectionInfo connectionInfo)
    at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration, DbContext usersContext)
    at System.Data.Entity.Migrations.DbMigrator..ctor(DbMigrationsConfiguration configuration)
    at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.GetMigrator()
    at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
    at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
    No connection string named ‘SqlServer’ could be found in the application config file.

    Do I need to do something to get the Repositories project to read from the connectionStrings.config in WebSite?

    • May 31, 2013 1:27 am

      Hmm…only thing I can think of is that you need to make sure in the console you’ve chosen the repositories project as the “Default Project”.

      • May 31, 2013 1:10 pm

        I did that. In which config is it looking? I don’t see any database connection strings in the repositories project. Does it know to look in the WebSite project?

        • May 31, 2013 1:34 pm

          Yes, the EF migrations is a bit of magic. So you need to point at the project with the migrations, but it then looks in the hosting app’s .config — magic, eh? Anyway, it should be looking for the connection string in ~/WebSite/configuration/connectionStrings.config.

          • January 16, 2014 12:25 pm

            I would disagree with that assertion, based on my experiences today…the only way it seems to find the conn strings is in the app.config in the repositories directory. How would it know to do otherwise?

  4. DCallaway permalink
    May 31, 2013 1:54 pm

    I ran the migrations successfully but, unlike your example, I never saw the output “Running Seed method.” When I attempt to run the project, I’m receiving the model backing error you mention in your next post. Any ideas?

    PM> Update-Database -TargetMigration:InitialMigration -ConnectionStringName:SqlServer -ConfigurationTypeName:SqlServerConfiguration
    Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
    Applying code-based migrations: [201301011810411_InitialMigration].
    Applying code-based migration: 201301011810411_InitialMigration.
    PM>

  5. June 27, 2013 4:35 am

    When I execute the Update command, I’m getting the following error:

    An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

    Any help?

    • June 27, 2013 9:17 pm

      Not enough info, but the error you have is the place to start — check the connection string.

  6. July 18, 2013 4:03 am

    First, thanks for all of your work: working with security tokens is getting us ahead of the curve.

    So far, I seem to have successfully migrated to my SQL Azure database using the Package Manager Console. I used the -Verbose flag and saw no errors.

    Using the latest drop, I ran the Update-Database command for each of ‘InitialMigration’, ‘RefreshToken’, ‘ClientSecretNotRequired’, and ‘AdfsIntegration’.

    I have two questions, though:

    1) In comparing the newly created tables with those initially created in the local database, I’m missing the ‘StoredGrant’ table. Is this still needed?

    2) I also want to store the IdentityServerUsers data in a separate SQL Azure database. Is this supported? If so, how? Or, is there a good reason not to do this?

    Many thanks,
    Brian

  7. November 7, 2013 9:02 am

    Hey
    I tried to migrate the database as described above. The problem is that every time the migration fails with error:

    System.Data.SqlClient.SqlException (0x80131904): There is already an object named ‘GlobalConfiguration’ in the database.

    Has somebody an advice how to fix this error?

    • December 7, 2013 3:35 pm

      Hmmm, sounds like an EF issue. More info would be helpful. Also, when you get more info submit it to the github issue tracker (and not here). thx.

  8. January 10, 2014 12:38 pm

    You could also use https://github.com/chucknorris/roundhouse/wiki – works on all major databases.

  9. Arjun permalink
    March 22, 2014 7:54 am

    Hey, I am trying to replace EF with NHibernate in IdentityServer and facing issues. Any pointers would be greatly appreciated.

    Thanks!

    • March 22, 2014 9:33 am

      Hmm, sorry I don’t know NHibernate at all. Perhaps post on the github issue tracker — more people will see your questions there.

  10. June 5, 2014 4:10 pm

    I’m looking to change the schema of the database — e.g. from “dbo” to something more meaningful. Any simple way this could be accomplished?

    • June 8, 2014 8:59 am

      Hmm, you’d have to do this at the EF layer. I’ve not tried myself. I’m not sure the version of EF we use supports mapping the tables to a different schema. You have the code, so you could try.

  11. Nicolas Stubi permalink
    August 25, 2014 10:10 am

    Hello,

    I executed successfully the migration with the Package Manager.

    However, when I launch the website, I’m getting errors on 2 columns:

    Invalid column name ‘DisableSSL’.
    Invalid column name ‘PublicHostName’.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Invalid column name ‘DisableSSL’.
    Invalid column name ‘PublicHostName’.

    Source Error:

    Line 17: using (var entities = IdentityServerConfigurationContext.Get())
    Line 18: {
    Line 19: var entity = entities.GlobalConfiguration.First();
    Line 20: return entity.ToDomainModel();
    Line 21: }

    Source File: c:\dev\xTaT\AIT Portal\Version2\Client\STS\Thinktecture.IdentityServer.v2-master\src\Libraries\Thinktecture.IdentityServer.Core.Repositories\ConfigurationRepository.cs Line: 19

    Stack Trace:

    [SqlException (0x80131904): Invalid column name ‘DisableSSL’.
    Invalid column name ‘PublicHostName’.]
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +392
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815
    System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
    System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
    System.Data.SqlClient.SqlDataReader.get_MetaData() +138
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6738869
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +6741487
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +586
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
    System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
    System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +208
    System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +438
    System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +53

    [EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
    System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +155
    System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +1130
    System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +534
    System.Data.Entity.Core.Objects.c__DisplayClass7.b__5() +239
    System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +263
    System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +368
    System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable.GetEnumerator>b__0() +11
    System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() +50
    System.Linq.Enumerable.First(IEnumerable`1 source) +245
    System.Linq.Queryable.First(IQueryable`1 source) +330
    Thinktecture.IdentityServer.Repositories.Sql.ConfigurationRepository.get_Global() in c:\dev\xTaT\AIT Portal\Version2\Client\STS\Thinktecture.IdentityServer.v2-master\src\Libraries\Thinktecture.IdentityServer.Core.Repositories\ConfigurationRepository.cs:19
    Thinktecture.IdentityServer.Web.FilterConfig.RegisterGlobalFilters(GlobalFilterCollection filters, IConfigurationRepository configuration) in c:\dev\xTaT\AIT Portal\Version2\Client\STS\Thinktecture.IdentityServer.v2-master\src\OnPremise\WebSite\App_Start\FilterConfig.cs:13
    Thinktecture.IdentityServer.Web.MvcApplication.Application_Start() in c:\dev\xTaT\AIT Portal\Version2\Client\STS\Thinktecture.IdentityServer.v2-master\src\OnPremise\WebSite\Global.asax.cs:41

    [HttpException (0x80004005): An error occurred while executing the command definition. See the inner exception for details.]
    System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +12951237
    System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +175
    System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +304
    System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +404
    System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +475

    [HttpException (0x80004005): An error occurred while executing the command definition. See the inner exception for details.]
    System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +12968244
    System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +159
    System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +12807949

    • September 8, 2014 2:57 pm

      Sorry — the blog isn’t a support forum. If you haven’t already, submit an issue to the github issue tracker.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: