A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

I checked the connection strings in the config files for xConnect and everything seemed fine. A better look at the stack trace showed me this error was originating from the Azure ShardMapManager code that xConnect used to manage the SQL shards. This ShardMapManager uses information from a table (ShardsGlobal) to connect to the right shard databases and opening this table showed me that the server name was set to localhost. However, SQL server was installed on another machine.

Because I was installing everything on seperated machines, I also ran the SIF scripts that created the shards on the SQL machine. These SIF scripts use a proprietary tool called Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe and the connection settings you use with this tool are implicitely used when connecting to the shards from xConnect. So bottom line: Make sure that the connection settings you pass into the Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe tool are the same ones that you will use when connecting from xConnect.

I was using the microsoft/mssql-server-windows-express docker image for the SQL database and you can only use windows authentication in the Build phase of this image. To work around this, I manually updated the connection settings in the shard tables after running the Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe tool:

UPDATE [xp0_Xdb.Collection.ShardMapManager].[__ShardManagement].[ShardsGlobal] SET ServerName = '<SERVERNAME>'

Besides this, don’t forget to make the user (That you are using in the connection string) db_owner of the sharding databases. If not, you will get errors in the xConnect logs about SQL not being able to get information about the original db_owner.