How Long Before Your Database Runs Out of Space?

Question

How Long Before Your Database Runs Out of Space?

Viewing 8 posts - 31 through 37 (of 37 total)

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

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 1

(apologies for the double post, post never confirmed)

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

"It's like a little doggy door to the house - If you have one, something is bound to try to get in."
That pretty much means you think it's unsafe. However, if they can run it then they've already gotten the keys to the kingdom. And security isn't open to skill in that way. If someone gets in then no amount of skill will give them the perms to run it. So disabling it still won't change anything.
I wind up using cmdshell for all sorts of things that SQL doesn't provide.
And again, the perms required to run it mean that it really doesn't add any fuel to anything. If they've got those perms then they can just enable it. So what fuel is it adding?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 3

KenpoDBA - Wednesday, October 25, 2017 8:13 AM
"It's like a little doggy door to the house - If you have one, something is bound to try to get in."
That pretty much means you think it's unsafe. However, if they can run it then they've already gotten the keys to the kingdom. And security isn't open to skill in that way. If someone gets in then no amount of skill will give them the perms to run it. So disabling it still won't change anything.
I wind up using cmdshell for all sorts of things that SQL doesn't provide.
And again, the perms required to run it mean that it really doesn't add any fuel to anything. If they've got those perms then they can just enable it. So what fuel is it adding?

Since there's nothing that can be prevented by having it disabled,or preventing an account from enabling it, then it doesn't matter if anyone leaves it disabled and doesn't use it. You and I don't agree, I get it. You don't like me analogy, sorry. It is absolutely a "doggy door" to the OS from SQL though. Some raccoon may try to come in and steal your carpet, but whether he can or not depends on other things - and not everyone does those things correctly. I'm sorry you didn't agree with the opinion I shared, which was only tangentially related to the original post from eighteen months ago. You should be careful around old threads - there's a 6 year old xp_cmdshell poll around here somewhere that you may find quite enraging 😉

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Joe O'Connor - Wednesday, October 25, 2017 9:47 AM
KenpoDBA - Wednesday, October 25, 2017 8:13 AM
"It's like a little doggy door to the house - If you have one, something is bound to try to get in."
That pretty much means you think it's unsafe. However, if they can run it then they've already gotten the keys to the kingdom. And security isn't open to skill in that way. If someone gets in then no amount of skill will give them the perms to run it. So disabling it still won't change anything.
I wind up using cmdshell for all sorts of things that SQL doesn't provide.
And again, the perms required to run it mean that it really doesn't add any fuel to anything. If they've got those perms then they can just enable it. So what fuel is it adding?
Since there's nothing that can be prevented by having it disabled,or preventing an account from enabling it, then it doesn't matter if anyone leaves it disabled and doesn't use it. You and I don't agree, I get it. You don't like me analogy, sorry. It is absolutely a "doggy door" to the OS from SQL though. Some raccoon may try to come in and steal your carpet, but whether he can or not depends on other things - and not everyone does those things correctly. I'm sorry you didn't agree with the opinion I shared, which was only tangentially related to the original post from eighteen months ago. You should be careful around old threads - there's a 6 year old xp_cmdshell poll around here somewhere that you may find quite enraging 😉

I believe I was one of the folks that was on that 6 year old thread. It was "quite enraging" because a lot of peoples' arguments were based on visceral fear, a lack of knowledge, and a lot of old knowledge that is no longer applicable.

If you want to consider xp_CmdShell to be a "doggy door", that's up to you. My purpose is to advise you that even if you brick and mortar over the "doggy door", it will not help if you leave the main entrance and all the windows open. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 5

Jeff Moden - Wednesday, October 25, 2017 10:17 AM
Joe O'Connor - Wednesday, October 25, 2017 9:47 AM
KenpoDBA - Wednesday, October 25, 2017 8:13 AM
"It's like a little doggy door to the house - If you have one, something is bound to try to get in."
That pretty much means you think it's unsafe. However, if they can run it then they've already gotten the keys to the kingdom. And security isn't open to skill in that way. If someone gets in then no amount of skill will give them the perms to run it. So disabling it still won't change anything.
I wind up using cmdshell for all sorts of things that SQL doesn't provide.
And again, the perms required to run it mean that it really doesn't add any fuel to anything. If they've got those perms then they can just enable it. So what fuel is it adding?
Since there's nothing that can be prevented by having it disabled,or preventing an account from enabling it, then it doesn't matter if anyone leaves it disabled and doesn't use it. You and I don't agree, I get it. You don't like me analogy, sorry. It is absolutely a "doggy door" to the OS from SQL though. Some raccoon may try to come in and steal your carpet, but whether he can or not depends on other things - and not everyone does those things correctly. I'm sorry you didn't agree with the opinion I shared, which was only tangentially related to the original post from eighteen months ago. You should be careful around old threads - there's a 6 year old xp_cmdshell poll around here somewhere that you may find quite enraging 😉
I believe I was one of the folks that was on that 6 year old thread. It was "quite enraging" because a lot of peoples' arguments were based on visceral fear, a lack of knowledge, and a lot of old knowledge that is no longer applicable.
If you want to consider xp_CmdShell to be a "doggy door", that's up to you. My purpose is to advise you that even if you brick and mortar over the "doggy door", it will not help if you leave the main entrance and all the windows open. 😉

