Passing Credentials within SQL Server job

Question

Passing Credentials within SQL Server job

Nandan Hegde 36,146 MVP Volunteer Moderator

Hello All,
Good day!

The requirement is to execute the below powershell via SQL server job agent :
https://github.com/NickyvVr/PowerShell/blob/master/Power%20BI%20Dataset%20Refresh%20Service%20Principal.ps

How can we pass this highlighted credentials into the the SQL job without hardcoding it within the server?

Or Is there any way to link Azure key vault with the SQL server job.

Note: We cannot leverage AD Account to access as MFA is enabled on every account within the organization

Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-09-05T13:16:13.71+00:00

It's an on prem sql server 2014

1 answer

Your answer

Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-09-05T13:16:13.71+00:00

It's an on prem sql server 2014

Answer 1

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Nandan Hegde ,

Quote from MS document.

>Azure Active Directory (Azure AD) multi-factor authentication (MFA). Azure AD MFA can be used when connecting SSMS or SqlPackage.exe to Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics.

You are using on prem SQL server 2014, suggest you using SQL server authentication or windows authentication.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-09-06T03:30:32.76+00:00

Hey @CathyJi-MSFT , it seems there is some confusion w.r.t ask :)
The ask here is , is it possible to share some credentials in a secure way within powershell script in SQL server jobs? like the appid and secertid highlighted in image?

Or in worst case is it possible to leverage an AD account with mFA to login in automated jobs via powershell job:
connect AzAccount
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2022-09-06T08:06:06.087+00:00

Hi @Nandan Hegde ,

I am not familiar with powershell. Please check if below blog and thread could give you some ideas. Hope this could help you.

Proper way to store a password within a SQL Server agent job
Using MFA enabled accounts in PowerShell scripts

If I still misunderstood, please let me know.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-09-06T16:04:01.133+00:00

Hey ,
Thank you for the details but unfortunately neither of the links help :

Below pic from 2nd blog which states we cannot leverage MFA accounts for automated jobs as there is manual interaction involved

whereas the 1st link is.w.r.t Stored pricedures but the ask here is to paas some credentials in a secured way to powershell script

Share via

Passing Credentials within SQL Server job

1 answer

Your answer