title: Connect to external services inside Visual Studio Online build/test task
body: <p>We are running our build and test process on TFS Online. This works fine, until we try to connect to an external service. In our case a SQL Database.<br>
  <em>The discussion about what we should mock and not is not really helpful in this case, as currently we need to do this.</em></p>
  
  <p>We also tried just a simple ping, but not even that is getting out:</p>
  
  <pre><code>Test-Connection "172.217.18.100" #resolves to www.google.com
  
  Testing connection to computer '172.217.18.100' failed: Error due to lack of resources
  </code></pre>
  
  <p>So we have the impression that most outside IP's/Ports/etc. could be locked?
  Is there a way to open this up? If yes, how?</p>
  
  <p>I can't imagine that we are the first ones to try something like that? Downloading something from a website, making a REST all, etc? Should be possible somehow, no?</p>
  
  <p><strong>Update 1:</strong><br>
  We had a bit a more detailed question about this issue <a href="https://stackoverflow.com/questions/41165554/unable-to-add-azure-db-firewall-rule-to-allow-build-server-to-run-tests">here</a>, but figured it was a more generic problem. </p>
  
  <p>The error message when we connect to Azure SQL is </p>
  
  <pre><code>System.Data.SqlClient.SqlException: 
  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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)`
  </code></pre>
  
  <p>But the Named Pipes Provider part is missleading. You also get this message locally if you try to connect to an IP that does not exist.</p>
  
  <p>We access Azure SQL from our C# NUnit tests.</p>
  
  <p><strong>Update 2:</strong><br>
  We tried out the idea from @starain-MSFT and installed the Azure SQL Execute Query step/task. Installation works fine, but it seems to miss a component. See picture below. </p>
  
  <blockquote>
    <p>No agent could be found with the following capabilities: azureps,
    sqlps, npm, node.js, msbuild, visualstudio, vstest</p>
  </blockquote>
  
  <p>Based on <a href="http://listofsoftwareontfshostedbuildserver.azurewebsites.net/" rel="nofollow noreferrer">installed apps</a> list I assume it's azureps.</p>
  
  <p><a href="https://i.stack.imgur.com/7Waaf.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7Waaf.jpg" alt="enter image description here"></a></p>
  
  <p><strong>Solution (partly):</strong><br>
  Ok, so we went down the wrong route all the way. The problem is not the firewall (or any firewalls for that matter). The problem was that our app.config file didn't had the right settings in it.<br>
  We had the same setup for our App.config files inside the unit tests as for our Web.config files. Each had a App.Debug.config and a App.Release.config file attached. While this worked fine for the web applications, this obviously didn't work for our unit tests.<br>
  We are still looking for a good solution here. 
  I found <a href="https://stackoverflow.com/questions/3004210/app-config-transformation-for-projects-which-are-not-web-projects-in-visual-stud">this solution on how to add a transform task inside Visual Studio</a>, but this is not exactly what we are searching, because we don't need the transform locally, but only in Visual Studio Teams.</p>
  
  <p><strong>app.config Transform inside Visual Studio Teams</strong><br>
  So, I think we finally got it. With <a href="https://marketplace.visualstudio.com/items?itemName=saeidbabaei.configtransformsaeid" rel="nofollow noreferrer">ConfigTransform</a>, we can now transform our app.config files during the build process. </p>
  
  <p><a href="https://i.stack.imgur.com/ijgwR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ijgwR.png" alt="enter image description here"></a></p>