SQL server OS name

Question

Post reply

SQL server OS name

UmaShankar Patel

Ten Centuries

Points: 1142
More actions
April 30, 2015 at 12:24 am

#297554

Hi,
Is there any way to find out Full SQL server's Operating System Name.
If I am using exec xp_msver it shows WindowsVersion,But I need original OS Name.Becoz WindowsVersion is same for many OS.
Plz Help.
Thanks.......
-----------------------------------
My Blog[/url] | Articles

Viewing 8 posts - 1 through 7 (of 7 total)

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

ninad.soman0 Ten Centuries Points: 1111 More actions · Answer 1

Hi,

A more simpler

Select @@version or

select * from sys.dm_os_windows_info

will give you the Windows Version name.

You will have to compare the returned value to find the OS name.

For your reference -

Windows NT 5.0 = Windows 2000

Windows NT 5.1 = Windows Server 2003

Windows NT 5.2 = Windows Server 2003 R2

Windows NT 6.0 = Windows Server 2008

Windows NT 6.1 = Windows Server 2008 R2

Windows NT 6.2 = Windows Server 2012

Windows NT 6.3 = Windows Server 2012 R2

Thank you,

Ninad

UmaShankar Patel Ten Centuries Points: 1142 More actions · Answer 2

I have checked it but problem is

NT 6.3 :=> Windows 8.1

NT 6.2 :> Windows 8

http://en.wikipedia.org/wiki/List_of_Microsoft_Windows_versions

Thanks.......
-----------------------------------
My Blog[/url] | Articles

ninad.soman0 Ten Centuries Points: 1111 More actions · Answer 3

Hi,

Check out

https://msdn.microsoft.com/en-us/library/windows/desktop/ms724832(v=vs.85).aspx

I guess confusion will be for Windows Server 2003 and Win2K3 R2. 🙂

but for others its just a difference of whether server edition or desktop/home OS

dan-572483 SSCoach Points: 15870 More actions · Answer 4

Don't you love how Windows 8.1 and Windows 6.3 are the same thing?

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 5

You can do this with PowerShell.

And if you have xp_cmdshell enabled (not encouraged for security reasons) you can run it from an SSMS Query window.

exec xp_cmdshell 'powershell.exe -noprofile -command "Get-WmiObject -Class Win32_OperatingSystem | Select Caption"'

output

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULL

Caption

-------

Microsoft Windows Server 2008 R2 Standard

NULL

(7 row(s) affected)

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

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

Richard Fryar (5/8/2015)
You can do this with PowerShell.
And if you have xp_cmdshell enabled (not encouraged for security reasons)...

What security reasons? If an attacker gets into your system without "SA" privs, they can't use xp_CmdShell even if it's enabled. If an attacker gets into your system with "SA" privs, having it disabled will not stop them from using it because they can turn it on.

There is virtually no security advantage to be had in turning off xp_CmdShell.

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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 7

Hi Jeff,

I am aware of your slide set on the subject and I agree with what you say.

Some comments though:

You assume an attacker with the skills to obtain SA privs and with malicious intent. In most shops you are more likely to get a developer who has sloppily been granted membership of sysadmin in production. Some damage could be done here, even if unintentional, and adding the requirement to enable xp_cmdshell first provides extra peace of mind. Yes I know the developer shouldn't have that level of access, but I've seem it happen in a lot of companies.

A Server Audit Specification writing to the Windows Security Log will detect the login and IP address of a user when they enable xp_cmdshell. And if they disable the audit first, that action itself will also be audited. For more secure auditing there are tools such as Idera SQLcompliance Manager, which claim to have tamper detection built to prevent attackers covering their tracks. I do agree with you, though, that this is too little too late, but it may act as a deterrent for the less determined attacker.

Try explaining to a spotty 18 year-old auditor why xp_cmdshell is enabled, when their 10 year-old guidelines say you should not do it. Maybe I've been unlucky, but explaining anything technical to these people is next to impossible.

Point taken though. As always, your arguments make a lot of sense.

Richard

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com