Executing a Prepare Instance table from an Ingest Instance gives the error: "Login failed for user"

Symptoms

The following error occurs when executing your first table in an Prepare Instance database mapped from an Ingest Instance:

Cause

The Ingest Server (and it's associated service account) is responsible for authentication and executing data from the Ingest Instance into the Prepare Instance. Therefore, when you execute a table in the Prepare Instance that is sourced from the Ingest Instance, it's the Ingest Instance handling the authentication into this. So, If your Ingest Instance and the Prepare Instance database are on different machines and networks, advanced networking configurations are required (dealing with firewall, VPN and subnet, etc.) before the Ingest Server can connect and transfer data.

In this case, the Ingest Instance is attempting to load data into the Prepare Instance database, but:

1. The Service account running the Ingest Server service does not have ‘write’ access into your Prepare Instance Database.
2. The Ingesty Server and Prepare Instance database are on separate servers and:
   1. You are NOT specifying a full remote server SQL Server name (or address) for your Prepare Instance database. (i.e. Ingest Server is trying to write to “localhost” but it is located on another computer)
   2. You ARE specifying a full remote server SQL Server name (or address), but the Ingest server is not able to reach the Prepare Instance server due to network or firewall issues. This article describes configuration steps to enable a SQL connection from a remote machine.  

Resolution

1. Ensure the service account running the Ingest service has proper permissions to write into your Prepare Instance database.
2. Specify the full remote SQL Server instance name (or address) on your Prepare Instance database.
3. Resolve network (firewall, VPN, subnet etc.) issue, if applicable.

Reference

Add Ingest Instances