distributed query for license key

Question

Hello,

I have a distributed query that I'm hoping is retrieving the license key information below:

USE master
GO

create table #version
(
	version_desc varchar(2000)
)

insert #version
select @@version

if exists
(
	select 1
	from #version
	where version_desc like '%2005%'
)
Begin
	DECLARE @Registry_Value_2005 VARCHAR(1000)
	EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup','ProductCode',@Registry_Value_2005 OUTPUT --2005
	SELECT @@version as 'version',@Registry_Value_2005 as 'license_key'
End
else if exists
(
	select 1
	from #version
	where version_desc like '%express%'
)
Begin
	DECLARE @Registry_Value_2008_express VARCHAR(1000)
	EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\Setup','ProductCode',@Registry_Value_2008_express OUTPUT -- 2008 express
	SELECT @@version as 'version',@Registry_Value_2008_express as 'license_key'
End
else if exists
(
	select 1
	from #version
	where version_desc like '%R2%'
)
Begin
	DECLARE @Registry_Value_2008_R2 VARCHAR(1000)
	EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\Setup','ProductCode',@Registry_Value_2008_R2 OUTPUT -- 2008 R2
	SELECT @@version as 'version',@Registry_Value_2008_R2 as 'license_key'
End
else if exists
(
	select 1
	from #version
	where version_desc like '%2008%'
)
Begin
	DECLARE @Registry_Value_2008 VARCHAR(1000)
	EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup','ProductCode',@Registry_Value_2008 OUTPUT -- 2008
	SELECT @@version as 'version',@Registry_Value_2008 as 'license_key'
End
else if exists
(
	select 1
	from #version
	where version_desc like '%2012%'
)
Begin
	DECLARE @Registry_Value_2012 VARCHAR(1000)
	EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup','ProductCode',@Registry_Value_2012 OUTPUT -- 2012
	SELECT @@version as 'version',@Registry_Value_2012 as 'license_key'
End
else
Begin
	select 'version not recognized'
End

drop table #version

I'm noticing the 'key' is coming back the same across our 2012 instances and I'm pretty sure this isn't right. Am I retrieving the right value from the registry? I want to get the actual key that is installed when SQL is installed. Please help also feel free to borrow this code if you like.

Thanks!

phil

Answer 1

Can you try with Powershell? 

https://xzwang.wordpress.com/2013/06/22/retreiving-sql-server-2012-product-key-from-the-registry/

--Prashanth

Answer 2

Sure, but I'm wanting to know if there is a TSQL solution. Plus this can be run as a distributed query across multiple servers at one time. The above code is working, my main question is whether I'm retrieving the right value or not from the registry.

Thanks,

Phil

Answer 3

If you are using "Core" licensing then that would be correct.  Every install uses the same installation key.

Answer 4

Tom,

Can you expand a bit more? What's the difference between the value in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup for ProductCode

&

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup for DigitalProductID

 

Answer 5

Can anyone answer whether or not the CD/license key (the one you see when you install SQL) is relevant if you are doing per core licensing? I'm gathering it's not..

Thanks,

phil

Answer 6

Hi phil,

The following query will return the serial number in binary format, you can convert this binary value to product key as other post. For more details, please review this similar blog.

use master

GO

exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup','DigitalProductID'

GO

Regarding to the product code, SQL Server consists of different products registered in the Registry. Each product has a product code (a GUID) as well as an installation package code (also a GUID). For more details, please review this similar thread.

Additionally, for license issues, please call 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time) to speak directly to a Microsoft licensing specialist. For international customers, please use the Guide to Worldwide Microsoft Licensing Sites to find contact information in your locations.

Thanks,
Lydia Zhang

---

Lydia Zhang
TechNet Community Support

Answer 7

Thanks Lydia. My main question is does the PRODUCT key have any bearing on licensing or is all that is important is the number of cores and the type of SQL Server you are running?

Thanks,

phil

Answer 8

>does the PRODUCT key have any bearing on licensing?

None at all.  There's no record on the server of the licenses assigned to the server.  You need to handle that externally.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 9

Thank you David :).