I'm coming from a Linux world so I'm a bit at a disadvantage here.

Assuming a windows Task scheduler script that calls a stored procedure to return some rows, and transmit a file over email or SFTP - what would be the best way to pass SQL Server credentials.

Both the script machine and SQL Server 2014 are running on a cloud server on Rackspace or Amazon so the physical access/admin access vulnerability is minimal here. My concern would be an attacker obtaining access to the server, then the script and obtaining clear text credentials.

  • are the hosts on a domain? You could use integrated security and just grant the required permissions to the user the script runs as. – Jay Aug 28 '15 at 8:19
  • 1
    Considered using Windows Authentication? If possible - there won't be (SQL) credentials in the script at all – Stef Heylen Aug 28 '15 at 8:20
  • 1
    Why not schedule SQL Server to run the stored procedure and output to a text file? Then there are no credentials to pass (and fewer things to break). – schroeder Aug 28 '15 at 17:31
  • Good one, I see that you can create scheduled jobs using SQL Management Studio. Not necessarily less moving parts because the output text file has to be created with the right ACL and then another Task Scheduler job has to pick it up and SFTP it somewhere. But the advantage of this approach is not having to expose credentials and not having to create special purpose accounts. I like. – Danny Lieberman Aug 30 '15 at 6:36

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.