SQL Server 2005 to SQL Azure migration errors (SQL0, SQL72014, SQL72045)

Question

I'm attempting to migrate/export a SQL 2005 (v9.0.5057) DB of roughly 450MB to SQL Azure. It's not going well.

I ran compatibility checks with both the current version of SQLPACKAGE and SMSS (13.0.16100.1) -- they passed.

However, neither utility can actually do the migration without a string of errors. SMSS has fewer errors, so I'll just detail those below.

Following this procedure, there are early successes:

After that, it sails right through all the tables.

But it soon hits a wall:

The details in each of those errors is the same:

===================================

Could not import package.
Warning SQL0: A project which specifies SQL Server 2005 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.
Error SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.
Error SQL72045: Script execution error.  The executed script:
CREATE USER [johnb] FOR LOGIN [johnb];


 (Microsoft.SqlServer.Dac)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Management.Dac.DacWizard.ImportDatabase.DoWork()
   at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

The database is nominally created, but it's only 4MB in size.

The SQL0 warning is puzzling, since in no place did I ever specify SQL 2005 as a "target platform."  In fact SQLPACKAGE defaults to using the "latest" platform for the export, and no doubt SMSS does, too (it's not asked for in any case).

The errors seem to relate to some kind of username conflict, though I'm not sure why that would be. The "johnb" you see above is the SQL admin both locally and in Azure. Is that the issue?  I don't quite follow what it's trying to say.

SQLPACKAGE (from a bacpac file) also throws in two SQL72012's (log, data_0) and one SQL72011, but since SMSS doesn't, I'm hoping they're not relevant.

Thanks

Answer 1

Hi Rseiler,

>>The SQL0 warning is puzzling, since in no place did I ever specify SQL 2005 as a "target platform."

Based on my test, this message only appears when deployment fails, I would assume it’s safe to ignore at this moment.

>>The errors seem to relate to some kind of username conflict, though I'm not sure why that would be.

I was able to reproduce the issue, from what I can tell that’s you cannot create any database user for Azure SQL Database subscriber account( login johnb in this case) as it’s already been associated to dbo. You could use SELECT CURRENT_USER to verify that. 

I’m afraid the only thing you could do at this moment is either create this user for another login, or drop and recreate this user without login before migration.

If you have any other questions, please let me know.

Regards,
Lin

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

There are also some helpful pages about the problem that I found (with a couple good links there in the comments that have helped others), but I couldn't quite make it work.

Then I realized that the "server admin login" in SQL Azure was that very user. I couldn't find a way to change that user (strange), so I deleted the server and recreated it with a new, unique SQL login.

That worked around the issue.

BTW, is there a bug in SMSS where when accessing the "Permissions" section of the properties of a given db that it tends to crash SMSS (Exception code: 0xe0434352 in KERNELBASE.DLL, a system file in Win10)?  I'm seeing that consistently, though only with some dbs. I've seen it happen on two different Win10's.

Answer 3

Hi Rseiler,

>>I couldn't find a way to change that user (strange), so I deleted the server and recreated it with a new, unique SQL login.

Glad to hear that you have found a workaround. Based on my test, the name of ‘server admin’ on Azure is indeed unchangeable. In this case, I would suggest you submit a feedback to Azure via this link.

>>BTW, is there a bug in SMSS where when accessing the "Permissions" section of the properties of a given db that it tends to crash SMSS (Exception code: 0xe0434352 in KERNELBASE.DLL, a system file in Win10)?  I'm seeing that consistently, though only with some dbs.

I would suggest you start a new thread in SQL Server Tools forum for better response. Please also include information like SSMS verison/OS version/SQL Server version/database compatibility level. It’s likely you could found detailed information regarding error 0xe0434352 in Event Viewer so please include them as well.

As this moment, I cannot comment much on this error as it seems like generic .Net CLR error and KERNELBASE.DLL it not the root cause of it.

Thank you for your understanding and support.

Regards,
Lin

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Yes, I'll open a new thread on that separate problem, since with the frequent revs of SMSS, it could very likely get fixed sooner rather than later.

Thanks for your help.