December 18, 2020 at 3:56 pm
richardmgreen1 wrote:I've seen a way of deleting the folders using cmdshell but I'm wary of enabling it because of the potential security issues.
Heh... it's not the security risk that people have been made to believe. Consider this... if it's "disabled", who can turn it on? The answer is only those people that have "sysadmin" or "control server" privs. That can be anyone that has those privs including someone that hacks the login for anyone that has those privs.
Next, ask yourself who can use it (unless you've made the horrible mistake of grant direct use privs to non-sysadmin people)? It's only the same group of people that have "sysadmin" or "control server" privs.
Disabling it will not prevent a hacker from enabling it. Enabling it will not allow people outside the privileged group to use it. You're concentrating on the wrong things if you consider xp_CmdShell to be a security risk and losing out on a huge amount of good, solid functionality to keep it disabled.
If you're really concerned, have your proc enable it, use it, and then disable it. It won't do any good as I pointed out above but it will give nervous-nellies the nice warm fuzzies. 😉
I thoroughly enjoyed your presentation on this - informative and a real eye-opener - would you consider turning it into an article for SSC?
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
December 19, 2020 at 3:33 am
Jeff Moden wrote:richardmgreen1 wrote:I've seen a way of deleting the folders using cmdshell but I'm wary of enabling it because of the potential security issues.
Heh... it's not the security risk that people have been made to believe. Consider this... if it's "disabled", who can turn it on? The answer is only those people that have "sysadmin" or "control server" privs. That can be anyone that has those privs including someone that hacks the login for anyone that has those privs.
Next, ask yourself who can use it (unless you've made the horrible mistake of grant direct use privs to non-sysadmin people)? It's only the same group of people that have "sysadmin" or "control server" privs.
Disabling it will not prevent a hacker from enabling it. Enabling it will not allow people outside the privileged group to use it. You're concentrating on the wrong things if you consider xp_CmdShell to be a security risk and losing out on a huge amount of good, solid functionality to keep it disabled.
If you're really concerned, have your proc enable it, use it, and then disable it. It won't do any good as I pointed out above but it will give nervous-nellies the nice warm fuzzies. 😉
I thoroughly enjoyed your presentation on this - informative and a real eye-opener - would you consider turning it into an article for SSC?
Thank you for the very kind words and the suggestion, David.
Actually, I've considered writing an article similar to the presentation many times but have been really torn about it. While I have no problem with being heterodoxic, especially when it comes to supposed "Best Practices" that actually aren't, the subject of NOT using xp_CmdShell seems to be ingrained in the very fiber of the community. My question would be, would such an article actually help the community or simply be a waste of everyone's time?
For example... you've seen the presentation and have made a great and thoughtful comment about it... but are you an xp_CmdShell user? If not, then you see the dilemma I've been facing. If you are (especially if it's as a result of seeing the presentation), then I'll have to reconsider because, while the masses may not subscribe to what I have to say, it may help a few and helping even one person is good enough for me to try to help others with the same problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2020 at 9:35 am
david.edwards 76768 wrote:Jeff Moden wrote:richardmgreen1 wrote:I've seen a way of deleting the folders using cmdshell but I'm wary of enabling it because of the potential security issues.
Heh... it's not the security risk that people have been made to believe. Consider this... if it's "disabled", who can turn it on? The answer is only those people that have "sysadmin" or "control server" privs. That can be anyone that has those privs including someone that hacks the login for anyone that has those privs.
Next, ask yourself who can use it (unless you've made the horrible mistake of grant direct use privs to non-sysadmin people)? It's only the same group of people that have "sysadmin" or "control server" privs.
Disabling it will not prevent a hacker from enabling it. Enabling it will not allow people outside the privileged group to use it. You're concentrating on the wrong things if you consider xp_CmdShell to be a security risk and losing out on a huge amount of good, solid functionality to keep it disabled.
If you're really concerned, have your proc enable it, use it, and then disable it. It won't do any good as I pointed out above but it will give nervous-nellies the nice warm fuzzies. 😉
I thoroughly enjoyed your presentation on this - informative and a real eye-opener - would you consider turning it into an article for SSC?
Thank you for the very kind words and the suggestion, David.
Actually, I've considered writing an article similar to the presentation many times but have been really torn about it. While I have no problem with being heterodoxic, especially when it comes to supposed "Best Practices" that actually aren't, the subject of NOT using xp_CmdShell seems to be ingrained in the very fiber of the community. My question would be, would such an article actually help the community or simply be a waste of everyone's time?
For example... you've seen the presentation and have made a great and thoughtful comment about it... but are you an xp_CmdShell user? If not, then you see the dilemma I've been facing. If you are (especially if it's as a result of seeing the presentation), then I'll have to reconsider because, while the masses may not subscribe to what I have to say, it may help a few and helping even one person is good enough for me to try to help others with the same problem.
That's a very good point, and I understand your reticence.
Actually I'm not at the moment, but have in the past needed to do "unusual" things which would have been a lot easier using it, but ended up stitching together various techniques, just to avoid that.
However, long story short, due to partial outsourcing and a change/creep/spread of responsibilities I am no longer in ultimate control of the SQL Server estate - still heavily involved in the database life, but would now need to convince others of changes like this. An article in these pages is easy to cite, and I hope would carry some weight. Are you happy with further sharing of your presentation though, in absence of an article?
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
December 21, 2020 at 10:32 am
Think about it: they consider xp_cmdshell is a security risk, and SolarWinds is not.
Go figure.
Not to mention PowerShell - administrative scripts requiring ultimate access rights are executed by a sysadmin from remote desktops having dozens of automated updates installed just last month , and which cannot be hacked or stolen in any way, of course.
There is no security risk there, no way.
_____________
Code for TallyGenerator
December 21, 2020 at 8:18 pm
That's a very good point, and I understand your reticence.
Actually I'm not at the moment, but have in the past needed to do "unusual" things which would have been a lot easier using it, but ended up stitching together various techniques, just to avoid that.
However, long story short, due to partial outsourcing and a change/creep/spread of responsibilities I am no longer in ultimate control of the SQL Server estate - still heavily involved in the database life, but would now need to convince others of changes like this. An article in these pages is easy to cite, and I hope would carry some weight. Are you happy with further sharing of your presentation though, in absence of an article?
Mr. Edwards,
That's nearly the perfect answer and great incentive. I need to update the presentation for how to do it in the "newer" Windows servers but an article on the subject will open up the opportunity to also share a whole bunch of handy things I've used it for including but not limited to things like human readable error file logging during Bulk Inserts and much, much more that is a pain to do using other methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2020 at 8:21 pm
Think about it: they consider xp_cmdshell is a security risk, and SolarWinds is not.
Go figure.
Not to mention PowerShell - administrative scripts requiring ultimate access rights are executed by a sysadmin from remote desktops having dozens of automated updates installed just last month , and which cannot be hacked or stolen in any way, of course.
There is no security risk there, no way.
+1 Billion. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2020 at 2:48 am
Actually, I was really hoping someone will show where I was wrong in that statement. Otherwise... it’s too sad to realise the truth.
_____________
Code for TallyGenerator
December 23, 2020 at 8:49 pm
Actually, I was really hoping someone will show where I was wrong in that statement. Otherwise... it’s too sad to realise the truth.
You and I go way back, Sergiy (2 banker's rounding threads taught me well 😀 ). I instantly recognized the deep sarcasm and that's what the +1 Billion was for. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2020 at 10:06 pm
You're absolutely right about sarcasm.
And that what I was hoping to be "punished" for by more educated members.
Without any sarcasm this time.
_____________
Code for TallyGenerator
December 24, 2020 at 11:05 am
Hi all
I've come across a weird issue when creating folders using xp_create_subdir.
The folder properties don't appear to be propogating correctly.
I've just tried to access one of the folder to be told I don't have permission (this is logged on with an admin account).
I'm not sure if the folders are now empty (as I can't access them).
Anyone any ideas?
December 24, 2020 at 4:27 pm
Hi all
I've come across a weird issue when creating folders using xp_create_subdir.
The folder properties don't appear to be propogating correctly.
I've just tried to access one of the folder to be told I don't have permission (this is logged on with an admin account).
I'm not sure if the folders are now empty (as I can't access them).
Anyone any ideas?
What was the code that you used xp_create_subdir in (stored proc, job, ???), where was that code executed on (you logged directly into the server using something like RDP, logged directly into the actual server, or logged in from a "normal" SSMS session from another machine like your laptop or desktop or ???)?
Then, how did you attempt to verify that the directories existed (same questions as above for that)?
What I'm thinking is anywhere from a misunderstanding of privs/propagation of privs to the Kerberos "Double-Hop" protection feature (most consider it to be a problem... I consider it to be a security feature that I won't override come hell or high water).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2020 at 7:14 am
Hi Jeff
I tested it by using an SSMS window to run the stored procs (which have parameters passed in to them). That all seemed towork fine.
I've attached the code for people to laugh look at.
I then used SSIS to run the sproc (we do this as standard. We use SSIS to control the flow and to exec stored procedures).
When I came across the issue, I'd used a job to run the SSIS package.
What I did notice after a bit of digging was me being an idiot. I'd left the job to run under the SQL Agent account instead of our proxy account (which maps to a domain account) which I think may have caused the issue. I've now set the job to run under the proxy account (but the job is still disabled).
As for checking the folders, I RDP'd onto the server, checked the relevant drive and made sure the folders existed (I'd done when I was testing the code in SSMS) and they did appear. The issue came when I wanted to clear everything down (i.e. get rid of my test folders) which is when it all went wrong. No-one, including the domain admin, could access the folders to delete them.
I'm not sure if it was they were created using the SQL Agent account or because files had potentially been deleted from those folders using the same account.
I'm hoping using the proxy account to run the job step will now work as intended (i.e. stop it locking everyone out of the folders) but I'm doing some more digging before I enable to job again.
As usual, any comments/help/pointers are greatly appreciated.
Richard
December 29, 2020 at 1:55 pm
It would appear that you forgot to attach the code but, based on the narrative above, it sounds like you're on the right track to a fix.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2021 at 7:28 am
Guys/gals
Thought I'd resurrect this thread as I've come across a new issue.
I decided to use cmdshell to delete empty folders and ..... it's not going well.
Once a folder is empty, I want to delete it so I don't have a lot of empty folder hanging about and clogging up the drive.
This is the code I'm using:-
-- Get initial backup path
EXEC master.sys.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'BackupDirectory'
,@InitialBackupPath OUTPUT
,'no_output';
-- Add in the folder name of the current week we want to check to give the full backup path
SET @BackupPath = CAST(@InitialBackupPath AS VARCHAR(MAX)) + N'\' + @@SERVERNAME + N'\' + @LocalFolderExtension;
SET @rmdircmd = N'RMDIR "' + @BackupPath + '"';
EXEC master.sys.xp_cmdshell @rmdircmd, no_output;
The issue I've now got is that the folder isn't getting deleted, but the permissions are.
I'm runnng the code as me (I'm an admin on the server) and a sysadmin on the SQL install.
For now, I've got two questions:-
Not sure if this is relevant, but I've set the variables used above to NVARCHAR(4000).
Any help on this would be greatly appreciated.
TIA
Richard
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply