Connect to external services inside the build / test task of Visual Studio Online

Question

We are starting the build and testing process at TFS Online. This works great until we try to connect to an external service. In our case, the SQL database.
The discussion that we should mock this is not very useful in this case, because we must do it now.

We also tried just plain ping, but even this does not work:

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 

Thus, it seems that most of the external IP addresses / ports / etc. can be blocked? Is there any way to open this? If so, how?

I can’t imagine that we are the first to try to do something like this? Downloading something from a website, creating REST, etc.? It must be possible somehow, no?

Update 1:
We had a slightly more detailed question about this issue here , but he decided that it was a more general problem.

Error message when connecting to Azure SQL

 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)` 

But part of the Named Pipes provider has gaps. You also receive this message locally if you try to connect to an IP address that does not exist.

We access Azure SQL from our C # NUnit tests.

Update 2:
We tested the idea with @ starain-MSFT and installed the Azure SQL Execute Query step / task. The installation works fine, but the component seems to be missing. See the figure below.

No agent can be found with the following features: azureps, sqlps, npm, node.js, msbuild, visualstudio, vstest

Based on the installed applications list, I assume these are azureps.

Solution (partially):
Good, so we went down the wrong route. The problem is not the firewall (or any firewalls). The problem is that our app.config file did not have the correct settings. We had the same setup for our App.config files inside unit tests as for our Web.config files. Each of them had App.Debug.config and an attached file App.Release.config. Although this worked fine for web applications, it clearly did not work for our unit tests.
We are still looking for a good solution here. I found this solution on how to add the conversion task inside Visual Studio , but this is not quite what we are looking for, because we do not need to convert locally, but only in Visual Studio groups.

app.config Convert Inside Visual Studio Commands
So, I think we finally got it. With ConfigTransform, we can now convert our app.config files during the build process.

Answer 1

Windows Hosted build statements do not block outbound outbound 1433.

• If you want to connect to SQL Azure through hosted build agents, make sure that you enable the "Allow access to Azure services" settings in the Azure SQL Firewall. You do not need to manually run the script.

Azure SQL Firewall Settings

2. Make sure you use the correct connection string during unit testing. For instance. in MSTest you need to add a connection string in the App.config of the UnitTest project .

 <connectionStrings> <add name ="TestContext" providerName="System.Data.SqlClient" connectionString="Server=tcp:[ServerName].database.windows.net,1433;Initial Catalog=[DB Name];Persist Security Info=False;User ID=[User];Password=[Password];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"/> </connectionStrings> 

What is it. I just did a quick test with EF, SQL Azure, and the VSTS Hosted Agent, and it worked.

Answer 2

Using Hosted Agents, SQL Server must be accessible from the Internet to connect to your SQL Server from Hosted Agents.

The way to solve this problem:

• Since Giulio said that he set up the room composition agent, you just need to make sure that the instance of SQL Server can be accessed from this assembly agent (there may be an intranet).
• Use SQL Server on the Internet, such as Azure SQL Server, which can be accessed from the Internet.
• Set up your SQL Server and network so that your SQL Server can be accessed from the Internet.

By the way, regarding your simple ping test, this IP address is used for its website, and port is 80, you can access another resource with this IP address. You can open another port on your server and access the resource by IP with the port.

Update 1:

Refer to this method to add the Azure SQL Server firewall rule:

• Select the Allow scripts to access OAuth Token options (Assembly definition options) check box.
• Add the Azure PowerShell step to the test phase (Arguments: -RestAddress https: // [your vsts account] .vsdtl.visualstudio.com / DefaultCollection / _apis / vslabs / ipaddress -Token $ (System.AccessToken) -RG [resource group] - Server [server name (without .database.windows.net)]

Script:

 param ( [string]$RestAddress, [string]$Token, [string]$RG, [string]$Server ) $basicAuth = ("{0}:{1}" -f 'test',$Token) $basicAuth = [System.Text.Encoding]::UTF8.GetBytes($basicAuth) $basicAuth = [System.Convert]::ToBase64String($basicAuth) $headers = @{Authorization=("Basic {0}" -f $basicAuth)} $result = Invoke-RestMethod -Uri $RestAddress -headers $headers -Method Get Write-Host $result.value New-AzureRmSqlServerFirewallRule -ResourceGroupName $RG -ServerName $Server -FirewallRuleName "UnitTestRule" -StartIpAddress "$($result.value)" -EndIpAddress "$($result.value)" 

By the way, you can refer to this script to remove the firewall rule after the test.

Update 2:

SQL ConnectionString:

 Server=tcp:[server name].database.windows.net,1433;Initial Catalog=sqlstarain1;Persist Security Info=False;User ID=[user name];Password=[password];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30; 

Answer 3

I assume that you are using Hosted Agents, which means that the machine is a shared resource between many VSTS accounts (tenants) and is managed (and locked) by Microsoft.

You can easily install the agent on your virtual machine and run the build there. The VM can be in the cloud or indoors, of your choice. You trade simplicity and cheapness for complete control.

Update: Hosting agents allow the use of HTTP (S) calls that cover many bases. Although useful, I don't think it solves the original question for connecting to an SQL database.