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

Question

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.

Detestable

Change vmImage: 'ubuntu-16.04' to vmImage: 'ubuntu-latest', and it succeeds to print ID on my side. Maybe the ubuntu-16.04 is a bit old. – Peatroy

After checking, you are correct, this was just one of my debugging steps. I've added more details, still even in ubuntu-latest I'm still getting a sql connection error. – Detestable

Answer 1

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.

Detestable

Use wait-for-it to block execution till the SQL port becomes available. Waiting 10s may fail when it's not long enough due to the shared pipeline resources are overutilised by smth else and spinning off the container takes longer. – Radiograph

I've never had the 10 seconds not be long enough for it to be ready. A wait for would be better, but this has worked perfectly til now. – Detestable

Answer 2

I ran into this issue over the past several days and came upon your post. I was getting the same behavior and then something clicked.

IMPORTANT NOTE: If you are using PowerShell on Windows to run these commands use double quotes instead of single quotes.

that note is from https://hub.docker.com/_/microsoft-mssql-server

I believe that changing your line from

options: -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express'

to

options: -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -e "MSSQL_PID=Express"

should get it to work. I think its also interesting that in the link you posted above the password is passed in as its own line, which probably resolves the issue, not necessarily the 10 second delay. (example below)

- container: mssql
  image: mcr.microsoft.com/mssql/server:2017-latest
  env:
    ACCEPT_EULA: Y
    SA_PASSWORD: Password123
    MSSQL_PID: Express

Boylan

this is irrelevant b/c in all instances of the dev ops pipelines they are using docker vms on linux machines so powershell is not even used. You actually can't run database docker in azure devops windows containers b/c they aren't really supported (really it's windows containers I think instead of docker). – Detestable

ok, well I couldn't get it to work with single quotes and then as soon as I switched to double quotes it started working. Didn't need the 10 second delay either. – Boylan

which environment was this in? github, azure devops, gitlab? – Detestable

Azure Devops Pipeline – Boylan

This is the real answer, thanks @Boylan – Thimblerig

Moving the options to Environment also helped me. I'm sure it has something to do with the "" – First

Answer 3

One thing I found helpful, was to log what was go on inside the container:

        services:
          db:
            image: mcr.microsoft.com/mssql/server:2017-latest
            ports:
              - 1433:1433
            options: --name sqlserver
            env:
              ACCEPT_EULA: Y
              SA_PASSWORD: StrongPassw0rd

        steps:
          - bash: |
              echo "Getting logs from sql server container"
              docker logs sqlserver
            displayName: 'DB startup logs'

It turned out that my container was seg faulting because I was using an incompatible host agent image (ubuntu-22.04). After downgrading to ubuntu-20.04, everything worked fine, and I no longer had to wait for the container to be ready.

Ramsay