If an attacker gains access to the server, you'll likely have other concerns than exposing clear text credentials in a file.
Also, your on the right track for inquiring about security best practices. Don't go through all of this effort only to email the results. Email is not secure so your results should be encrypted or use something else such as your mention of SFTP.
With that said, you have two options:
Windows Account - run the task as a dedicated Windows account and use Integrated Auth. This will not require storing the username/password in the connection string but you will need to store the credentials when setting up the scheduled task. If this account is compromised, you now need to consider what access it has. Either domain account or a local account on the sql server. If the former, they'll be able to enumerate domain resources and if the later access will be limited to the sql server. Finally be careful what security groups this account is granted membership, if any.
SQL Account - will be specific to the MSSQL instance though it will require storing the username/password combination in the connection string. A way to get around this is set NTFS permissions only allowing the account running the scheduled task access. For example, if the task runs as SYSTEM only allow that account access to your script. Don't forget to include the account used to run backups and your account as well.
DPAPI - I believe this would be out-of-scope for what you're looking to accomplish so I'll just mention that DPAPI allows one to use asymmetric encryption in configuration files and such.
Consider account management when making your choice. There may be policies requiring a password change every ?? days. See what applies and choose the account type that best fits what you're looking to achieve.
No matter what option you choose, make sure the account only has access to run the applicable stored procedure(s). This will limit the exposure on your sql server should the account become compromised.
Finally, there are lots of examples of using PowerShell to run stored procedures. I just used the following search string which returned lots of results.
sql server stored procedure from powershell stackoverflow