InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:09:09 GMT20http://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxSince 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'Thu, 24 Jun 2010 13:59:19 GMTtewhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxI 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.Thu, 24 Jun 2010 07:03:52 GMTWizardOfOzhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxGood 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 &lt;insert sensitive data here&gt;?Maybe it's because I've never had to deal with systems that are [i]so[/i] sensitive you need a minimum of 3 people to accomplish any given task on that system.Wed, 23 Jun 2010 20:56:59 GMTThe CosmicTricksterhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxCosmicTrickster: 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.Wed, 23 Jun 2010 20:03:50 GMTWizardOfOzhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxIf 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.Wed, 23 Jun 2010 19:55:11 GMTThe CosmicTricksterhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxWizardOfOz - thanksI 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 :)Wed, 23 Jun 2010 12:40:00 GMTlake_xphttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxSince 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:[code="sql"]SELECT DATEADD(MILLISECOND, (SQL#.OS_Uptime() * -1), GETDATE())[/code]I hope this helps.Take care,Solomon...Wed, 23 Jun 2010 11:48:41 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxI do not agree to enable xp_cmdshell also no longer used in recent versions of SQL. Sorry.Itu.Wed, 23 Jun 2010 10:14:26 GMTDaniel Iturriagahttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspx:-)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 :-)Wed, 23 Jun 2010 08:24:23 GMTWizardOfOzhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxWizardOfOz - 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 :-)Wed, 23 Jun 2010 08:00:03 GMTlake_xphttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxlake_xp: Check out the NTToolkit [url]http://www.netikus.net/products_nttoolkit.html[/url], it also includes a "uptime.exe". For a simple display, just run "uptime /onetime". Unfortunately it doesn't run against remote machines (yet).Wed, 23 Jun 2010 07:10:39 GMTWizardOfOzhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxI would highly recommend against enabling xp_cmdshell, enabling this feature just opens up a can of worms - especially when the SQL Server service is running under a privileged account like the LocalSystem user.We use EventSentry ([url]http://www.eventsentry.com[/url]) to track server uptime - it keeps a log of all reboots, shows graphs of server's uptimes (e.g. for comparison), logs the longest uptime for a server and more. It also monitors services, if one wants to track the SQL Server services themselves.Wed, 23 Jun 2010 07:05:35 GMTWizardOfOzhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxGood Article, uptime is sadly outdated and often crashes on Win2008Wed, 23 Jun 2010 07:02:47 GMTlake_xphttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxThe one problem we have with measuring uptime is that the server uptime doesn't necessarily mean the SQL Server was accessible at all times. We made a custom check to run from a centralized server to check connectivity every 9 minutes to make sure a simple select runs against master. However, it's hard to measure the pct of uptime strictly of SQL Server.The logic I currently try to use it 100-((num_of_connectivity_failures/160)*100) = pct_uptime_for_connectivty_to_sql1440 minutes in a day / 9 minutes = 160 connectivity checks per day.For our missions critical systems, we check connectivity to the individual databases every minute, so the checks would be more 1440 checks per day and the percentage becomes more accurate.Any other thoughts / suggestions are always welcomed though.SteveWed, 23 Jun 2010 06:45:35 GMTS. Kusenhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxPowershell would be a good option and the one I'd pursue.However for those that don't want to dabble in Powershell and don't want to enable xp_cmdshell, you can pipe the output from uptime into a text file and then use osql/sqlcmd to import the file into the DB.Wed, 23 Jun 2010 04:04:00 GMTphilcarthttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxAs you said in the article, enable xp_cmdshell is not the best option and a solution would be to create a CLR function to get the uptime.But, as the uptime it has nothing to do with SQL Server, because it's a Windows "function", it might be a good idea to get the information using a PowerShell script.Wed, 23 Jun 2010 03:14:39 GMTJosephttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspx[quote]Secondly, that won't work for a server that you might want to monitor that doesn't actually have SQL Server installed on it! [/quote]Since I'm a DBA, i'm only interested in servers which have SQL installed (I only get payed for that)Another way to determine the uptime for SQL Server is to query the creation time from TempDBWed, 23 Jun 2010 03:05:24 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspx[quote][b]drikusvd (6/22/2010)[/b][hr]For SQL Server 2008 you can execute the following query to obtain the server start time. This is a lot simpler than running a command line utility.[/quote]Well, except that gives you the time the SQL Server started, which isn't necessarily the same as the time the server started. Secondly, that won't work for a server that you might want to monitor that doesn't actually have SQL Server installed on it!Wed, 23 Jun 2010 01:06:21 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxFor SQL Server 2008 you can execute the following query to obtain the server start time. This is a lot simpler than running a command line utility.SELECT sqlserver_start_time FROM sys.dm_os_sys_infoTue, 22 Jun 2010 22:35:32 GMTdrikusvdhttp://www.sqlservercentral.com/Forums/Topic941473-2723-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Monitoring/70349/">Using the UPTIME Utility to Monitor Servers</A>[/B]Tue, 22 Jun 2010 21:48:52 GMTRichard F. Pless