Does the account have the required database rights? I believe the ‘test connection’ just needs to see the database instance. I don’t believe it will be verifying full read/write access.
If you are using SQL authentication, then it would be whatever user you are specifying in the connection dialogue or connection string.
If you are using a trusted connection, it will be the domain account that is being used to run the Datawatch Agent service on the Automator server.