VSTS/TFS Agents behind a proxy

Mario Majcica April 9, 2017 0 Comments

Since I wrote this post things regarding the agents and proxies changed drastically, for good. Now setting up an agent behind the proxy is a documented procedure and can be done directly from the initial configuration of the agent. You can read more about the procedure itself here Run a self-hosted agent behind a web proxy.

It often happens that I do write about my love affair with a web proxy systems. In the past years I shared many of these challenges through my blog. Following is another one that I came lately across and that is precisely, allowing the build agent tasks to gain the access to internet via a proxy.
Till now I have never noticed any necessity for setting explicitly the access via a proxy server on any of our build agents. We were using Azure App Service Deployment and other build/release tasks that did accessed an external web address and everything always worked without the need for any particular intervention. However, after the TFS 2017 Update 1, we noticed that our Azure App Service Deployment task started failing with the following error:

Error: connect ETIMEDOUT 104.45.0.17:443

Clearly the call towards the Azure services was failing. Couldn’t be anything else then our lovely proxy. Strange thing was that it was all working till before the upgrade. At this point I was curious why this was happening. Here is what I found.

With the TFS 2017 Update 1, a new version of the Azure App Service Deployment build/release task has shipped. This new task implementation is now based on Node.js handler and no Node.js applications were able to access the web. So obviously I went to setup the necessary to enable Node.js applications to get the access to the web. This was however not the right solution, also because build agent doesn’t use the Node.js engine installed on that machine, but executes everything via a version of Node.js that is shipped together with the agent itself. In order to get a bit more insight in this, I had to peak in the couple of MS Github repositories and luckily I found a reference in which I see that the Node.js HttpClient wrapper sets the proxy for all calls based on the environment variables called HTTP_PROXY and HTTPS_PROXY. Now, I tried adding those two variables and voila my build task started working again (after the build agent service restart).

So, in order to have your Node.js based tasks that do need to access Internet from a build agent behind a proxy, add two new environment variables called HTTP_PROXY and HTTPS_PROXY and set the value of those variables pointing to the proxy itself, like http://my.proxy.local:8080.

Do not forget to restart your agent and give it a go.
I hope this solves it.

Happy releasing!

TFS Tips from the Trenches

Mario Majcica March 20, 2017 2 Comments

In the recent past I came across several interesting techniques to solve a non every day challenges with TFS. Not only that I would like to share those with you, also I would like to leave a trace about this as a note to my future self. I will list several short tips without a particular order.

Let’s start.

Proxy squared

I already wrote about allowing TFS to get the internet access via a proxy server in one of the past articles, TFS 2015 behind a proxy. It is not a very common situation to have a TFS server behind a proxy still in many enterprises it may be the case. My previous post shows how to let the TFS web application to access the web through proxy for TFS 2015 and it is also valid for the TFS 2017. However I missed to mention that there is another component on the application tier that also needs to be set and that is TFS Job Agent. You may ask yourself, why would TFS Job Agent have a need to access the internet? Well if you are trying to set up a web hook in your service hooks and your system needs to communicate with a machine that is out of your network, then the TFS Job Agent needs to be able to do so as it is him that actually sends the request generated by the chosen event. Luckily things are quite simple, move to the C:\Program Files\Microsoft Team Foundation Server 15.0\Application Tier\TFSJobAgent folder and open the TfsJobAgent.exe.config file. The following section needs to be added pointing to your proxy server

</configuration>
  ...   
  <system.net>
    <defaultProxy>
      <proxy usesystemdefault="True" proxyaddress="http://your.proxy.server.com:8080" bypassonlocal="True" />
    </defaultProxy>
  </system.net>
</configuration>

Once you saved these changes, you need to restart the TFS Job Agent. That can be easily done by executing the following from the command prompt with full administrator permissions:

net stop tfsjobagent

followed by

net start tfsjobagent

Now your web hook requests to an external party should succeed.

Browsing TFS from the AppTier machine fails in: ‘Unauthorized: Logon Failed’ error

In case you are not using the machine name to access your TFS server (by using an CNAME in DNS or accessing it via an A-Record that may lets say point to the NLB Virtual IP) you may discover a strange behavior of you Application tier server once it tries to access the service on the localhost. It is not an issue strictly related to the TFS and it has to do with the loopback check security feature that is designed to help prevent reflection attacks on your computer. You can read more about it at KB896861. This also can present an issue in case of TFS 2015 or earlier once you try to setup the Notification URL. A solution to this issue is quite simple and adding a value in the registry will solve it. The following PowerShell command will do the trick:

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa\MSV1_0 -Name “BackConnectionHostNames” -Value “your.tfs.com”,”tfs.yourcompany.com” -PropertyType multistring

