Solving Error : BAM deployment failed : The locale identifier (LCID) 8192 is not supported by SQL Server

When trying to deploy a BAM activity on a BizTalk Server 2020 machine, I had the following error: BAM deployment failed : The locale identifier (LCID) 8192 is not supported by SQL Server

Error Description

The following BAM command:

bm deploy-all -DefinitionFile:MyActivity.xml

Returns the following error:

Deploying Activity… ERROR: The BAM deployment failed.
A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal":
System.Data.SqlClient.SqlException: The locale identifier (LCID) 8192 is not supported by SQL Server.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlDataReaderSmi.NextResult() at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.GetServerProperty(String propertyName)
at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.GetSchemaVersion()
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExecArgumentBuilder.ToString()
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName)

After a little bit of Googling, I found someone having a different issue but with what looked like the same root cause (see Marc van der Wielen blog post here).
I detail the solution here again in case that one day Marc’s blog goes down. All credit goes to him.

Error root cause

The root cause of the problem is that the Service Account running the SQL Server instance is using an (apparently) unsupported locale setting.

In my case, my Windows 10 machine is configured with en-BE (English-Belgium) locale. I installed MS SQL Server with mostly all default options. By doing so, the Service Account created by the installer took the en-BE locale setting.
Once I changed the locale setting of the Service Account to en-US, the error hereabove disappeared.
It looks like while SQL Server did run fine for everything so far, it needs to have the local settings of the Service Account set to en-US for some specific functionalities to work properly (such as publishing a BizTalk BAM Activity in my case).

Resolution Procedure

1. Find what is the Service Account running the MS SQL Server service.
We can find that easily by running services.msc from a prompt.
When we find the SQL Server service, we can just look at its properties to see the name of the Service Account used to run it:

2. Find the SID (Security Identifier) of the Service Account.
One way to do this is through the registry:
– Open the registry by running regedit from a prompt.
– Once in the registry editor, navigate to the following registry node: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
– The registry node contains a list of nodes having SIDs as name. Find the node for which the ProfileImagePath‘s entry contains the MSSQLSERVER value. Write down the SID, in my case it is S-1-5-80-3880718306-xxxxx.

Another way to find the SID is to use PowerShell:
The PowerShell command is slightly depending of the type of account running the SQL Service (local account or domain account), see details here.

For local accounts:
>$objUser = New-Object System.Security.Principal.NTAccount("NT Service\MSSQLSERVER")
>$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
>$strSID.Value

For Domain account we get the NTAccount object in the following way:
>$objUser = New-Object System.Security.Principal.NTAccount(DOMAIN_NAME, USER_NAME)

3. Change the Service Account locale setting.
Navigate to the following registry node folder (using your own SID from the previous step): Computer\HKEY_USERS\S-1-5-80-3880718306-xxx\Control Panel\International.
– Set the Locale entry value to “00000409“
– Set the LocalName entry value to “en-US“
– Restart the SQL Server Service.

Setting the local setting for new user accounts to be of a particular locale

To avoid having this kind of issue, it is possible to tell Windows to set the default locale for new accounts. This option is in the Control Panel -> Regions -> Administrative -> Welcome screen and new user accounts.

This is also scriptable. See : https://docs.microsoft.com/en-US/troubleshoot/windows-client/deployment/automate-regional-language-settings