How to get the SQL Express Offline Installer

When working in a test environment you don't exactly want something to have internet access; so you need to be able to download files in an offline setting.  I had this need when I was working on a SQL numbering issue where SQL server added 1000 to the item count.  Not particularly helpful and annoying to fix.  The SQL version I was working with was 12 (or 2014) for those at home keeping track by year.  At any rate I needed to fix this 1000 item count but part of the issue was that the count is a primary key.

To get the offline install of SQL Server in this case we are going to use Express 2019, we need to go to https://www.microsoft.com/en-ca/sql-server/sql-server-downloads

https://www.microsoft.com/en-ca/sql-server/sql-server-downloads

When you download the Express download you will get a Application download called SQL2019-SSEI-Expr.  

Run the application.  When the application runs you get three options.  Basic, Custom and Download media which we are going to do.

When you select download media you get a few more options.  For the purposes of what I want I am selecting Express Advanced.

After pressing download; it will download to the directory where you have specified it to download.  In my case C:\Users\Trevor Tye\Downloads

Here I have it in my download directory.

How to find the Windows Product Key using Powershell

I have had the need recently to get the product keys from some new windows laptops.  Now automated ticketing systems such as Lansweeper and Solarwinds can scan for these keys and there are free tools to save these keys to txt files such as MagicJellyBean Keyfinder.  However what I eventually want this to do is put the data into a Microsoft 365 or google workspaces spreadsheet. This is because I have to inventory some non-domain connected systems and there is not ticketing/inventory system on the network.

So I made a powershell script which will eventually save the information to a Microsoft or Google Workspaces spreadsheet but for now just saves it to a text file.  I have some work to do on the CD Key detection in powershell to remove the vbscript as the powershell script doesn't seem to find the right key.  I am using a vbscript from 

techspot that finds and saves the CD Key properly.  After the VBScript has been exicuted the powershell script finds they other key entries and adds them to the outputted file.  Windows has a couple different places where you can find different product keys such as the OEM key from vendors such as Dell, Lenovo, etc; and the BackupProductKeyDefault in the registry.  To find the OEM Key you can find your key using this powershell command.

wmic path SoftwareLicensingService get OA3xOriginalProductKey 

or 

powershell "(Get-WmiObject -query 'select * from SoftwareLicensingService').OA3xOriginalProductKey"

To find the backup Windows Key in the registry it is located using the regedit path below.

HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\SoftwareProtectionPlatform BackupProductKeyDefault

The way I have the scripts working together is I have a bat file which runs the powershell script.  The first thing the powershell script does is runs the CDKey.vbs script.  Then the powershell script appends the rest of the key data to the text file generated by CDKey.vbs.  You can see the code for the 3 scripts below.

CDKey.vbs

Option Explicit

Dim objshell,path,DigitalID, Result

Set objshell = CreateObject("WScript.Shell")

'Set registry key path

Path = "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\"

'Registry key value

DigitalID = objshell.RegRead(Path & "DigitalProductId")

Dim ProductName,ProductID,ProductKey,ProductData

'Get ProductName, ProductID, ProductKey

ProductName = "Product Name: " & objshell.RegRead(Path & "ProductName")

ProductID = "Product ID: " & objshell.RegRead(Path & "ProductID")

ProductKey = "Installed Key: " & ConvertToKey(DigitalID)

ProductData = ProductName & vbNewLine & ProductID & vbNewLine & ProductKey

'Save Data to a file

Save ProductData

'Convert binary to chars

Function ConvertToKey(Key)

Const KeyOffset = 52

Dim isWin8, Maps, i, j, Current, KeyOutput, Last, keypart1, insert

'Check if OS is Windows 8

isWin8 = (Key(66) \ 6) And 1

Key(66) = (Key(66) And &HF7) Or ((isWin8 And 2) * 4)

i = 24

Maps = "BCDFGHJKMPQRTVWXY2346789"

Do

Current= 0

j = 14

Do

Current = Current* 256

Current = Key(j + KeyOffset) + Current

