SQL Exception during initial migration on Azure SQL Database #5274
-
Version8.2.2 Using which broker(s) did you encounter the issue?MassTransit.SqlTransport.SqlServer What are the steps required to reproduce the issue?This issue occurs during the first migration.What is the expected behavior?Works as expected with on-premises SQL Server, but throws an exception during the initial migration on Azure SQL Database. What actually happened?Hello, I'm encountering an issue with MassTransit.SqlTransport.SqlServer (v8.2.2) and connecting with Azure SQL Database. The library works as expected with on-premises SQL Server, but throws an exception during the initial migration on Azure SQL Database. The error message is: "Invalid object name 'sys.syslogins'". Any help would be appreciated. Related log output, including any exceptions
16:04:15:415 INF MassTransit DbTransport creating database "XXXXX-backend-db" <Module: XXXXXBackend> <App: XXXX> ││ 16:04:15:415 ERR Hosting failed to start <Module: XXXXBackend> <App: XXXXX> ││ Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sys.syslogins'. ││ at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ││ at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ││ at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose) ││ at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) ││ at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() ││ at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() ││ at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) ││ at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) ││ at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod) ││ at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) ││ at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) ││ at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteReaderAsync>b__201_1(IAsyncResult asyncResult) ││ at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) ││ --- End of stack trace from previous location --- ││ at Dapper.SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 1241 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.GrantAccess(ISqlServerSqlTransportConnection connection, SqlTransportOptions options) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1447 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1391 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1395 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1395 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabase(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1340 ││ at MassTransit.SqlTransport.SqlTransportMigrationHostedService.StartAsync(CancellationToken cancellationToken) in /_/src/MassTransit/SqlTransport/SqlTransport/SqlTransportMigrationHostedService.cs:line 34 ││ at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token) ││ at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation) ││ ClientConnectionId:XXXXX ││ Error Number:208,State:1,Class:16 ││ 16:04:15:415 WRN Failed to stop bus: db://XXXX-mssql.database.windows.net/XXXXX?autodelete=300 ("Not Started") <Module: XXXXXBackend> <App: XXXXX> ││ Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sys.syslogins'. ││ at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ││ at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ││ at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose) ││ at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) ││ at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() ││ at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() ││ at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) ││ at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) ││ at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, Boolean isInternal, String endMethod) ││ at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) ││ at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) ││ at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteReaderAsync>b__201_1(IAsyncResult asyncResult) ││ at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization) ││ --- End of stack trace from previous location --- ││ at Dapper.SqlMapper.ExecuteScalarImplAsync[T](IDbConnection cnn, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 1241 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.GrantAccess(ISqlServerSqlTransportConnection connection, SqlTransportOptions options) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1447 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1391 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1395 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateSchemaIfNotExist(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1395 ││ at MassTransit.SqlTransport.SqlServer.SqlServerDatabaseMigrator.CreateDatabase(SqlTransportOptions options, CancellationToken cancellationToken) in /_/src/Transports/MassTransit.SqlTransport.SqlServer/SqlTransport/SqlServer/SqlServerDatabaseMigrator.cs:line 1340 ││ at MassTransit.SqlTransport.SqlTransportMigrationHostedService.StartAsync(CancellationToken cancellationToken) in /_/src/MassTransit/SqlTransport/SqlTransport/SqlTransportMigrationHostedService.cs:line 34 ││ at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token) ││ at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation) ││ at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken) ││ at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token) ││ at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token) ││ at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host) ││ at Program.<Main>$(String[] args) in ......Link to repository that demonstrates/reproduces the issueNo response |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 13 replies
-
|
You could try using the configuration callback when adding the migration service and specify CreateDatabase = false since the database already exist. It will still create the infrastructure (tables, etc.). |
Beta Was this translation helpful? Give feedback.
-
|
Hello, thank you for your prompt reply. Even with the settings "CreateDatabase = false" and "CreateInfrastructure = true", an exception arises during the initial DB migration when an empty database already existed. An error message appears at the start of the service: "Microsoft.Data.SqlClient.SqlException: 'The specified schema name "massTransitBus" either does not exist or you do not have permission to use it.'" (see image below) Given that my db user has all required permissions, it appears that the issue lies in the fact that the "MassTransit SQL schema" is created when the "CreateDatabase" setting is used, not when "CreateInfrastructure" is used. So, is this a bug, or is it supposed to be like this? When “CreateDatabase = false”, should the “db schema” for the MassTransit infrastructure already be there? This bug shows up in both on-premises and Azure SQL Server. Thank you and have a nice day P.S |
Beta Was this translation helpful? Give feedback.
-
|
I mean, good question, but you might just have to create the schema and make sure the user has access to it, and then let MassTransit create the infrastructure in that schema. Database/schema creation and assignment of rights is typically part of "creating a database" based on how permissions work, but it's something to explore I guess. |
Beta Was this translation helpful? Give feedback.
-
|
Alright, then. In my case, where the DB exists but the schema and role do not, I’ll create them with a simple SqlCommand. This is sufficient for my case. I’ll share my code, so that it might help other developers reading this 😀 ill mark this question as resolved. Code: public static class MassTransitMigrationExtension
{
private const string MassTransitDbSchema = "massTransitBus";
private const string MassTransitDbRole = "massTransitBus_owner";
public static void ConfigureMassTransitDbConnection(this IServiceCollection services, IConfiguration configuration)
{
services.AddOptions<SqlTransportOptions>().Configure(options =>
{
options.ConnectionString = configuration.GetConnectionString();
options.Schema = MassTransitDbSchema;
options.Role = MassTransitDbRole;
options.EnsureSchemaAndRoleExists();
});
services.AddSqlServerMigrationHostedService(options =>
{
options.CreateInfrastructure = true; // creates tables, stored procedures etc.
options.CreateDatabase = false; // creates db, schema and role. We use EnsureSchemaAndRoleExists() method for this.
options.DeleteDatabase = false;
});
}
private static void EnsureSchemaAndRoleExists(this SqlTransportOptions options)
{
try
{
using var connection = new SqlConnection(options.ConnectionString);
connection.Open();
// Note:
// A user with the db_owner role in SQL Server has full permissions in the database.
// If the user cannot have db_owner permissions, the user must have the following permissions to create a database role and schema:
// To create a database role, the user must have the ALTER ANY ROLE permission or be a member of the db_securityadmin fixed database role.
// To create a schema, the user must have the CREATE SCHEMA permission or be a member of the db_ddladmin fixed database role.
var sqlQuery = "IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @role) " +
$"EXEC('CREATE ROLE [' + @role + ']');" +
"IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = @schema) " +
$"EXEC('CREATE SCHEMA [' + @schema + '] AUTHORIZATION [' + @role + ']')";
using var command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@role", options.Role);
command.Parameters.AddWithValue("@schema", options.Schema);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new GenericException($"Error while migrating database - ensure {options.Schema} and {options.Role} exist.", ex);
}
}
} |
Beta Was this translation helpful? Give feedback.
-
|
I went ahead and put the schema and role generation into the infrastructure creation, so it's always done even if create database is false. Might try it out and report back. |
Beta Was this translation helpful? Give feedback.
-
|
Hi, (sorry for the late response). Since which version is this change available? Is it in one of the development/preview releases or in a stable release? |
Beta Was this translation helpful? Give feedback.
-
|
All commits end up in a pre-release build until the next version if released: |
Beta Was this translation helpful? Give feedback.
-
|
I faced kind of similar issue then I realize that with Azure SQL, we don't have access to this |
Beta Was this translation helpful? Give feedback.
-
|
@minhhieugma The latest develop packages should work if you set CreateDatabase = false with Azure. |
Beta Was this translation helpful? Give feedback.
-
|
I still face the same issue with the @borczeangelovbasenetch code can help me to create the empty schema. I guess the |
Beta Was this translation helpful? Give feedback.
-
|
Yeah, I'll need to test it with Azure SQL and see what's allowed and sort it out. |
Beta Was this translation helpful? Give feedback.
-
|
I pushed a commit last night that works with Azure SQL "on my machine" so you might pull the latest and try it. CreateDatabase = false, but CreateInfrastructure = true. |
Beta Was this translation helpful? Give feedback.
-
|
It works perfectly. Thank you @phatboyg |
Beta Was this translation helpful? Give feedback.
I mean, good question, but you might just have to create the schema and make sure the user has access to it, and then let MassTransit create the infrastructure in that schema. Database/schema creation and assignment of rights is typically part of "creating a database" based on how permissions work, but it's something to explore I guess.