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, 2021 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, 2021 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, 2021 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, 2021 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, 2021 at 14:53

0

Reset to default

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.