SQL Server 2014 Connectivity in Azure Container Instances (ACI)

What Happened?

Issue:

When running in an Azure Container Instance (ACI) with a modern Linux base image, the Loome Agent fails to establish a connection to a SQL Server 2014 instance.

Impact:

Data migration tasks that use ‘SQL Server 2014’ as a source or target connection cannot start. This prevents the synchronization of legacy data into modern platforms like Snowflake or Azure SQL, stalling data integration workflows.

Error:

The following errors occur:

  • SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
  • SSL Library Error: error:0A000126:SSL routines::unexpected eof while reading
  • Connection timeouts or “Error 31” during the validation phase in the Loome Portal.

Root Cause:

The problem is caused by ‘OpenSSL 3.0’ (the security library in the container), which is significantly stricter than previous versions.

It blocks SQL Server 2014 for two reasons:

  1. Security Level (SECLEVEL): OpenSSL 3.0 defaults to SECLEVEL=2, which rejects older encryption keys and SHA-1 certificates commonly used by SQL 2014.
  2. Handshake Termination: SQL 2014 often terminates TLS sessions abruptly. OpenSSL 3.0 interprets this as a “truncation attack” (Unexpected EOF) and kills the connection instead of ignoring it.

How can you fix it?

Resolution

The fix involves “injecting” a legacy configuration into the container at runtime using an Azure File Share and an environment variable override.

Step 1. Configuration File Creation

Create a file named openssl_legacy.cnf with these specific compatibility overrides:

openssl_conf = default_conf
[default_conf]
ssl_conf = ssl_sect
[ssl_sect]
system_default = system_default_sect
[system_default_sect]
CipherString = DEFAULT@SECLEVEL=1
Options = UnsafeLegacyRenegotiation
Step 2. Infrastructure Setup:
  • A. Storage: Use a General Purpose v2 (GPv2) Storage Account.
  • B. File Share: Create a share (e.g., agent-configs) and upload the .cnf file.
  • C. Permissions: Ensure the Storage Account Key is available for the ACI deployment.
Step 3. ACI Deployment (YAML Configuration):

The deployment must include the OPENSSL_CONF environment variable and the volume mount.

Generate the values from the Agent setup page and then supply the needed values on your YAML file.

Required YAML Snippet:

agentsetuppage

apiVersion: '2021-10-01'
location: <Location>
name: <agent name>
identity:
  type: SystemAssigned
properties:
  containers:
  - name: <agent name>
    properties:
      image: quay.io/loomesoftware/agent:latest
      environmentVariables:
      # This tells OpenSSL to use your custom file instead of the system default
      - name: OPENSSL_CONF
        value: /mnt/config/openssl_legacy.cnf
      - name: LOOME_AGENT_SETTING_TENANTS__0__HOSTURL
        value: https://dg-api-au.perspectiveilm.com
      - name: LOOME_AGENT_SETTING_TENANTS__0__HOSTNAME
        value: <agent name>
      - name: LOOME_AGENT_SETTING_TENANTS__0__PRODUCT
        value: Integrate
      - name: LOOME_AGENT_SETTING_TENANTS__0__IDA__CLIENTID
        value: <clientId>
      - name: LOOME_AGENT_SETTING_TENANTS__0__IDA__SECRET
        value: <secret>
      - name: LOOME_AGENT_SETTING_TENANTS__0__IDA__AUTHORITY
        value: https://identity-au.perspectiveilm.com/
      - name: LOOME_AGENT_SETTING_MICROSERVICES__LOGGINGENDPOINT
        value: https://logging-au.loomesoftware.com/

      resources:
        requests:
          cpu: 2.0
          memoryInGB: 2.0
      volumeMounts:
      - name: config-volume
        mountPath: /mnt/config/
        readOnly: true
  volumes:
  - name: config-volume
    azureFile:
      shareName: <Fileshare name>
      storageAccountName: <YOUR_STORAGE_ACCOUNT_NAME>
      storageAccountKey: <YOUR_STORAGE_ACCOUNT_KEY>
  osType: Linux
  restartPolicy: Always
Step 4. Deploy the Container

Open your Command Prompt, navigate to the folder where you saved agent.yaml, and run:

az container create -g <resourcegroup> --file agent.yaml

Ensure your SQL connection string in Loome Portal also includes TrustServerCertificate=true; to complement this fix.