Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using the UPTIME Utility to Monitor Servers Expand / Collapse
Author
Message
Posted Wednesday, June 23, 2010 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:00 AM
Points: 43, Visits: 104
WizardOfOz - thanks However, i'm extremely wary of 3rd party .exes on our production boxes. I'd strongly recommend testing this thoroughly in a test environment. ...oh the irony of a utility to test uptime that brings your systems down


Post #941762
Posted Wednesday, June 23, 2010 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 6:07 AM
Points: 6, Visits: 21


Well, it's not really an install anyways, just a command-line app. But if you rather use Microsoft's outdated version that crashes - or enable xp_cmdshell then please go ahead

I actually wrote that tool, and it just queries the uptime from a Windows performance counter - so it's pretty safe
Post #941798
Posted Wednesday, June 23, 2010 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 9:04 AM
Points: 8, Visits: 54
I do not agree to enable xp_cmdshell also no longer used in recent versions of SQL. Sorry.

Itu.
Post #941906
Posted Wednesday, June 23, 2010 11:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
Since someone mentioned using CLR to get this information I will say that my SQLCLR project, SQL# (SQLsharp), includes this function. It is usable in SQL Server 2005 and 2008 and is the real system uptime, not the DB uptime. It returns the number of milliseconds since the system was started. This can be used to determine what the exact date & time via:

SELECT DATEADD(MILLISECOND, (SQL#.OS_Uptime() * -1), GETDATE())

I hope this helps.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #941970
Posted Wednesday, June 23, 2010 12:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:00 AM
Points: 43, Visits: 104
WizardOfOz - thanks
I only mentioned testing as each org is different. We use Applocker in our environment to lock down all .exes, .DLLs etc on production boxes so anyone who has a similar setup would need to go through the ropes of testing before approval. Good to know though even if your .exe does not do remote uptime (as yet :)



Post #942009
Posted Wednesday, June 23, 2010 7:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
If you're running it as a SQL Agent job, why not enable xp_cmdshell on the first step, run the utility in the next step then disable xp_cmdshell in the last step. I do that for one of my tasks that requires xp_cmdshell. If it runs in seconds, any attack window is minimal.





Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #942176
Posted Wednesday, June 23, 2010 8:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 6:07 AM
Points: 6, Visits: 21
CosmicTrickster: That's a good point, and is probably acceptable for environments that don't require extremely high security. Still, one would want to make sure that the command being executed can't get stuck - e.g. take a long time to complete for whatever reason. Such a job should then probably be monitored to ensure the xp_cmdshell is disabled again - maybe even with a separate job that runs regardless and disable xp_cmdshell at a given time.

One should also make sure that the file executed through xp_cmdshell can't be easily replaced with a rogue binary by an unauthorized user, but that would apply to any batch job really.

If it's just about inserting some string into a table, then I wonder if it's not more efficient to just write a script that executes whichever utility, and then just adds the row to the table via OLEDB/ODBC/etc.
Post #942184
Posted Wednesday, June 23, 2010 8:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
Good point.

I've never really understood the big fuss over xp_cmdshell being enabled or not. I see how it can be a security risk, and how if SQL security gets compromised then access can be gained to the OS/domain (depending on the SQL Server service account) and limiting collateral damage through siloed access/responsibility but by default only sysadmins can run it. If you control who has sysadmin access, it's not so much of an issue.

Besides which, what is more of an issue - your sysadmin (or hacker) can run files on the OS or he has access to all the <insert sensitive data here>?

Maybe it's because I've never had to deal with systems that are so sensitive you need a minimum of 3 people to accomplish any given task on that system.




Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Post #942200
Posted Thursday, June 24, 2010 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 6:07 AM
Points: 6, Visits: 21
I didn't actually know that only sysadmins can run it, but I believe one can give other users the right to execute xp_cmdshell, or at least this was possible in earlier versions.

I think why the security community doesn't like it, is simply because the benefits don't outweigh the risks, and because a lot of bad things happened because of it.

Here is what I think is a good example. Say yo have a SQL Server which has SQL authentication enabled, and the "sa" account gets compromised. With xp_cmdshell enabled (I'm not sure if the "sa" can enable that), you can now compromise the entire server, and potentially more. Without xp_cmdshell, the incident is limited to the SQL server.

But also, i just opens up a can of worms. You have jobs that depend on it, maybe somebody granted some specific user the right to execute cmdshell, and so forth. Just like you said, as soon as you have a server with multiple admins, it gets tricky.

IMHO it's better to run jobs through the Task Scheduler if they need to run commands in the OS, and then just add/update data in SQL from there if needed.

I don't know when xp_cmdshell was introduced (might have been before SQL 2000), but it comes from a time when security was not taken as seriously as it is today.
Post #942412
Posted Thursday, June 24, 2010 1:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 4, 2012 11:40 AM
Points: 89, Visits: 318
Since tempdb is created everytime the SQL Services are started, why not just check sys.databases? ie: select create_date from sys.databases where name='tempdb'
Post #942684
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse