0

"login failed for collectionuser" errors appear after migrating our Sitecore 10.1.1 databases from self-hosted SQL Server on VM to Azure SQL Database. The site itself is working, but anything connected to XConnect is not.

Mitigation attempts:

  • Validated all Connection Strings worked in all three sites
    • identity
    • sc
    • xconnect
  • Created individual collectionuser users and used the same password for each.
    • Xdb.Collection.ShardMapManager
    • Xdb.Collection.Shard0
    • Xdb.Collection.Shard1
  • Executed EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = '$(CollectionDbUserName)' on each shard
  • Updated the ServerName and DatabaseName fields to match the new server and database names
    • [__ShardManagement].[ShardsGlobal]
    • [__ShardManagement].[ShardsLocal]
  • Recycled AppPools and Services

If I revert only the collection connectionString the errors stop, so I know that it is the connection to those databases is the problem, but of course, we want the databases in Azure.... Anyone have any recommendations?

Improve this question
5
  • collection_user access is needed in multiple databases. In each shard, I believe you will need to run this script EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = '$(CollectionDbUserName)' and execute the output of the first script.
    – Nehemiah Jeyakumar
    Oct 18 at 16:58
  • @NehemiahJeyakumar, I updated my question to show had done that previously. Examining the SP shows that it executes the script output after printing the commands.
    – Daniel Govier
    Oct 18 at 17:22
  • I believe I got this same error when I did something similar. In Xdb.Collection.ShardMapManager there are tables which have references to database names, e.g. ShardsGlobal has a ServerName and DatabaseName field. Is this all set correctly? More info can be found here doc.sitecore.com/en/developers/101/…
    – Jeroen
    Oct 19 at 14:54
  • @Jeroen, I had checked the ServerName and DatabaseName fields previously. They are correct. (updated question to be more clear)
    – Daniel Govier
    Oct 19 at 21:52
  • When I had this issue I only figured out what was happening by running DB profiler. Perhaps you can do something like this stackoverflow.com/a/50631093/4950874?
    – Jeroen
    Oct 20 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.