You need to set the Value to the URL you have chosen for your DNS entries. A server may require a restart in order to make the changes effective.

TFS DB’s under a SQL AlwaysOn replica

Obviously this is not a guide on how to setup an AlwaysOn replica on SQL Server and move your databases under the replication. That is a topic for a much longer post. What I would like to show you here, is what is necessary purely on TFS side in order to get your Application Tier to connect via the SQL Availability Group Listener to the cluster.

Following commands will make that happen:

TFSConfig RegisterDB /SQLInstance:TFS_LISTENER,10010 /databaseName:Tfs_Configuration

and

TFSConfig RemapDBs /DatabaseName:TFS_LISTENER,10010;Tfs_Configuration /SQLInstances:TFS_LISTENER,10010 /AnalysisInstance:TFSAS /AnalysisDatabaseName:Tfs_Analysis

TFSConfig command must be run from an elevated Command Prompt, even if the running user has administrative credentials. To open an elevated Command Prompt, click Start, right-click Command Prompt, and then click Run as Administrator.
TFSConfig tool is installed in the Tools directory – by default, this will be

• TFS 2017: %programfiles%\Microsoft Team Foundation Server 15.0\Tools
• TFS 2015: %programfiles%\Microsoft Team Foundation Server 14.0\Tools
• TFS 2013: %programfiles%\Microsoft Team Foundation Server 12.0\Tools
• TFS 2012: %programfiles%\Microsoft Team Foundation Server 11.0\Tools
• TFS 2010: %programfiles%\Microsoft Team Foundation Server 2010\Tools

With the first command we will update the name of the server that hosts the TFS configuration database. SQL Instance parameter is pointing to the Availability Group Listener and not the actual instance of the server. 10010 is just the port on which the listener will replay (a non standard port in my case). In case you are on a version of TFS prior to 2017 you will need to include the /usesqlalwayson parameter, which on the TFS2017 is not anymore necessary.
For the second command we will redirect team project collection databases to be accessed via the SQL Availability Group Listener. Again if you are not running the TFS 2017 you will need to specify the /usesqlalwayson parameter at the end.
Make sure that you also specify correctly your analysis server as it is not accessible thought the SQL Availability Group Listener.
Once done do some failover tests and verify the correct functioning of your TFS instance.

More information about the TFSConfig tool can be found at Manage TFS server configuration with TFSConfig page.

Restore permissions for project administrators on Service Hooks

In case you upgraded your TFS instance from TFS 2013 or any previous version to TFS 2015/2017 it may happen in certain cases that the Project Administrators, a role which should have rights in creating and editing Service Hooks, will not be in place. You can add these permissions manually or you can use a tool provided by Microsoft to check the current situation and correct it is necessary. I made some changes to this tool giving you the opportunity to do that for all of the projects and collections on your instance. A fork of the tool can be found on GitHub at https://github.com/mmajcica/vsts-integration-samples.
Once you have downloaded and compiled the code, just run the command line tool with /Server parameter and specify the full path towards your service, like http://mytfs:8080/tfs. This will be sufficient for it to check and, if necessary, correct the missing rights. Same can be done for only a specific collection by using /collection parameter and passing in the path towards the desired collection, like http://mytfs:8080/tfs/DefaultCollection

Controlling and debugging TFS Jobs from DB

Often when you are checking your jobs and you realize that something went wrong, you need to analyze your issues in detail and retry failing jobs. This is usually done via a web services, however, it can also be done directly by querying the DB. I find the second approach often quicker and easier. Let me show you a couple of tricks and where is the necessary data located.

First thing first. Before we are able to do anything further we need to find the Id of the Job that we are looking to operate with. Let’s assume that I’m looking for ‘Reporting Service Path Rename’ Job, which in my case is failing.

Jobs can be defined in the Collection database as they can be defined in the Tfs_Configuration database. This specific one is on the collection level, so I will execute the following query on the collection DB.

SELECT JobId FROM tbl_JobDefinition WHERE JobName like '%report%'

At this point you should get back the JobId which we will use later for obtaining the execution history and to put a new job in the queue.
In my case the above query returned the following GUID: 6322B69A-04BD-47DF-9390-C3185ED59287

Now, on the Tfs_Configuration database you can now check the state of the above job with the following query:

