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


Proxy account not working in stored procedure


Proxy account not working in stored procedure

Author
Message
jack_walker
jack_walker
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 14
I have a stored procedure that uses xp_cmdshell to copy a file from one folder to another (right now just trying to copy to a folder on the same server but eventually needs to copy to a share on another server). I am getting "access denied" even though I have set up a ##xp_cmdshell_proxy_account## credential and a powershell proxy account linked to the credential. The credential are tied to my own network credentials which has full access to the folder. This is all on a Windows Server running SQL Server 2017. I had this all set up on an older server with no issue. Any ideas? Thanks in advance!

It seems like the stored procedure is not picking up the proxy account???
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)

Group: General Forum Members
Points: 786048 Visits: 45914
jack_walker - Wednesday, May 2, 2018 1:40 PM
I have a stored procedure that uses xp_cmdshell to copy a file from one folder to another (right now just trying to copy to a folder on the same server but eventually needs to copy to a share on another server). I am getting "access denied" even though I have set up a ##xp_cmdshell_proxy_account## credential and a powershell proxy account linked to the credential. The credential are tied to my own network credentials which has full access to the folder. This is all on a Windows Server running SQL Server 2017. I had this all set up on an older server with no issue. Any ideas? Thanks in advance!

It seems like the stored procedure is not picking up the proxy account???


Why are you involving PowerShell for this? Just use xp_CmdShell directly. Either that or just use PowerShell. There's no need to use both and I think it may be a part of the problem on newer servers.

If you could post the code that created the Cmd Shell proxy, that may also help.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jack_walker
jack_walker
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 14
So I have it down to this... I have my own Windows network credentials loaded in ##xp_cmdshell_proxy_account##. I have granted execute on cmd_shell to my network account. I then login as a standard SQL Server login and try executing the following:

DECLARE @cmd varchar(1000)
SET @cmd = 'copy \\server\c$\folder\file.txt \\sameserver\newfolder\'
PRINT @cmd
EXEC xp_cmdshell @cmd


My expectation is that when the exec runs it will use the ##xp_cmdshell_proxy_account## which has my network credentials stored. My network credentials are listed as an admin on the server - so there should be no problem and I granted execute permission on xp_cmdshell to my network user account. But, when I simply run the command in Management Studio logged in as the SQL Server login, I get the error:

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

The overall goal is for a .net application to kick off a stored procedure which writes data from the database to a file and then copies the file to a web server. The stored procedure then fires a web service which sends the user a link to the file. I had this all working on an older SQL Server machine but now cannot replicate it on our new machine. I seem to be stuck and pulling my hair out. UGH!

Thanks for the help!!!

jack_walker
jack_walker
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 14
Also.... When I login to Management Studio with my network credentials and try running the above xp_cmdshell - it runs but I get "Access is denied. 0 file(s) copied". But, I am an admin on the server!!! None of this makes sense to me?!?

Sue_H
Sue_H
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58384 Visits: 12837
jack_walker - Thursday, May 3, 2018 6:50 AM
Also.... When I login to Management Studio with my network credentials and try running the above xp_cmdshell - it runs but I get "Access is denied. 0 file(s) copied". But, I am an admin on the server!!! None of this makes sense to me?!?


For the first error you posted before this, you still need to grant execute permissions on xp_cmdshell to the standard user. If you grant execute and do NOT have a proxy, they will get an error.
For this last error, you are referencing another server with a unc path and using an admin share when you use:
copy \\server\c$\folder\file.txt \\sameserver\newfolder\
Using an admin share isn't a good idea and anyone executing that would need to be an administrator on that other server you reference. You should create a normal share and grant the permissions on that share.

Sue



jack_walker
jack_walker
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 14

For the first error you posted before this, you still need to grant execute permissions on xp_cmdshell to the standard user. If you grant execute and do NOT have a proxy, they will get an error.

I have set up the credential ##xp_cmdshell_proxy_account## with my own network credentials (to test). I was under the impression that when the standard SQL Server user tried to execute xp_cmdshell, that SQL Server would use the credentials stored in ##xp_cmdshell_proxy_account## to execute. Is that not right?

Sue_H
Sue_H
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58384 Visits: 12837
jack_walker - Thursday, May 3, 2018 7:18 AM

For the first error you posted before this, you still need to grant execute permissions on xp_cmdshell to the standard user. If you grant execute and do NOT have a proxy, they will get an error.

I have set up the credential ##xp_cmdshell_proxy_account## with my own network credentials (to test). I was under the impression that when the standard SQL Server user tried to execute xp_cmdshell, that SQL Server would use the credentials stored in ##xp_cmdshell_proxy_account## to execute. Is that not right?


The proxy account is to allow a non-sysadmin to execute xp_cmdshell. Otherwise you can grant permissions for a non-sysadmin to execute xp_cmdshell and they would not be able to since the are not sysadmins.
When you execute sp_xp_cmdshell_proxy_account, you don't need to use a sysadmin - the user will connect to windows under the security context of the credentials used for the proxy. And the user needs to have permissions to execute xp_cmdshell to actually invoke the process with the proxy. Once they log on and nothing else, they aren't operating under the security context of the proxy. It's only when they execute xp_cmdshell that the proxy becomes involved and they can't get there if they don't have permissions to execute xp_cmdshell in the first place. This article has a decent walkthrough on creating the proxy:
Creating a SQL Server proxy account to run xp_cmdshell


Sue



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)

Group: General Forum Members
Points: 786048 Visits: 45914
Ok... this is all going the wrong way. NEVER NEVER NEVER grant privs to any user or application login to execute xp_CmdShell directly! NEVER! If they need to do something that requires the use of xp_CmdShell, then only allow them to use a very well controlled, very well written stored procedure and don't forget that there IS such a thing as DOS INJECTION!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sue_H
Sue_H
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58384 Visits: 12837
Jeff Moden - Thursday, May 3, 2018 11:12 AM
Ok... this is all going the wrong way. NEVER NEVER NEVER grant privs to any user or application login to execute xp_CmdShell directly! NEVER! If they need to do something that requires the use of xp_CmdShell, then only allow them to use a very well controlled, very well written stored procedure and don't forget that there IS such a thing as DOS INJECTION!

If you grant a non-sysadmin permissions to xp_cmdshell they can't execute it without the proxy account. So it seems what you are saying is really don't ever use the xp_cmdshell proxy account. To use it, you grant execute permissions to a non-sysadmin.
I was explaining why they got the errors they got as they were using it on a different server and didn't have issues.

Sue




jack_walker
jack_walker
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 14

Thanks so much Sue!!!! I finally got it all working.

I am still wondering though if I am doing this the best way. So I have a web application that users can choose different criteria on a form - the web application then fires a SQL Server stored procedure - the stored procedure runs a select query based on their criteria and outputs the data to a file - the stored procedure then copies that file to another folder on our web server (using xp_cmdshell) - the stored procedure then calls a web service which emails the user a link to their file.

Is this the best/safest way to accomplish the task?

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