Database migrations in Thinktecture.IdentityServer
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:
- Database support in Thinktecture IdentityServer.
- EF migrations in Thinktecture IdentityServer (this post).
- 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.
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.
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):
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:
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
brockallen 
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?
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.
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?
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
<<<<<<
Yes, thanks for that Raymond.
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?
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”.
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?
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.
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?
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>
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?
Not enough info, but the error you have is the place to start — check the connection string.
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
Yea, I might be behind on the DB migrations. It’s best to submit issues to github — they get looked at sooner than here.
Sounds good, thx …
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?
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.
You could also use https://github.com/chucknorris/roundhouse/wiki – works on all major databases.
Hey, I am trying to replace EF with NHibernate in IdentityServer and facing issues. Any pointers would be greatly appreciated.
Thanks!
Hmm, sorry I don’t know NHibernate at all. Perhaps post on the github issue tracker — more people will see your questions there.
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?
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.
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
Sorry — the blog isn’t a support forum. If you haven’t already, submit an issue to the github issue tracker.