none
distributed query for license key RRS feed

  • 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

    Tuesday, March 10, 2015 6:52 PM

Answers

All replies

  • Can you try with Powershell? 

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

    --Prashanth

    Tuesday, March 10, 2015 7:24 PM
  • 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

    Tuesday, March 10, 2015 8:12 PM
  • If you are using "Core" licensing then that would be correct.  Every install uses the same installation key.

    Tuesday, March 10, 2015 8:26 PM
    Moderator
  • 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

     

    Tuesday, March 10, 2015 8:30 PM
  • 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

    Wednesday, March 11, 2015 11:05 PM
  • 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


    Thursday, March 12, 2015 2:47 AM
    Moderator
  • 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

    Thursday, March 12, 2015 3:12 PM
  • >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/

    Thursday, March 12, 2015 3:43 PM
  • Thank you David :).
    Thursday, March 12, 2015 3:45 PM