I am attempting to switch over a database from sql server version 12.0.5203 to 14.0.1000, however I am seeing issues. While I can't get a backup to import, I also can't get the project to publish to a blank database. When I run the DB publish I very quickly get an error stating:
"Unable to connect to master or target server [database_name]. You must have a user with the same password in master or target server [database_name]."
With this same project I have published 100's of times to the older version of the database.
So with the publish issue aside. I tried to export data tier application and import with the newer version of sql server and every time I do that I get the following errors on the import.
Could not import package. Warning SQL72012: The object [itss_tutor_3_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box. Warning SQL72012: The object [itss_tutor_3_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box. Error SQL72014: .Net SqlClient Data Provider: Msg 15404, Level 16, State 19, Line 1 Could not obtain information about Windows NT group/user 'PSLLC\dbodenschatz', error code 0x54b. Error SQL72045: Script execution error. The executed script: CREATE ASSEMBLY [GroupConcat] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030058898C510000000000000000E00002210B010B00001E000000080000000000007E3D0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000243D000057000000004000003804000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000841D000000200000001E000000020000000000000000000000000000200000602E7273726300000038040000004000000006000000200000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000260 (Microsoft.SqlServer.Dac)
So basically I can't import an existing database and I can't publish to an empty one. Yet I can publish fine to the older version.
UPDATE: I installed newer version of Sql Server Data Tools and restarted. Now the publish doesn't fail immmediately but instead I get this error:
Creating [GroupConcat]... (65,1): SQL72014: .Net SqlClient Data Provider: Msg 10343, Level 14, State 1, Line 1 CREATE or ALTER ASSEMBLY for assembly 'GroupConcat' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly. (65,0): SQL72045: Script execution error. The executed script: CREATE ASSEMBLY [GroupConcat] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030058898C510000000000000000E00002210B010B00001E000000080000000000007E3D0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000243D000057000000004000003804000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000841D000000200000001E000000020000000000000000000000000000200000602E7273726300000038040000004000000006000000200000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000260 An error occurred while the batch was being executed.
The data tools install as well as following this link seems to have both the publish and import working:
https://www.sqlshack.com/impact-clr-strict-security-configuration-setting-sql-server-2017/