SQL server Agent proxy Runs under Default profile

Pam 46 Reputation points
2021-10-28T17:04:55.997+00:00

Hi, I have SQL server Agent job using "Runs as" Proxy, with Proxy setup with domain account "domain\ABC".
The job runs cmdexec to run ps1 and connect to Sharepoint.

Case 1.When this job runs, it runs under C:\Users\Default (Default profile) and not under C:\Users\ABC, and it fails with "unable to connect to remote server"
Case 2. When smbdy RDPs as ABC or run as ABC on that server (ABC profile invoked) then this job runs successfully , it runs under C:\Users\SQLAgent (SQLAgent profile)

For testing purposes we gave ABC admin permissions on both windows and SQL Server. Without admin permissions Case 2 fails as well with "unable to connect to remote server".

Could this Default profile cause the job failure/with "unable to connect to remote server"?
Is it normal for Proxy to run under Default profile, or should it be Proxy-specific account (ABC)?
Should Powershell be configured somehow for that Default profile (how?)?
What policies, extra permissions or config are needed to get this right? SQL agent has all standard permissions and functional for other types of jobs.

Thank you!

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,675 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,108 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,359 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MotoX80 31,571 Reputation points
    2021-10-29T02:40:10.81+00:00

    I could be wrong, but I doubt that the file system folder for the profile (C:\Users\Whatever) has any impact on this. Since you are running a .ps1 file, add a few commands to it to verify that impersonation is working and that you can access network shares. 

    whoami.exe
net.exe view \\SomeFileServerName
Test-NetConnection -ComputerName My.Sharepoint.Site.Name -Port 443
Invoke-WebRequest HTTPS://My.Sharepoint.Site.Name

    Whoami should return domain\ABC. Net view should just list the file shares that are available on a server. If Net shows an error, then look at the security eventlog on SomeFileServerName and see what error was generated that prevented ABC from authenticating.

    The last 2 statements will test basic connectivity to your Sharepoint site.

  2. Limitless Technology 39,341 Reputation points
    2021-10-29T15:34:42.327+00:00

    Hello @Pam ,

    Thank you for reaching out.

    SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running.
    You can check this follow below steps.

    1. Run "gpedit.msc".
    2. Navigate to Local Computer Policy >> Computer Configuration >> Windows Settings >> Security Settings >> Local Policies >> User Rights Assignment>> "Access this computer from the network" right

    Below is Microsoft article mentioning the same.

    https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15#Restrictions

    ----

    --If the reply was helpful, please don’t forget to upvote or accept as answer.--

    0 comments