SQL Server 2022 in containers

[michvllni]

Hi,

are there plans to use SQL Server 2022 inside the containers?

Currently it is SQL 2019 and we are unable to bring databases that were on our SQL 2022 servers into the containers

[freddydk]

I have been working on this, but currently I cannot install the latest SQL 2022 hotfix in a container - it fails the installation. I am in contact with the SQL team to see whether we can fix this.

[michvllni]

I am creating sql server developer images for us as they are not provided by microsoft any more.

Maybe my dockerfile can help you, I'd expect express to install in a similar way as developer:

FROM mcr.microsoft.com/windows/servercore:ltsc2022

ENV sa_password="_" \
attach_dbs="[]" \
ACCEPT_EULA="_" \
bakPath="_" \
sa_password_path="C:\\ProgramData\\Docker\\secrets\\sa-password"

WORKDIR C:\\installer

RUN curl -o .\installer.exe https://go.microsoft.com/fwlink/p/?linkid=2215158 -L
        RUN .\installer.exe /MEDIATYPE=CAB /Action=Download /Q /Language=en-US /HIDEPROGRESSBAR /MEDIAPATH="C:\installer"
        RUN move .\SQLServer2022-DEV-x64-ENU.box .\SQL.box
        RUN move .\SQLServer2022-DEV-x64-ENU.exe .\SQL.exe

RUN .\SQL.exe /qs /x:setup
RUN .\setup\setup.exe /q /ACTION=Install /INSTANCENAME=MSSQLSERVER /FEATURES=SQLEngine /UPDATEENABLED=0 /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /TCPENABLED=1 /NPENABLED=0 /IACCEPTSQLSERVERLICENSETERMS

WORKDIR /
SHELL ["powershell", "-noprofile", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue'; "]
RUN Remove-Item -Recurse installer -ErrorAction SilentlyContinue

RUN stop-service MSSQLSERVER
RUN set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql16.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpdynamicports -value ''
RUN set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql16.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -name tcpport -value 1433
RUN set-itemproperty -path 'HKLM:\software\microsoft\microsoft sql server\mssql16.MSSQLSERVER\mssqlserver\' -name LoginMode -value 2

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]
# make install files accessible
COPY start.ps1 /
COPY entry.ps1 /
CMD .\start.ps1 -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -bakPath \"$env:bakPath\" -sqlVersion "16" -Verbose
ENTRYPOINT ["powershell.exe","C:\\entry.ps1"]

entry.ps1:

$hostsFile = "C:\Windows\System32\drivers\etc\hosts"
try {
    $DnsEntries = @("host.docker.internal", "gateway.docker.internal")
    # Tries resolving names for Docker
    foreach ($Entry in $DnsEntries) {
        # If any of the names are not resolved, throws an exception
        Resolve-DnsName -Name $Entry -ErrorAction Stop | Out-Null
        if (!(Test-NetConnection -ComputerName $Entry -InformationLevel Quiet -WarningAction SilentlyContinue -ErrorAction Stop)) {
            throw "Ping to $Entry failed."
        }
    }
    # If it passes, means that DNS is already configured
}
catch {
    Write-Host "Configuring host.docker.internal"
    # Gets the gateway IP address, that is the Host's IP address in the Docker network
    $ip = (ipconfig | where-object { $_ -match "Default Gateway" } | foreach-object { $_.Split(":")[1] }).Trim()
    # Read the current content from Hosts file
    $src = [System.IO.File]::ReadAllLines($hostsFile)
    # Add the a new line after the content
    $lines = $src += ""

    # Check the hosts file and write it in if its not there...
    if ((cat $hostsFile | Select-String -Pattern "host.docker.internal") -And (cat $hostsFile | Select-String -Pattern "gateway.docker.internal")) {
        For ($i = 0; $i -le $lines.length; $i++) {
            if ($lines[$i].Contains("host.docker.internal")) {
                $lines[$i] = ("{0} host.docker.internal" -f $ip)
                $lines[$i + 1] = ("{0} gateway.docker.internal" -f $ip)
                break
            }
        }
    }
    else {
        $lines = $lines += "# Added by Docker for Windows"
        $lines = $lines += ("{0} host.docker.internal" -f $ip)
        $lines = $lines += ("{0} gateway.docker.internal" -f $ip)
        $lines = $lines += "# End of section"
    }
    # Writes the new content to the Hosts file
    [System.IO.File]::WriteAllLines($hostsFile, [string[]]$lines) 
}

start.ps1:

