How I could specify the proper collation for the connection?
You can't. And even if you could, that wouldn't help here since the problem is a conflict between two different string columns. The only aspect of this problem that is specific to SSMS is that SSMS is selecting from a specific system view —
msdb.dbo.sysdac_instances — that is joining on these two string columns, expecting that they will always be using the same collation (and in most cases they do).
Please see my answer to this related / duplicate question:
Collation conflict … Could not use dbo.sysdac_instances
There I explain why this is happening and what your options are. This should be caused by having a different instance-level collation than the
msdb collation. This can happen when restoring / attaching
msdb created in another instance that had a different instance-level collation.
The option that should have the least amount of impact would be updating the definition of the
dbo.sysdac_instances system view. Not ideal, but given the other options, it is also the easiest to reverse should it prove to be a problem, though I don't see how it could be a problem. In that example I used
Latin1_General_CI_AS for the fix as it was the instance-level collation. In this case, I would use
SQL_Latin1_General_CP1_CS_AS as I am guessing it is your instance-level default collation.
Please also see the following post of mine if you are considering updating the collation of the instance and database(s):
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
Also, this is another example of why we need to have a special collation similar to
DATABASE_DEFAULT but working at the instance level —
INSTANCE_DEFAULT — so that there can be a generic solution to this type of issue. This would allow the
msdb.dbo.sysdac_instances_internal table to be created using:
[instance_name] sysname COLLATE INSTANCE_DEFAULT,
Or, it could be that the
msdb.dbo.sysdac_instances view has this at the end:
LEFT JOIN sys.databases sd
ON dac_instances.instance_name = sd.name COLLATE INSTANCE_DEFAULT
So please vote for the following suggestion:
Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation