SQL Server 2014 Connectivity in Azure Container Instances (ACI)

What Happened?

Issue:

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

Impact:

Data migration jobs targeting SQL Server 2014 sources or destinations cannot start. This prevents the synchronization of legacy data into modern platforms like Snowflake or Azure SQL, stalling data integration workflows.

Error:

• 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. 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

  1. 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.

  2. ACI Deployment (YAML Configuration) The deployment must include the OPENSSL_CONF environment variable and the volume mount. Required YAML Snippet:

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

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
  1. Deploy the Container Open your Command Prompt, navigate to the folder where you saved agent.yaml, and run: .ps1 az container create -g <resourcegroup> --file agent.yaml

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