# The script sets the sa password and start the SQL Service
# Also it attaches additional database from the disk
# The format for attach_dbs

param(
    [Parameter(Mandatory = $false)]
    [string]$sa_password,

    [Parameter(Mandatory = $false)]
    [string]$ACCEPT_EULA,

    [Parameter(Mandatory = $false)]
    [string]$attach_dbs,
    [Parameter(Mandatory = $false)]
    [string]$bakPath,
    [Parameter(Mandatory = $false)]
    [string]$sqlVersion
)

if ($ACCEPT_EULA -ne "Y" -And $ACCEPT_EULA -ne "y") {
    Write-Host "ERROR: You must accept the End User License Agreement before this container can start."
    Write-Host "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

    exit 1
}

# start the service
Write-Host "Starting SQL Server"
start-service MSSQLSERVER

if ([String]::IsNullOrEmpty((sqlcmd -Q "SET NOCOUNT ON;SELECT 1 FROM sys.databases WHERE name not in ('master','tempdb','model','msdb');SET NOCOUNT OFF" -h -1))) {
    Write-Host "Initializing"
    if ($sa_password -eq "_") {
        if (Test-Path $env:sa_password_path) {
            $sa_password = Get-Content -Raw $secretPath
        }
        else {
            Write-Host "WARN: Using default SA password, secret file not found at: $secretPath"
        }
    }

    if ($sa_password -ne "_") {
        Write-Host "Changing SA login credentials"
        $sqlcmd = "ALTER LOGIN sa with password=" + "'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
        & sqlcmd -Q $sqlcmd
    }

    $attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

    $dbs = $attach_dbs_cleaned | ConvertFrom-Json

    if ($null -ne $dbs -And $dbs.Length -gt 0) {
        Write-Host "Attaching $($dbs.Length) database(s)"

        Foreach ($db in $dbs) {            
            $files = @();
            Foreach ($file in $db.dbFiles) {
                $files += "(FILENAME = N'$($file)')";           
            }

            $files = $files -join ","
            $sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"

            Write-Host "Invoke-Sqlcmd -Query $($sqlcmd)"
            & sqlcmd -Q $sqlcmd
        }
    }

    $bakPathCleaned = $bakPath.TrimStart('\\').TrimEnd('\\')
    if ($bakPathCleaned -ne "_") {
        if (Test-Path "$bakPathCleaned") {
            Write-Host "Restoring $bakPathCleaned"
            $sqlcmd = "RESTORE FILELISTONLY FROM DISK = '$bakPathCleaned'" 
            $files = sqlcmd -Q $sqlcmd -s "," -W
            $files = $files[2..($files.length - 3)] #remove header and footer
            $importcmd = "RESTORE DATABASE mydatabase FROM DISK = '$bakPathCleaned'"
            if ($files.Count -gt 0) {
                $importcmd += "WITH "
                foreach ($file in $files) {
                    $fileRow = $file -split ","
                    $logicalName = $fileRow[0]
                    $physicalName = Join-Path "C:\Program Files\Microsoft SQL Server\MSSQL$sqlVersion.MSSQLSERVER\MSSQL\DATA" (Split-Path $fileRow[1] -Leaf)
                    if ($importcmd -like "*MOVE*TO*") {
                        $importcmd += ","
                    }
                    $importcmd += "MOVE '$logicalName' TO '$physicalName'"
                }
            }
            sqlcmd -Q "$importcmd"
            if (!$?) {
                exit 1
            }
        }
        else {
            Write-Host "Could not find $bakPathCleaned"
            Exit 1
        }
    }
}

Write-Host "Started SQL Server."

Write-Host "Ready for connections!"
$lastCheck = (Get-Date).AddSeconds(-2) 
while ($true) { 
    Get-EventLog -LogName Application -Source "MSSQL*" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message   
    $lastCheck = Get-Date 
    Start-Sleep -Seconds 2 
}

[freddydk]

@michvllni thanks. Have you been able to add the latest SQL Server 2022 CU to the image in the DOCKERFILE as well? This is where I have an issue - the installation fails causing my images to run SQL 2022 RTM, which is flagged vulnerable and my images will be removed from Microsoft Security.

[michvllni]

@freddydk haven't tried yet - I'll give it a shot once I'm in the office

[FelixHaemsch]

@freddydk Have you achieved any progress on this topic? Many onPrem customers have upgraded to SQL 2022 and we can no longer import these databases in our development containers.

[ashishdawale20]

I am getting bellow error for alter login db script, any idea about this error?

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. . Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..