question

Pam-7890 avatar image
0 Votes"
Pam-7890 asked LimitlessTechnology-2700 answered

SQL server Agent proxy Runs under Default profile

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-generalwindows-serverwindows-server-powershellsql-server-integration-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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)

So it never run under C:\Users\ABC?

Could the profile of that user be corrupted? Could it be that there is a local account by that name?

0 Votes 0 ·

Hi @ErlandSommarskog
Right
It would never run under C:\Users\ABC profile if run as SQL job.

Or I could myself login as ABC, then I am using C:\Users\ABC
-> if I run ps1 interactively using powershell ISE, it will obviously run as C:\Users\ABC and script will run successfully.
->and then if manually run the SQL job, it will pickup C:\Users\SQLAgent.

How can identify that ABC profile is corrupted, should I run any commands or tests?
Server does not seem to have local account by that name (ABC), tried Net User command, thank you for the suggestion thou!



0 Votes 0 ·
MotoX80 avatar image
0 Votes"
MotoX80 answered MotoX80 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you @MotoX80
would this command net.exe view \\SomeFileServerName work with sharepoint online thou?
Ours is online.

0 Votes 0 ·

It's been a while since I used Sharepoint and I no longer have access to any SP site. (Retired now.)

You commented that you "run ps1 and connect to Sharepoint" but you didn't provide any details of the exact call that you are making. I assumed that since you mentioned user accounts multiple times, that you are using integrated authentication and not providing credentials on the call.

So my thought was to verify that the SQL Agent is correctly impersonating your ABC account (whoami) and that that account can authenticate to another domain joined server. If that account has access to a share then see if you can get a directory list.

 Get-ChildItem -path \\SomeFileServerName\SomeShareName 

I also wanted to see if maybe some firewall was blocking access and to see what HTTP response you got back from the SP server. Just to verify network connectivity.

On both the SP and the SomeFileServerName servers, the Security eventlog should contain either a success or failure event for the account logon. Have you looked at the Security event log on the SP server?

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello @Pam-7890,

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://docs.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.--

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.