This error message normally means that your SQL Server has been configured to operate in "Windows Authentication Mode (Windows Authentication)" and doesn't allow the use of SQL accounts.

I guess you'll have to change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)" to "Mixed Mode (Windows Authentication and SQL Server Authentication)".

If you want to use a "native" SQL Server user you'll have to change the server to Mixed Authentication.

It appears that Perfmon is not running under the credentials you specified, but as Network Service instead.

To solve this the service must run as a Windows user that has SQL Server credentials associated.

In my case the problem was the missing DSN in the remote machine targeted by PerfMon.
You must first login and configure both the 'DSN' and 'Data Collector Set' in the machine to be monitored by PerfMon (the remote machine). Then you can open the Perfmon in your local machine and change the target to the remote machine and start monitoring.