XP_Cmdshell 'Access is denied'

Question

XP_Cmdshell 'Access is denied'

ravisamigo

SSCertifiable

Points: 7440
More actions
May 8, 2012 at 11:19 pm

#261257

Hi All,
xp_cmdshell 'dir \\Server001\e$' - running in Server2
xp_cmdshell 'dir \\Server002\e$' - running in server1
OUTPUT : 'Access is denied.'
But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.
NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.
Please advice.
Thanks and Regards,
Ravi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

ravisamigo (5/8/2012)
Hi All,
xp_cmdshell 'dir \\Server001\e$' - running in Server2
xp_cmdshell 'dir \\Server002\e$' - running in server1
OUTPUT : 'Access is denied.'
But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.
NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.
Please advice.
Thanks and Regards,
Ravi

Please post the result of the foloowing query when executed against the sql server instance

select value_in_use from sys.configurations

where name = 'xp_cmdshell'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Lowell SSC Guru Points: 323518 More actions · Answer 2

ravisamigo (5/8/2012)
Hi All,
xp_cmdshell 'dir \\Server001\e$' - running in Server2
xp_cmdshell 'dir \\Server002\e$' - running in server1
OUTPUT : 'Access is denied.'
But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.
NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.
Please advice.
Thanks and Regards,
Ravi

if you touch anything outside of SQL server (local directories, UNC shares, mapped drives, your own credentials don't count and are not used.

SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.

SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

slash.young SSC Enthusiast Points: 159 More actions · Answer 3

I know this is an old post, my apologies. I had this same issue... so I did the above suggestion and set the Logon account for the SQL Server Service in my config manager to use a domain account instead, so my sysadmin users on the sql server could use xp_cmdshell. This worked and fixed the problem, HOWEVER, in doing so it made the SQL Server instance only accessible to the local box. When trying to access through SSMS from a different box I would get a "Cant generate SSPI Context error".

Any Suggestions?

Thank you in advance

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 4

slash.young (5/8/2014)
I know this is an old post, my apologies. I had this same issue... so I did the above suggestion and set the Logon account for the SQL Server Service in my config manager to use a domain account instead, so my sysadmin users on the sql server could use xp_cmdshell. This worked and fixed the problem, HOWEVER, in doing so it made the SQL Server instance only accessible to the local box. When trying to access through SSMS from a different box I would get a "Cant generate SSPI Context error".
Any Suggestions?
Thank you in advance

It's an old topic again, but can be useful for somebody in the future.

Sometimes when you change the service account (the one who runs the service through SQL Server Configuration Manager), the new account is not able to create the SPN's for the combination Service-Service Account, so Kerberos authentication (Windows) fails

So, you can fix this by removing the old SPN's and creating new ones.

To check that's the problem:

setspn -l OldServiceAccount

setspn -l NewServiceAccount

You should see something like

MSSQLSvc/<FullQualifiedName> or MSSQLSvc/<FullQualifiedName>:InstanceName

MSSQLSvc/<FullQualifiedName>:ListeningPort

if you see then only for the old one, remove them and create new

To remove:

setspn -d MSSQLSvc/<FullQualifiedName> OldServiceAccount

setspn -d MSSQLSvc/<FullQualifiedName>:ListeningPort OldServiceAccount

To add the new one

setspn -s MSSQLSvc/<FullQualifiedName> NewServiceAccount

setspn -s MSSQLSvc/<FullQualifiedName>:ListeningPort NewServiceAccount

It's important to use -s to check for duplicates before adding new SPN's as that can make a bit of a mess.

Regards

mgc SSC Enthusiast Points: 100 More actions · Answer 5

SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:

Thnx for sharing this!!

I just had this issue and I had forgot the solution.

regards

Michael