We want to retrieve product key of SQL server. We have multiple versions of SQL and understood that product key's path changes for each version. We don't want to hard code the path hence searching for a generic logic. As every version has different folder structure to store product key, we are using Switch statement as
switch (majorVersion)
{
case "8":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration";
break;
case "9":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\90\ProductId";
break;
case "10":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup";
break;
case "11":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup";
break;
case "12":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup";
break;
case "13":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\130\Tools\Setup";
break;
case "13.1":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\130\Tools\Setup\Client_Components_Full\1033";
break;
case "14":
registryPath = @"SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup";
break;
default:
registryPath = @"SOFTWARE\Microsoft\Windows\CurrentVersion";
break;
}
Want to avoid this switch and retrieve key without specifying hard coded path.
How can we get correct product key of any installed sql version?
( Looking for sql script solution only and not power shell.)
2条答案
按热度按时间rkue9o1l1#
The trick to do this in a dynamic way is to look in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
and read the value of the subkeys. e.g. MSSQL14.MSSQLSERVER, SSRS, ...Use the subkey value in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Setup
to read in theVersion
value. The version number is needed to change behavior for older versions.When the first two digits are higher than 11, read in the
DigitalProductID
value at the same location.Here is some C# example code. It is further explained on Max Volkov's blog . It outlines some concerns regarding the base24 decoding of the binary key too.
It is possible to recreate the code in T-SQL, but to me it does not seem the ideal language for all this.
There is a cmdlet Get-DbaProductKey , part of https://github.com/dataplat/dbatools .
And since this page ends up high in search results, here is another way to retrieve the key:
Learn how to find the product key for SQL Server Reporting Services (SSRS) 2017 and 2019 so you can install your server in a production environment.
https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/find-reporting-services-product-key-ssrs
snz8szmq2#
This reads from the registry: