title: How do I get MSSQL service container working in Azure DevOps pipeline?
body: <h2>Description</h2>
  <p>I'm trying out the <strong>service containers</strong> for <strong>integrated database tests</strong> in azure devops pipelines.</p>
  <p>As per this opensourced dummy ci cd pipeline project <a href="https://dev.azure.com/funktechno/_git/dotnet%20ci%20pipelines" rel="noreferrer">https://dev.azure.com/funktechno/_git/dotnet%20ci%20pipelines</a>. I was experimenting with azure devops <a href="https://docs.microsoft.com/en-us/azure/devops/pipelines/process/service-containers?view=azure-devops&amp;tabs=yaml" rel="noreferrer">service containers</a> for integrated pipeline testing. I got postgress and mysql to work. I'm having issues with with <strong>microsoft sql server</strong>.</p>
  <p>yml file</p>
  <pre><code>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:&quot;XPlat Code Coverage&quot;'
  
      - task: PublishCodeCoverageResults@1
        inputs:
          codeCoverageTool: 'Cobertura'
          summaryFileLocation: '$(Agent.TempDirectory)\**\coverage.cobertura.xml'
  </code></pre>
  <p>db connection string</p>
  <pre><code>{
      &quot;sqlserver&quot;: {
          &quot;ConnectionStrings&quot;: {
              &quot;Provider&quot;: &quot;sqlserver&quot;,
              &quot;DefaultConnection&quot;: &quot;User ID=sa;Password=yourStrong(!)Password;Server=localhost;Database=mockDb;Pooling=true;&quot;
          }
      }
  }
  </code></pre>
  <h2>Debugging</h2>
  <ol>
  <li><p>Most I can tell is that when I run Bash@3 to check the environment variables postgres and mysql print something similar to</p>
  <pre class="lang-sh prettyprint-override"><code>/bin/bash --noprofile --norc /home/vsts/work/_temp/b9ec7d77-4bc2-47ab-b767-6a5e95ec3ea6.sh
      &quot;id&quot;: &quot;b294d39b9cc1f0d337bdbf92fb2a95f0197e6ef78ce28e9d5ad6521496713708&quot;
    &quot;pg11&quot;: {
    }
  </code></pre>
  <ul>
  <li>while the mssql fails to print an id</li>
  </ul>
  <pre class="lang-sh prettyprint-override"><code>========================== Starting Command Output ===========================
  /bin/bash --noprofile --norc /home/vsts/work/_temp/70ae8517-5199-487f-9067-aee67f8437bb.sh
    }
  }
  </code></pre>
  <ul>
  <li><strong>update</strong> this doesn't happen when using ubuntu-latest, but still have mssql connection issue</li>
  </ul>
  </li>
  <li><p>Database Error logging.</p>
  <ul>
  <li>I'm currently getting this error in the pipeline</li>
  </ul>
  <pre class="lang-sh prettyprint-override"><code>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)
  </code></pre>
  <ul>
  <li>In TestDbContext.cs my error message is this. If people have pointer for getting more details it would be appreciated.</li>
  </ul>
  <pre class="lang-cs prettyprint-override"><code>catch (System.Exception e)
     {
                  var assertMsg = &quot;Failed for &quot; + connectionStrings.Provider + &quot;\n&quot; + &quot; providername:&quot; + dbContext.Database.ProviderName + &quot; m:&quot;;
                  if (e.InnerException != null)
                      assertMsg += e.InnerException.Message;
                  else
                      assertMsg += e.Message;
                  _exceptionMessage = assertMsg;
      }
  </code></pre>
  </li>
  </ol>
  <h2>Example pipeline: <a href="https://dev.azure.com/funktechno/dotnet%20ci%20pipelines/_build/results?buildId=73&amp;view=logs&amp;j=ce03965c-7621-5e79-6882-94ddf3daf982&amp;t=a73693a5-1de9-5e3d-a243-942c60ab4775" rel="noreferrer">https://dev.azure.com/funktechno/dotnet%20ci%20pipelines/_build/results?buildId=73&amp;view=logs&amp;j=ce03965c-7621-5e79-6882-94ddf3daf982&amp;t=a73693a5-1de9-5e3d-a243-942c60ab4775</a></h2>
  <h2>Notes</h2>
  <ul>
  <li><p>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.</p>
  </li>
  <li><p>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.</p>
  </li>
  <li><p>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.</p>
  </li>
  </ul>
  <h2>Progress Update</h2>
  <ul>
  <li>got <strong>mssql</strong> working in <strong>github actions</strong> and <strong>gitlab pipelines</strong> so far.</li>
  <li><strong>postgres</strong> and <strong>mysql</strong> work just fine on <strong>azure devops</strong> and <strong>bitbucket pipelines</strong>.</li>
  <li>still no luck on getting <strong>mssql</strong> working on <strong>azure devops</strong>.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.</li>
  </ul>