SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XP_Cmdshell 'Access is denied'


XP_Cmdshell 'Access is denied'

Author
Message
ravisamigo
ravisamigo
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 760
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55074 Visits: 17700
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
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73289 Visits: 40963
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
slash.young
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 42
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
sqldoubleg
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1467 Visits: 1363
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
mgc
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 14
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search