Yes you were, and what you said about security in that thread "if they can do anything but execute a set of sprocs it's not locked down" is still one I go to when "discussing" SQL security with developers who want to know why it works on their laptop, but not the environment I give them.

Sadly, people inherit environments where the application doesn't work unless it's running as sysadmin, and fixing the problem, and the mentality, takes time.

If you happen to pop to downtown Detroit during the day and have an hour to kill, I'll buy the first (reasonable) round of whatever you're having.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Joe O'Connor - Wednesday, October 25, 2017 6:35 AM
Jeff Moden - Tuesday, October 24, 2017 4:50 PM
Joe O'Connor - Tuesday, October 24, 2017 6:56 AM
Jeff Moden - Monday, October 23, 2017 8:07 PM
Joe O'Connor - Friday, May 12, 2017 5:53 AM
SQLBlimp - Wednesday, June 8, 2016 10:23 PM
Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?
Thanks for sharing this! I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity. It does not take SQL File growth size into account, so there may be room for improvement
I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂
SELECT DISTINCT @@SERVERNAME AS ServerName , vs.volume_mount_point AS VolumeName , vs.logical_volume_name AS VolumeLabel , vs.total_bytes AS VolumeCapacity , vs.available_bytes AS VolumeFreeSpace , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;
"Gets you away from xp_CmdShell". Why do you think the use of xp_CmdShell is a problem?
It's like a little doggy door to the house - If you have one, something is bound to try to get in. I would prefer not to enable it if you can do it a different way.
Only people with sysadmin privs can enable it. Only people with sysadmin privs can use it (unless you've made the terrible mistake of allowing non-DBAs to use it with or without a proxy). If an attacker get's in but doesn't attain sysadmin privs (one way or another), they can't use xp_CmdShell. If they do get in with sysadmin privs , having it disable does NOTHING for security except cause a couple of millisecond delay in their attach software. Further, if they make it in with sysadmin, they don't even need xp_CmdShell because they can, quite literally, do anything they want.
Turn on xp_CmdShell and use it. If that makes you paranoid because you need to be paranoid about who gets sysadmin privs either intentionally or during an attack. xp_CmdShell isn't a security risk. Bad security is the security risk that you need to be paranoid about.
Jeff, your writing and articles have always been helpful, so I give some weight to what you say, so I'm a bit at a loss as to why you're attacking my preference of utilizing other methods to get the exact same results. That would be like me replying to one of your comments that someone should use cursors even if there are set-based ways to accomplish the same results. Your points are all valid about how it's not the security hole as I suggested it was, so enable it, disable it, do whatever you like so long as you recognize that its acceptable to not use it too.

Oh no... not attacking your other methods at all. While I've proven to myself and a large number of other folks that the use of xp_CmdShell isn't the security risk, my real goal is to make sure that people that think it adds anything more than a 3ms speed bump to someone's attack software understand that having it turned off does NOTHING to enhance security of the system. You were the one that brought up "Gets you away from xp_CmdShell" and I just want to make sure that people understand that getting away from it does nothing to enhance security and that they must be ever vigilant in other areas.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 7

Jeff Moden - Monday, October 23, 2017 8:09 PM
And, yes, I realize this article is from last year but it's new to me. Nice job, John.

Thank you muchly sir!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

I know I'm a few days late to the party on xp_cmdshell, but last week was ISO-27001 audit week and I was a bit busy. 😉

You can add me to the list of people who understand that xp_cmdshell is not the worst practice it's been made out to be. Disabling it does nothing to "keep the bogey man" out of your database. I guess disabling it could make people feel better, but it's a false sense of security.

Can it be used do damage the server? Well, duh - of course. But look at what's required to use it:

1. Only people with sysadmin privs (or control server, which I don't grant) can use it.
2. Only people with sysadmin privs can turn it on.
3. Hence, it doesn't matter if it's turned on or off because at attacker with sysadmin privs will simply turn it on.

It's as simple as that. The sa login has been around a very long time and is the well-known favorite of attackers. I once got to see a demo of hacking it and it was scary just how easy it was using automated tools. The most effective solution is to disable it and leave it disabled. Not having SQL logins with elevated privs enabled precludes the argument around enabling xp_cmdshell by addressing the real issue of security configruation.

I've heard the argument of renaming it and creating a honeypot sa login, but I don't even go there because it gives the attack vector somewhere to go.
Jeff made the point earlier about not giving people explicit privs to run it. I take it a step further and don't give people permission to run xp_ anything.

Joe, I understand your point about poorly-designed third party apps flipping out if they aren't running with sysadmin privs. I know that's a real problem for some apps. If it were up to me, I wouldn't install something like that so we never develop a dependency on it. If it's required, I would isolate the database for the application to it's own instance. Of course, that's only if I can't get along without the app to begin with.

Getting back to the larger issue of security, an attacker who has sysadmin privs doesn't need xp_cmdshell to do damage. Think about what the login has privs to do - anything the attacker wants. This includes querying all data from all tables in all databases. Such a breach is a complete disaster. The only thing left is to read about it on the news and pay the heavy costs associated with it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions