I know this is a stupid idea, but I'm having a hard time finding a better solution right now. I need to execute several sql stored procedures on a daily basis, then execute a powershell script to trigger an external application to pull some data off the sql server
The second step needs to pass username and password as arguments. These two servers are not a part of the same windows domain, so any user-level trust is not possible, only explicitly sending a username and password in the powershell scripts will work.
The server is SQL Server 2008, and we do not have SSIS available as an option. The sql server itself is restricted to only 3 users today, so having the password in plain text as a powershell command step within an agent job wouldn't get any of those users privilege they don't already have today, but this still just seems like there should be a better way I'm missing.
Best Answer
One risk mitigation is to use a signed stored procedure. Here's an example of this in action. In summary, you package all the sensitive action (table access etc.) inside a stored procedure. You create a certificate in SQL server to sign that SP. You create a new login who's only ability is to execute the SP and you send the password to this low-privilege login in plaintext. Depending on how sensitive the data coming from the SP are this can limit damage from a password leak.