At present each person will use the same credentials to access the remote server and one Sql user. This may change over time hence the need for the user to...

It is a data source yes. I don't want to save credentials, I want the user to be prompted each time the report is run.

I was logging in using my windows credentials when prompted to ensure the connection worked and the report returned data. Users will not be using windows authentication.