How do I get MSSQL service container working in Azure DevOps pipeline?

Description

I'm trying out the service containers for integrated database tests in azure devops pipelines.

As per this opensourced dummy ci cd pipeline project https://dev.azure.com/funktechno/_git/dotnet%20ci%20pipelines. I was experimenting with azure devops service containers for integrated pipeline testing. I got postgress and mysql to work. I'm having issues with with microsoft sql server.

yml file

resources:
  containers:
  - container: mssql
    image: mcr.microsoft.com/mssql/server:2017-latest
    ports: 
      # - 1433
      - 1433:1433
    options: -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express'

- job: unit_test_db_mssql
  # condition: eq('${{ variables.runDbTests }}', 'true')
  # continueOnError: true
  pool:
    vmImage: 'ubuntu-latest'
  services:
    localhostsqlserver: mssql
  steps:
    - task: UseDotNet@2
      displayName: 'Use .NET Core sdk 2.2'
      inputs:
        packageType: sdk
        version: 2.2.207
        installationPath: $(Agent.ToolsDirectory)/dotnet
    - task: NuGetToolInstaller@1
    - task: NuGetCommand@2
      inputs:
        restoreSolution: '$(solution)'
    - task: Bash@3
      inputs:
        targetType: 'inline'
        script: 'env | sort'

        #  echo Write your commands here...
        #   echo ${{agent.services.localhostsqlserver.ports.1433}}
        #   echo Write your commands here end...
    - task: CmdLine@2
      displayName: 'enabledb'
      inputs:
        script: |
          cp ./MyProject.Repository.Test/Data/appSettings.devops.mssql.json ./MyProject.Repository.Test/Data/AppSettings.json
    - task: DotNetCoreCLI@2
      inputs:
        command: 'test'
        workingDirectory: MyProject.Repository.Test
        arguments: '--collect:"XPlat Code Coverage"'

    - task: PublishCodeCoverageResults@1
      inputs:
        codeCoverageTool: 'Cobertura'
        summaryFileLocation: '$(Agent.TempDirectory)\**\coverage.cobertura.xml'

db connection string

{
    "sqlserver": {
        "ConnectionStrings": {
            "Provider": "sqlserver",
            "DefaultConnection": "User ID=sa;Password=yourStrong(!)Password;Server=localhost;Database=mockDb;Pooling=true;"
        }
    }
}

Debugging

1. Most I can tell is that when I run Bash@3 to check the environment variables postgres and mysql print something similar to

   /bin/bash --noprofile --norc /home/vsts/work/_temp/b9ec7d77-4bc2-47ab-b767-6a5e95ec3ea6.sh
       "id": "b294d39b9cc1f0d337bdbf92fb2a95f0197e6ef78ce28e9d5ad6521496713708"
     "pg11": {
     }
   

   • while the mssql fails to print an id

   ========================== Starting Command Output ===========================
   /bin/bash --noprofile --norc /home/vsts/work/_temp/70ae8517-5199-487f-9067-aee67f8437bb.sh
     }
   }
   

   • update this doesn't happen when using ubuntu-latest, but still have mssql connection issue

2. Database Error logging.

   • I'm currently getting this error in the pipeline

   Error Message:
     Failed for sqlserver
       providername:Microsoft.EntityFrameworkCore.SqlServer m:A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)
   

   • In TestDbContext.cs my error message is this. If people have pointer for getting more details it would be appreciated.

   catch (System.Exception e)
      {
                   var assertMsg = "Failed for " + connectionStrings.Provider + "\n" + " providername:" + dbContext.Database.ProviderName + " m:";
                   if (e.InnerException != null)
                       assertMsg += e.InnerException.Message;
                   else
                       assertMsg += e.Message;
                   _exceptionMessage = assertMsg;
       }
   

Example pipeline: https://dev.azure.com/funktechno/dotnet%20ci%20pipelines/_build/results?buildId=73&view=logs&j=ce03965c-7621-5e79-6882-94ddf3daf982&t=a73693a5-1de9-5e3d-a243-942c60ab4775

Notes

• I already know that azure devops pipeline mssql server doesn't work in the windows agents b/c they are windows server 2019 and the windows container version of mssql server is not well supported and only works for windows server 2016. It fails on the initialize container step when I do that.

• I tried several things for the unit_test_db_mssql, changing ubuntu version, changing parameters, changing mssql server version, all give about the same error.

• If people know of command line methods that work in linux to test if the mssql docker instance is ready, this may help as well.

Progress Update

• got mssql working in github actions and gitlab pipelines so far.
• postgres and mysql work just fine on azure devops and bitbucket pipelines.
• still no luck on getting mssql working on azure devops.bitbucket wouldn't work as well although it did give a lot of docker details of the running database, but then again nobody really cares about bitbucket pipelines with their measly 50 minutes. You can see all of these pipelines in the referenced repository, they are all public, pull requests are also possible since it's open-sourced.

Per the help from Starain Chen [MSFT] in https://developercommunity.visualstudio.com/content/problem/1159426/working-examples-using-service-container-of-sql-se.html. It looks like a 10 second delay is needed to wait for the container to be ready.

adding

  - task: PowerShell@2
    displayName: 'delay 10'
    inputs:
      targetType: 'inline'
      script: |
        # Write your PowerShell commands here.
        
        start-sleep -s 10

Gets the db connection to work. I'm assuming that maybe the mssql docker container is ready by then.