SELECT * FROM tbl_JobHistory WHERE JobId = '6322B69A-04BD-47DF-9390-C3185ED59287' AND NOT Result = 0 ORDER BY StartTime DESC

This will bring us all of the failed runs for the given job in the chronological order. You can get valuable information from the result of this query. In particular I need to get the information about the JobSource which indicates the collection for which this job is failing
In order to get the collection ID <=> Collection Name mappings, you can check the follwoing table:

SELECT * FROM tbl_ServiceHost ORDER BY Name

Let’s get to the point, lets trigger again the job from my example that was failing. This is the query that will create a new run for the given job:

DECLARE @jobSource UNIQUEIDENTIFIER
DECLARE @jobs typ_GuidInt32Table
SELECT  @jobSource = 'C64929FF-9329-4123-BF82-F021DDCBE0C3'
INSERT INTO @jobs VALUES('6322B69A-04BD-47DF-9390-C3185ED59287', '1')

EXEC prc_QueueJobs @jobSource, @jobs, 15, 0

As you can see, we used the job id that we retrieved earlier and the collection for which this is going to be triggered (as it is a collection specific job). Last thing left for us to do is to verify the state of the run and we can do that by checking on the tbl_JobQueue for all of the running jobs:

SELECT * FROM tbl_JobQueue WHERE JobState = '1'

Now that you know the tables and SP’s in play, you can try it and proceed on your own.

Be very careful with modifying TFS DB! It is for sure a non recommended practice! 🙂

Conclusion

These are only some of many issues solved in the past for which I haven’t found a solution by simply asking Google. I hope these information will let you avoid spending hours in finding a valid solution to your TFS challenges.

SonarQube on Windows and MS SQL

Mario Majcica April 28, 2016 10 Comments

Introduction

In the following post we will see what is necessary to install and configure SonarQube 5.4. We will also see how to setup some basic security concerns by making our SonarQube part of our LDAP infrastructure and map security groups to roles.
I’m sure that there are plenty of guides out there, but what I found most annoying meanwhile reading some of them, is that all of them do give several things for granted. Also the information is segmented and not easy to find. I will try in this post to cover even the basic steps that can save you hours of struggling. I’m going to install SonarQube on Windows platform using MS SQL as my database of choice, you can also try Couchbase. Both of these services in my case are going to reside on the same machine, but nothing limits you to use multiple machines for your setup.

Prerequisites

Java runtime is the main prerequisite. Although it works with Java 7, my advice is to install and use JDK 8. At the moment of writing the latest version for my platform is jdk-8u77-windows-x64.exe.
For what concerns MS SQL versions, 2008, 2012 and 2014 are supported. Also the SQL Express is supported. Your SQL server needs to support case-sensitive (CS) and accent-sensitive (AS) collation.

Installing the database

After you installed your MS SQL version of choice, you need to create a database. Add a new database and name it SonarQube.

Now the important step. In the Options page you need to specify the right collation. It needs to be one of the case-sensitive (CS) and accent-sensitive (AS) collations. In my case I will go for SQL_Latin1_General_CP1_CS_AS.

Once that is set, click OK and create the new database.

After the database is created, we need to make sure that the TCP/IP protocol is enabled for our SQL instance. Open the Sql Server Configuration Manager and, in the console pane, expand SQL Server Network Configuration. Choose the Protocols for your instance. In the details pane, right-click TCP/IP, and then click Enable. Once done, restart the service. A detailed guide is available on Technet at Enable TCP/IP Network Protocol for SQL Server.

Last but not least, make sure that SQL Server Browser service is running. Often it is disabled by default, however for the JDBC driver to work, it needs to be enabled and running. Open the Services management console and find the Service called SQL Server Browser. If disabled, enable it and start the service.

That’s all for now for what database concerns.

Installing SonarQube

Before we start, make sure that the latest JDK is installed, then download the SonarQube installation file from SonarQube website. For this demo I will be using the latest available version of SonarQube at the moment of writing and that is 5.4. After I downloaded sonarqube-5.4.zip I will extract it’s content in a folder of my choice and that is D:\SonarQube.

There is another important file we need to get and set before we can continue configuring SonarQube and that is Microsoft JDBC driver. Go to Download the Microsoft JDBC Driver 6.0 (Preview), 4.2, 4.1, or 4.0 for SQL Server and download sqljdbc_4.2.6420.100_enu.tar.gz file. Once done, open the just downloaded file with compression tool of your choice and extract all of it’s content in a temporary folder. Get into sqljdbc_4.2\enu\auth\x64 folder and copy the only file present in that path, sqljdbc_auth.dll and paste it into your System32 directory, usually C:\Windows\System32.