Key(j + KeyOffset) = (Current \ 24)

Current=Current Mod 24

j = j -1

Loop While j >= 0

i = i -1

KeyOutput = Mid(Maps,Current+ 1, 1) & KeyOutput

Last = Current

Loop While i >= 0

If (isWin8 = 1) Then

keypart1 = Mid(KeyOutput, 2, Last)

insert = "N"

KeyOutput = Replace(KeyOutput, keypart1, keypart1 & insert, 2, 1, 0)

If Last = 0 Then KeyOutput = insert & KeyOutput

End If

ConvertToKey = Mid(KeyOutput, 1, 5) & "-" & Mid(KeyOutput, 6, 5) & "-" & Mid(KeyOutput, 11, 5) & "-" & Mid(KeyOutput, 16, 5) & "-" & Mid(KeyOutput, 21, 5)

End Function

'Save data to a file

Function Save(Data)

Dim fso, fName, txt,objshell,UserName

Set objshell = CreateObject("wscript.shell")

fName = ".\ProductKey.txt"

Set fso = CreateObject("Scripting.FileSystemObject")

Set txt = fso.CreateTextFile(fName)

txt.Writeline Data

txt.Close

End Function

This is run from the powershell script; which I have listed below. Then the powershell script finds the reset of the keys I want to find such as the OEM Key and the registry BackupProductKeyDefault then appends it to the text file.

Get-ProductKey.ps1

start-process -FilePath "CDKey.vbs" -Wait -Passthru 

$SLS = wmic path SoftwareLicensingService get OA3xOriginalProductKey

$RLS = Get-ItemPropertyValue 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\SoftwareProtectionPlatform' -Name BackupProductKeyDefault

$SLSKey = $SLS.Split(" ",[System.StringSplitOptions]::RemoveEmptyEntries)

$DPID = Get-ItemPropertyValue 'HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\DefaultProductKey2' -Name DigitalProductId

$EditionID = Get-ItemPropertyValue 'HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\DefaultProductKey2' -Name EditionId

function ConvertTo-ProductKey {

param (

[parameter(Mandatory = $True, Position = 0)]

$Registry,

[parameter()]

[Switch]$x64

)

begin {

$map = "BCDFGHJKMPQRTVWXY2346789"

}

process {

$ProductKey = ""

$prodkey = $Registry[0x34 .. 0x42]

for ($i = 24; $i -ge 0; $i--) {

$r = 0

for ($j = 14; $j -ge 0; $j--) {

$r = ($r * 256) -bxor $prodkey[$j]

$prodkey[$j] = [math]::Floor([double]($r/24))

$r = $r % 24

}

$ProductKey = $map[$r] + $ProductKey

if (($i % 5) -eq 0 -and $i -ne 0) {

$ProductKey = "-" + $ProductKey

}

}

$ProductKey

}

}

$x = Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion' -name DigitalProductId

$key = ConvertTo-ProductKey $x.DigitalProductId

$prodKey = "Windows Backup Product Key Default: $RLS `nOEM Software Licensing Service Key: $SLSKey `nDigital Product ID Windows Key: $key"

Add-Content .\ProductKey.txt $prodKey

The powershell script is run from a bat file which sets the Powershell script bypass for running the powershell script.

RunProdKey.bat

@ECHO OFF

Powershell.exe -executionpolicy remotesigned -File  ./Get-ProductKey.ps1

 

The resulting file output looks like this

It has worked well on the half dozen machines I tried it on and I will update this post once I get the Microsoft 365 and Google Workspaces integration working.  I have the 3 files running from a directory you can place anywhere on the machine you are wanting to inventory.  In this case I have it in a folder called "Find Product Key" which is on my desktop.  If you download the zipped files listed below all you have to do is run the "RunProdKey.bat" file and it will put the generated file in the same folder and it is called ProductKey.txt

Files for this post can be downloaded from here https://drive.google.com/file/d/1Wg327EYS8wyev1ZohVNrcU1o6mxkQY8N/view?usp=sharing