The following error occurs when executing your first table in an MDW database mapped from an ODX Server:
System.Data.SqlClient.SqlException: Login failed for user '<Username>'
The ODX Server (and it's associated service account) is responsible for authentication and executing data from the ODX into the MDW database, as shown in the diagram below. Therefore, when you execute a table in the MDW that is sourced directly from the ODX, it's the ODX server handling the authentication into the MDW. So, If your ODX Server and the MDW database are on different machines and networks, advanced networking configurations are required (dealing with firewall, VPN and subnet, etc.) before the ODX Server can connect and transfer data.
In this case, the ODX Server is attempting to load data into the MDW database, but:
1. The Service account running the ODX Server service does not have ‘write’ access into your MDW Database.
2. The ODX Server and MDW database are on separate servers and:
- You are NOT specifying a full remote server SQL Server instance name (or address) for your MDW database. (i.e. ODX Server is trying to write to “localhost” but it is located on another computer), OR
- You ARE specifying a full remote server SQL Server instance name (or address), but the ODX server is not able to reach the MDW server due to network or firewall issues. This article describes configuration steps to enable a SQL connection from a remote machine.
- Ensure the service account running the ODX Server service has proper permissions to write into your MDW database.
- Specify the full remote SQL Server instance name (or address) on your MDW database.
- Resolve network (firewall, VPN, subnet etc.) issue, if applicable.