Now we are ready to start the configuration. Open the main configuration file of SonarQube called sonar.properties. You can find it in the conf folder in your SonarQube installation path. Open it with the editor of your choice and search for the line reporting ‘Microsoft SQLServer 2008/2012/2014 and SQL Azure’. Under that line you should see a the following configuration item that is commented out:

#sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true

We need to uncomment this line by removing the hash sign in front of it and change the connection string to point towards our SQL database instance (the one we create earlier).
Following, an example of the connection string using a name instance of SQL:

sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=SonarQube;instanceName=DEV_01;integratedSecurity=true

If you are using the default instance, you can simply omit the instanceName=DEV_01 from your connection string.

Also you can see I’ve set to use the integrated security. If you want to use SQL Authentication, remove the integratedSecurity=true part and specify the credentials as separate configuration items under your connection string (also create users in SQL accordingly and map the newly create user to dbo schema).

sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=SonarQube
sonar.jdbc.username=sonarqube
sonar.jdbc.password=mypassword

Once the connection string is set, save the configuration file and try starting SonarQube. Open the command prompt and move to ...\bin\windows-x86-64 folder and execute StartSonar.bat

If everything is set right, you should see a message in the console INFO app[o.s.p.m.Monitor] Process[web] is up.

Now you can open the web browser of your choice and head to http://localhost:9000. A welcome page on SonarQube should be shown.

If the page loaded, congratulations, SonarQube is running correctly on your machine.

What is left to do is to create a service that will run SonarQube. Stop the current execution with a CTRL+C and terminate the batch job. In the same bin folder where StartSonar.bat is located, you will find InstallNTService.bat. Execute the just mentioned batch file and you should receive the wrapper | SonarQube installed. message. This means that a new service is created. Check your services management console and you should find a service called SonarQube:

As you can see from the picture, service is created but not started.
By default, the “Local System” account is used to execute the SonarQube service. If this account doesn’t have the required permission to create some directories/files in the SonarQube installation directory (which is the case by default on recent Windows versions), the execution of the SonarQube service will fail. In such case, the SonarQube service must be configured to run in the context of a suitable account.
Right click on the SonarQube service and choose properties then move to Log On tab choose “This account”, and select an account that can read/write the folder in which SonarQube is installed. Hopefully you will have a specific service account created for this purpose.

Now, you can start the service manually or by launching StartNTService.bat.

Services configuration

SonarQube is the only web application running on my server, so I will move it from the port 9000 to the default 80. To do so, edit the sonar.properties configuration file and find the #sonar.web.port=9000 comment line. Uncomment it and change port value to 80, sonar.web.port=80.

After this change you need to restart your SonarQube service and try to reach your localhost in the browser. If all went fine you will not need to specify the port at the end of the address.

SonarQube behind a proxy

I wrote in the past time numerous post about running services and applications behind a proxy. SonarQube will not be an exception to that practice. You may wonder why SonarQube should have access to internet and my answer is, plug-ins. Plug-ins are essential to SonarQube and installing and updating them is easiest done via Update Center, a functionality integrated in the administrative portal. In order for it to work, SonarQube needs to be able to access the internet. In case you are behind a proxy, you need to modify again sonar.properties configuration file.

Search for #sonar.web.javaAdditionalOpts= configuration line and modify it by specifying http, https proxy host and port:

sonar.web.javaAdditionalOpts=-Dhttp.proxyHost=swg.myProxy.com -Dhttp.proxyPort=8080 -Dhttps.proxyHost=swg.myProxy.com -Dhttps.proxyPort=8080

Restart the service and try the Update Center. Open SonarQube web page and log in with the default admin user (password is also admin). Click on the administration menu item and then, in the sub-menu, choose System -> Update Center. Check if the updates are retrieved and try to update one of the plug-ins installed by default, like C#. If all goes well you will see the following screen

Once the plug-in is installed you will see a button in the notification message that offers to restart the server for you. In my case it never worked and after choosing this option my server stopped replaying. In order to get it back online, you need to manually restart the service.

This problem is addressed in SONAR-7422 and it is a recognized bug. It should be fixed in the SonarQube version 5.6.

If you where able to install or update plug-in correctly, then your proxy settings where picked up fine.

Securing SonarQube connection

