• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 986
  • Last Modified:

How to identify SQL Server version from program files for Auditing/Licensing Purposes

Hi Guys,
We are using Numara Track-IT Enterprise 8.5 as our helpdesk software.  Track-IT has an audit tool that automatically scans every machine and lists all of the software.  I'm in the process of setting up the licensing module that reconciles the audited software against licenses.

The problem I'm having is with SQL server and identifying all of the different versions from the files picked up by the audit.  

Track-IT allows you to create a software title  based on a set of files, so I can select different versions of 'SQLServr.exe' and call it 'SQL 2005 Enterprise edition'.  I can then select other versions of the same file and call the software 'SQL 2005 Standard edition' etc.

The Track-IT audit software lists the File Name, File Version and Product Version.  I have done some research on the internet and discovered an article at : http://support.microsoft.com/default.aspx/kb/321185 that lists different versions e.g. SQL Server
Release                  SQLServr.exe
SQL Server 2005 Service Pack 1       2005.90.2047
SQL Server 2005 Service Pack 2       2005.90.3042
SQL Server 2005 Service Pack 3       2005.90.4035

The trouble is that when I create a software title from these versions, its listing servers that have Enterprise, Standard and the desktop edition that comes with things like backup software and anti-virus etc.

Can anyone point me in the direction of a complete list of either the file versions above for sqlservr.exe  or some collection of files, so that I can positively identify which files make up the different versions of SQL Server, including the free desktop edition (Not sure what the correct name is for it).  That way I can create all the different software titles and allocate licenses against them.  If I don't get this right, it looks like I have loads of unlicensed software on the servers.

Thanks for your help guys.
0
ianwills
Asked:
ianwills
  • 5
  • 5
1 Solution
 
Anthony PerkinsCommented:
The versions will be the same across editions.  You will have to check the edition by doing something like this:
Select SERVERPROPERTY('Edition')
0
 
ianwillsAuthor Commented:
Hi acperkins,

I know what version of SQL server I have on each server or at least I know the ones that require licenses e.g. not MSDE.  The problem I'm having is finding a way to let my helpdesk software identify the version from the files it scans.

I was under the impression that you could tell the version from 'sqlservr.exe'.  My helpdesk software picks this file up and shows all the different versions of the file.  In theory I would just need to put all the SQL Server 2005 files together for example and create a software title called 'SQL Server 2005'.  Whenever the helpdesk software finds those version of the file on one of my servers, it would allocate a license to it.  

I have manually gone round a few of the servers, right-clicking the file and writing down its version number.

It was all going well until I discovered that my licensed SQL Server 2005 64 bit SQL server had the same 'sqlservr.exe' file version (2005.90.3042.00) as one of my other Servers, which has SQL 2005 MSDE installed for anti-spam software.  This would cause my helpdesk software to allocate a license against a server that didn't require one and make it look as though I was using unlicensed software if we were audited by Microsoft.

Is there another way of identifying SQL server from file versions installed anywhere on the server?
0
 
Anthony PerkinsCommented:
>>Is there another way of identifying SQL server from file versions installed anywhere on the server?<<
That is exactly what I am trying to get across to you.  You need to be able to distinguish between SQL Server Editions (Enterprise, Standard, WorkGroup, Developer, MSDE, Express, Personal, etc.) as they all could potentially have the same version.  One way to accomplish this is to use the SQL Server T-SQL command:
Select SERVERPROPERTY('Edition')
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
ianwillsAuthor Commented:
Hi acperkins,
I apologise if I haven't made my self clear.

I understand that I can find the information out myself by running 'select SERVERPROPERTY ("Edition")', but that wont do me any good.  I am trying to find a way for my helpdesk auditing tool to be able find the edition automatically.  It only works by scanning computer/servers for any files and discovering their names and versions.  It can't run SQL commands, it simply lists every file it has found on every computer it scans.

I then need to group the files that it discovers into groups and give them a collective name that describes the software e.g. 'SQL Server 2000', 'Office 2007', 'Faxmaker' etc.  I then allocate how many licences I have for the software and my helpdesk application checks the difference between how many versions of that software it finds on my network and either shows I have too many or too few licences.

It all seems to be working well for me so far, apart from SQL server, because I can't find a file or group of files that distinguish one version from another.
0
 
Anthony PerkinsCommented:
If the auditing tool you are using can only retrieve the version and cannot run any Windows command (let alone SQL command), then quite simply you are out of luck.
0
 
ianwillsAuthor Commented:
Do you know of an auditing tool that can run SQL queries?
0
 
Anthony PerkinsCommented:
I am afraid I do not.
0
 
ianwillsAuthor Commented:
Ok, thanks for the help.  I'll leave the call open for a few days, just in case anyone else lokks at it.
0
 
ianwillsAuthor Commented:
Apparently Centennial Software will bring back the version information and is used by the more expensive version of our helpdesk, so we may upgrade in the future.

In the mean time a work around suggested by one of the Numara support guys, Is to run the query manually to collect results in a file named c:\version.txt on each machine.

Next, amended the File Capture list in Tools > Administration Console > Configuration > Inventory > Auditing > File Captures, adding c:\version.rpt on the next available line.

The captured information can then be added to the different SQL Software titles and show the correct software versions against licenses.

Not the best solution, but it will have to do until we upgrade out software.

Thanks for the help
0
 
trimblenetCommented:
thinking outside the square for a second, could you not use a web server to run the query and output the results to a text file then have the audit software record the details of the text file?
0
 
Anthony PerkinsCommented:
trimblenet,

I am not sure if you noticed, but this question is nearly 2 years old.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now