CauseSQL Server will not accept an Active Directory (AD) username and password passed from a separate AD user account.
Typically, "Test Connection" will function because it creates a direct connection to the database and does not involve the Run As User service account. However in this case, "Test Connection" is attempting to validate the user signed into Tableau Server instead of the Run As User. If this user does not have connect permissions, using the "Test Connection" option will fail.
When Tableau Server runs a refresh or connects to SQL it will use the Tableau Server Run As user account and the connection will succeed. It is expected behavior that the Database connection will connect with the Run As User account before providing SQL Server the impersonated user's credentials through the query.