3

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.
The discussion about what we should mock and not is not really helpful in this case, as currently we need to do this.

We also tried just a simple ping, but not even that is getting out:

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

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?

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?

Update 1:
We had a bit a more detailed question about this issue here, but figured it was a more generic problem.

The error message when we connect to Azure SQL is 

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 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.

We access Azure SQL from our C# NUnit tests.

Update 2:
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.

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

Based on installed apps list I assume it's azureps.

enter image description here

Solution (partly):
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.
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.
We are still looking for a good solution here. I found this solution on how to add a transform task inside Visual Studio, but this is not exactly what we are searching, because we don't need the transform locally, but only in Visual Studio Teams.

app.config Transform inside Visual Studio Teams
So, I think we finally got it. With ConfigTransform, we can now transform our app.config files during the build process.

enter image description here

Community
  • 1
  • 1
Remy
  • 12,555
  • 14
  • 64
  • 104
  • What's the ConnectionString? Try to connect it via SQL Server Management Studio and check the result. – starian chen-MSFT Jan 16 '17 at 09:00
  • Yes, that works. We can connect to the SQL Server otherwise without any issues. It's just that it seems ALL connections to the outside are blocked from the build process. Even ping as I stated. – Remy Jan 16 '17 at 09:03
  • How do you configure Azure SQL Server can be accessible from any IP address? Regarding Firewall Rule, you can add Azure SQL Execute Query step/task that can add build agent IP address to rule, so you can add this step before do NUnit tests to exec a simple script to add build agent IP to rule (uncheck delete Rule After Task Ends) https://marketplace.visualstudio.com/items?itemName=geeklearningio.gl-vsts-tasks-azure&targetId=694f8310-2406-4022-b85e-97dd9106a4e9 – starian chen-MSFT Jan 16 '17 at 09:39
  • I updated my answer to add firewall rules, please check it. (Azure SQL Execute Query has the issue) – starian chen-MSFT Jan 18 '17 at 03:29
  • I think we might get a bit closer to the issue. Could it be that port 1433 is closed on the build agent? http://stackoverflow.com/questions/11617687/cannot-connect-to-azure-sql-database-even-with-whitelisted-ip – Remy Jan 19 '17 at 17:30
  • Can you logon your build machine and try to connect to your azure DB manually? What's the result? – starian chen-MSFT Jan 20 '17 at 01:55
  • We are using the included build machine from Visual Studio Online. I don't think we can login to one of those. It's not our own. – Remy Jan 20 '17 at 09:22
  • Based on the screenshot, your are using Hosted Build agent, are you still using Hosted Build Agent? It is working for me with Hosted Build agent. What's the detail build log if you try to add firewall rule with my way (PowerShell)? Is the IP added to the firewall rule? – starian chen-MSFT Jan 23 '17 at 03:16
  • I think we figured out where the problem is. Firewalls and all are not the issue. Your app.config file has not been transformed. In our web apps we have a Web.Debug.config and a Web.Release.config file for each web.config file. We did the same thing for our app.config files in the unit tests. But "obviously" this transformation is not happening. A bit embarrassing. – Remy Jan 23 '17 at 18:10

3 Answers3

2

Using Hosted Agents, the SQL Server need to be accessible from internet in order to connect to your SQL Server from Hosted Agents.

The way to deal with this issue:

  1. As Giulio said that set up an on premise build agent, then you just need to make sure the SQL Server instance can be accessible from that build agent (can be intranet).
  2. Apply a SQL Server on internet, such as Azure SQL Server that can be accessible from internet.
  3. Configure your SQL Server and network to let your SQL Server can be accessible from internet.

BTW, regarding your simple ping test, that IP address is used for its web site and the port is 80, you can access other resource with that IP. You can open another port on your server and access resource by IP with port.

Update 1:

Refer to this way to add Azure SQL Server Firewall Rule:

  1. Check Allow Scripts to Access OAuth Token option (Options of build definition)
  2. Add Azure PowerShell build step before test step (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)"

BTW, you can refer to that script to remove the firewall rule after test.

Update 2:

The SQL ConnectionString like this: 

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;
starian chen-MSFT
  • 33,174
  • 2
  • 29
  • 53
  • We opened up our Azure SQL server to ALL IP addresses (just for the test). It still didn't connect. I didn't really understand your answer about our Ping test. I'm sure if we host our buildserver ourself we would get it to work, but I would much rather use the ones provided by Visual Studio Online. – Remy Jan 13 '17 at 17:27
  • @Remy What's the detail error message you get when connect to azure database? Where do you access azure database? – starian chen-MSFT Jan 16 '17 at 04:13
  • Often connection strings specify the SQL Server name, you'll see that SQL failure when the SQL Database is not advertising it's name. Similar to a DNS failure but specific to SQL Servers. – JWP Jan 18 '17 at 23:33
  • Aside from the evidence in the question, do you have any reason to believe that an Azure SQL DB does not advertise/broadcast its name? The IP addresses are not static so the only reliable way of connecting to an Azure SQL DB is by name. Furthermore, all the examples of elastic queries for querying remote DBs use the DB name (https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/). – awj Jan 19 '17 at 13:32
1

Windows Hosted build agents don't block 1433 outbound.

  1. If you want to connect to SQL Azure through hosted build agents ensure that you enabled in your SQL Azure firewall settings "Allow access to Azure services". You don't need to run a script manually.

SQL Azure Firewall settings

  1. Ensure you are using the right connection string during unit testing. E.g. in MSTest you need to add your connection string into App.config of you 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>

That's it. I just did a quick test with EF, SQL Azure & VSTS Hosted Agent and it worked.

Tom
  • 26
  • 2
  • Cheers Tom! Very embarrassing. The problem were indeed not the firewalls. The problem was that our app.config file didn't go through the publish transformation that our web.config files went. The question now is whats the common approach to have a different app.config file on your TFS server than locally? I saw multiple posts on how to include this transformation locally in VS, but we don't actually want to run it for a local debug run. Only on VS Online. – Remy Jan 23 '17 at 18:14
  • Are we saying that a specific firewall rule is not required? Does this mean any VSTS hosts can access Azure SQL servers? Or is it only for Azure instances that have been associated to that VSTS instance? – Kevin Jun 17 '17 at 02:54
0

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

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

Update: Hosted Agents allows HTTP(S) calls which cover a lot of grounds. While useful I do not think it solves the original question to connect to a SQL database.

Giulio Vian
  • 8,248
  • 2
  • 33
  • 41
  • Right, that would be an option. But would much rather not do this if there is a way around around the lock-down. – Remy Jan 12 '17 at 13:48
  • We have the assumption now that port 1433 is closed on the build agent. But the ps script does not let us change anything. – Remy Jan 19 '17 at 17:31