You can setup SonarQube to run on a https secure connection. It natively supports the SSL certificates however it is not advised to configure it. Using a reverse proxy infrastructure is the recommended way to set up your SonarQube installation on production environments which need to be highly secured. This allows to fully master all the security parameters that you want. I will not dig into details on how to set up IIS to leverage the reverse proxy setup. If interested in this, you can read the following blog post on Jesse Houwing’s blog, Configure SSL for SonarQube on Windows. It will guide you in setting up IIS that will act as a proxy for the secure calls towards your SonarQube server.

Security configuration

My desire is to integrate the authentication of the SonarQube server with my LDAP (Active Directory domain services). In order to do that, we need to install LDAP plug-in. Locate the LDAP plug-in in update center under available plug-ins and install it.

Before you restart your SonarQube service, open the sonar.properties configuration file and add the following section:

#----------------------------------------------------------------------
# LDAP

sonar.security.realm=LDAP
sonar.forceAuthentication=true
sonar.authenticator.downcase=true

This are the only necessary settings if you are part of the Active Directory domain. Restart the SonarQube service and open the portal. If all went well, SSO kicked in, and you should be logged in with your domain account. Now comes the fun part. Log out, then, log in again as administrator and go to Administration -> Security -> Users screen. You should see in the list the domain account you logged in with. Update groups for this account and assign it to sonar-administrators group.

Now close the browser and reopen it. Surprise, surprise, you are logged in again via your user profile but you do not see Administration option in your menu, as you would expect. Once the LDAP is configured, on each login, the membership information will be retrieved and local settings will be overwritten. Thus no group membership we assigned will be persisted. In this case, LDAP/AD becomes the one and only place to manage group membership. In order to do so, we need to create a security group in AD and map it in the SonarQube Security Groups.

Before we create a new group in the SonarQube Security Groups we need to get the groups precise name. Group names are case sensitive and do require the domain to be specified. This is not something we can guess but we can extract it from our log file.

Add your user to the AD security group of choice. Edit sonar.properties configuration file again and set the logging level to a higher setting. In order to do so, find the #sonar.log.level=INFO line, uncomment it and change the level from INFO to DEBUG. You line should now look like sonar.log.level=DEBUG. Restart the service and open the portal.

If you are successfully logged in, open the log file. In the SonarQube directory there is a folder called logs, in my case it is, sonarqube-5.4\logs. Inside you will find a file called sonar.log. Open it with your editor of choice and search for your domain username. Next to your username (probably at the bottom of the log file) you will find a couple of log lines made by web[o.s.p.l.w.WindowsAuthenticationHelper] and in one of those lines you will find written Groups for the user YOURDOMAIN\YOU and a list of security groups you are part of. Find the correct one and copy it, in my case this is sonar@maiolocal. Now log in as admin and open the Groups screen. Create new group by clicking to the Create Group button in top right corner and set the name to your group of choice, in my case sonar@maiolocal.

Once the group is created, move to Global Permissions screen (always in the Security menu), and assign the desired permissions to just created group. Let’s suppose that this group will list all of the administrators, under Administer System permission, click on groups and select the newly created group.

Now if you close your browser and reopen it pointing to your SonarQube portal, you will get logged in via SSO and you should be able to see the Administration button in the menu. Same can be done for the users.

Conclusion

This is roughly it. There are some details you would probably like to set as SMTP/Email settings and Source Control Manager settings, however all of this is quite trivial as you can find all of the necessary settings in the UI under General Settings. For more details check Notifications – Administration page in SonarQube documentation site, as SCM support page.

Your SonarQube server should now be correctly installed and configured to access LDAP. Ahhh, I almost forgot it, get the logging level back to INFO, otherwise you are risking quite a large log files on your disk.

UPDATE

After I published my post I realized that there is a better way of forcing the authentication. As Nicolas Bontoux pointed out this setting should be set in Administration – General Settings – Security pane. Otherwise you do risk encountering a problem during the upgrade of your SonarQube instance to a newer version.

Set the Force user authentication in previously mentioned pane.

Once done, comment or remove the sonar.forceAuthentication=true line from the sonar.properties configuration file and restart your service.
In this way you will not be bothered during your SonarQube updates.

UPDATE 2

In regard to the proxy settings, since SonarQube 5.5 specifying your proxy address via javaAdditionalOpts is not necessary anymore (and it is not advisable). Now it is sufficient to search in the sonar.properties configuration file the following line #http.proxyHost= and set the proxy parameters as shown here:

http.proxyHost=swg.myproxy.com
http.proxyPort=8080
https.proxyHost=swg.myproxy.com
https.proxyPort=8080

Save your settings and restart the service. Your Update Center should still be working correctly.