0

I'm using xp_cmdshell to get files information from network share like this:

EXEC master..xp_cmdshell 'dir \\Server\share\folder'

but i get output 'access is denied'

I figured out my account was NT Service\mssqlserver that can't get access to local system. In my case I can't change the service account to local system because it will require restarting the service.

Is there a way to get access with NT Service or anything I can do? Thanks for the help.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Reinardus Hans
  • 77
  • 2
  • 8
  • 1
    Does this answer your question? [Access denied for enabled xp\_cmdshell for the admin user](https://stackoverflow.com/questions/10822464/access-denied-for-enabled-xp-cmdshell-for-the-admin-user) – JeffUK Aug 18 '20 at 07:02
  • 1
    The right way to do this (not suggesting `xp_cmdshell` is the right way to do anything) is to run SQL Server under a domain account and assign the share permissions needed. Without changing the service account, try adding `Everyone` to the share with Read permissions. – Dan Guzman Aug 18 '20 at 09:48

1 Answers1

1

If you are using a domain:

  • you can to grant access on the share to the computer account of the server where SQL Server is running. For example, if SQL Server is running on SERVER1 and you need to access a share on SERVER2, you should grant read permissions to YOURDOMAIN\SERVER1$. This will allow access to this share to any service that is running on SERVER1 with network access (Local System, Network Service, any NT Service), in other words, all services except those running under Local Service or a particular user account.
  • a better option (as already pointed out in the comments) would be to change the service account for SQL Server, using a domain user for which you grant the appropriate permissions on the share.

If you are not using a domain, I believe the only options are:

  • grant access on the share to Everyone or Guest (which is not recommended)
  • change the service account for SQL Server, using a local user which has the same passwords on both machines
  • change the service account for SQL Server, using a Microsoft Account
Razvan Socol
  • 5,426
  • 2
  • 20
  • 32