Collation conflict when moving Azure SQL DB to SQL server on-premises or Azure VM using SQLPackage.

by | Jul 27, 2020 | Alerts, Microsoft, Technology, Uncategorized | 0 comments

This article is contributed. See the original author and article here.

Error message sample:

*** Error importing database: Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg 468, Level 16, State 9, Procedure GETTABLESWITHOPENCURSORS, Line 142 Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS_KS_WS_SC” in the equal to operation.

 
 

error1.png

 

Error cause:

The conflict is happening because the database containment is enabled on source, hence it is forcing the same on target that makes any system or temp objects to use the default windows collation “Latin1_General_100_CI_AS_KS_WS_SC” instead of the instance/DB collation “SQL_Latin1_General_CP1_CI_AS”.

 

What can enable containment on Azure SQL DB?

When exporting Azure SQL DB to a Bacpac the containment is None by default, unless database has a contained database users created under it, then it will force the containment to Partial.

In SQLPackage, the DAC framework will force enabling the containment during the import process if one of the below conditions is true:

  • If source database containment = Partial in model.xml.
  • If containment was not being set in model.xml file and database has a contained database users.

In our scenario, the second option is true, hence the containment was being forced during the import of the bacpac, you can see it in SQLPackage logs as mentioned below:

logs1.png

 

Resolution/Workaround:

In order to disable containment in Azure SQL DB, you need to drop the contained DB users created under the database, and it will set the containment = None when importing the database to target SQL server.

 

How to identify and drop contained DB users is Azure SQL DB?

1. Detect the contained DB users:   

   A. Run the below script to list all users:

 

select name as username,

       create_date,

       modify_date,

       type_desc as type,

       authentication_type_desc as authentication_type

from sys.database_principals

where type not in (‘A’, ‘G’, ‘R’, ‘X’)

      and sid is not null

      and name != ‘guest’

order by username;

 

   B.  Users with authentication_type DATABASE is a contained DB user:

            ssms1.png

 

2. Remove the contained DB user on source database by following the below steps:

  A. Run the below script to confirm if the user is an owner to a schema:

      SELECT s.name

      FROM sys.schemas s

      WHERE s.principal_id = USER_ID(‘test2’);

  B. If it’s an owner to a schema, then change the owner for this schema to dbo

      ALTER AUTHORIZATION ON SCHEMA::<SchemaName> TO dbo;

  C. Drop the user now under the database.

      DROP USER [test2]

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment