May 2, 2018 at 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???
May 2, 2018 at 8:47 pm
jack_walker - Wednesday, May 2, 2018 1:40 PMI 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
Change is inevitable... Change for the better is not.
May 3, 2018 at 6:32 am
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!!!
May 3, 2018 at 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?!?
May 3, 2018 at 7:09 am
jack_walker - Thursday, May 3, 2018 6:50 AMAlso.... 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
May 3, 2018 at 7:18 am
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?
May 3, 2018 at 8:02 am
jack_walker - Thursday, May 3, 2018 7:18 AMFor 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
May 3, 2018 at 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!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2018 at 11:30 am
Jeff Moden - Thursday, May 3, 2018 11:12 AMOk... 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
May 3, 2018 at 11:54 am
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?
May 3, 2018 at 11:58 am
Jeff Moden - Thursday, May 3, 2018 11:12 AMOk... 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!
Can you give me a little more detail on what you mean by "well controlled, well written stored procedure"? The web application is using parameters in the sql to guard against SQL Injection...
May 3, 2018 at 12:08 pm
jack_walker - Thursday, May 3, 2018 11:54 AMThanks 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?
No it's not safe. Most of the time I limit what a stored procedure does to interacting with the data, database. I think there is often a tendency to start doing things with SQL Server that it's wasn't really meant to do and/or doesn't do well. Just because you can, doesn't mean you should 🙂
When I read this part -
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.
It just doesn't seem to be doing much that has anything to do with SQL Server. It seems like you could output the file name in the stored procedure so it's available to the application and then manage the rest of the process in the Application.
Sue
May 3, 2018 at 12:57 pm
If I have only one SQL Server login (with a strong password) that only has access to 1 database but that can call xp_cmdshell via a stored procedure called by 1 application - still too dangerous?
May 3, 2018 at 1:15 pm
jack_walker - Thursday, May 3, 2018 12:57 PMThanks Sue!!! That all makes sense. But... do I still have the same issue - I am just handing off the more dangerous part - moving the file from one server to another - to the application? I will still need to use credentials that have the proper security to copy the files between servers.If I have only one SQL Server login (with a strong password) that only has access to 1 database but that can call xp_cmdshell via a stored procedure called by 1 application - still too dangerous?
Whether it's one or fifty logins, it's still a risk whenever non-sysadmins have access to objects like that. When it's used for something that really isn't a SQL Server process, that seems to be elevating a SQL Server users permissions needlessly. Even if it's just one login. Even if you plug security holes as best as possible, it's still a hole that can be compromised. Access to xp_cmdshell is a whole lot more than an account that is limited to just copying files and sending an email. If a login has access to xp_cmdshell, it opens a hole to a lot more than just copying files and sending an email. The principle of least privileges would be an account that the app can use with just those permissions needed.
One other thing to think about is auditors used to ask for just sysadmins and then some of the other roles in the database. Many seem to have switched where they ask who has access or permissions to their list of stored procedures/extended stored procedures.xp_cmdshell seems to always be in that list.
Sue
May 3, 2018 at 1:22 pm
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply