Most Secure way to handle secure parameters in SQL Job

Nandan Hegde 29,896 Reputation points MVP
2022-09-26T09:08:20.553+00:00

@Erland Sommarskog @Ronen Ariely
Below is the scenario which I have and needed the expert opinion for best security std to be followed

I have a powershell step in SQL Job wherein I need to have the blob access key provided

So how to have it passed in a secure way?

a) Similar to how we have WITH Encryption in stored procedures, is it possible to encrypt SQL Job steps?
b) I know we can trigger a SQL Job via sp_start_job but is there a way to pass parameters to the Job via TSQL activity ?
If yes, than I can create an encrypted stored procedure and have the access key passed to the job and trigger the job

Note: This is an On Prem SQL server , so accessing the key vault is not in scope via managed identity

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,766 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2022-09-26T21:00:59.083+00:00

    No, you cannot encrypt jobs. And for that matter, nor can you encrypt stored procedures. You can obfuscate them, but there tools out there that retrieve the source code easily for you.

    There is no direction provision to pass parameters to job, but you could read them from a table. And in this case, you would store the data encrypted with the old-style cell encryption. That is, you encrypt with a symmetric key, and the sym key is encrypted by an asym key or a certificate, which in its turn is protected by the database master key.

    Not that this that much better than obfuscation, but it is difficult to encrypt something in an environment, if you need to retrieve the secret in the same environment.

  2. Seeya Xi-MSFT 16,441 Reputation points
    2022-09-27T08:48:11.13+00:00

    Hi @Nandan Hegde ,

    No, but you can take a look at this: Implement SQL Server Agent Security
    https://learn.microsoft.com/en-us/sql/ssms/agent/implement-sql-server-agent-security?view=sql-server-ver16

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments