We have a BizTalk 2013 server that uses a standard edition of SQL 2012 that gave many BAM errors. I am writing this up because the answer to the problem was not obvious from the errors.
The first error occurs when we run a SQL server agent job to refresh the cube that is part of the BAM aggregation.
The second error occurs when we try to run the SSIS package that the SQL agent job is trying run i.e. “ To run a SSIS package outside of SQL Server Data tools you must install Set global connection string variables of Integration Services or higher.”
Several posts claim that is because we do not have the correct permissions set but this was not the reason in our case.
The first clue as to what was the problem was found by Mario Lueckoff one our DBA’s who discovered that if he ran the package from another SQL server pointed it at the BAM_AN_All_GCTM SSIS package on the target SQL server then the cube was populated successfully.
Indeed we where trying to connect to an alias for the SQL server. Thus the cause is the loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.
The suggested workarounds are to not use the DNS alias, or disable the loopback check in the registry (set DisableLoopbackCheck registry entry).
We disabled the loopback check in the registry and our problem was solved…… That took a two weeks of my life