How to link a SQL Server database to a Team Foundation Server (Azure DevOps Server/Services) repository

Applies to

ApexSQL Source Control

Note:

Team Foundation Server (TFS) is now called Azure DevOps Server, and Visual Studio Team Services (VSTS) is now Azure DevOps Services.

Summary

This article describes how to link a database to a Team Foundation Server (Azure DevOps Server/Services) repository as well as which permissions are required for the TFS (Azure DevOps) user (that need to be granted on TFS (Azure DevOps Server/Services) repository in order to link a database and use TFS (Azure DevOps Server/Services) projects.

Since TFS (Azure DevOps) comes in two offerings: On-premise and Azure DevOps Services, we’ll go through each one and explain how to link a database.

Description

To link a database, follow the instructions on how to proceed through the Source control setup window in How to link a database to source control article as the same steps are needed for any version control system. We’ll focus on specific settings and required information when TFS (Azure DevOps) is the selected version control system.

  • Note: In ApexSQL Source Control is still present Azure DevOps source control system by its old name Team Foundation Server (TFS).

Make sure that the Team Foundation Server source control system is selected in the drop-down list under the Connection type tab in the Source control setup window:

The Connection type tab in Source control setup window when linking a database to Azure DevOps repository

In the System login tab, credentials and the TFS (Azure DevOps Server/Services) repository URL should be specified:

The System login tab in the Source control setup window when linking a database to Azure DevOps repository

Team Foundation Server (Azure DevOps Server) on-premise

TFS (Azure DevOps Server) username

A TFS (Azure DevOps Server) user is a Windows user (account) from a machine where TFS (Azure DevOps Server) is installed. In addition to this, a TFS (Azure DevOps Server) user can be any other Windows account or group that is in the same domain as the machine where TFS (Azure DevOps Server) is installed.

Quick tip icon

Quick tip:

What is specified in the username field will be shown later in the Project history or the Object history window when the specified user commits changes, as well as the user who created labels in the Label window.

TFS (Azure DevOps Server) password

The password for the appropriate TFS (Azure DevOps Server) user is the same as the password used by the Windows account to access (log on) to the machine where the TFS (Azure DevOps Server) is installed.

Quick tip icon

Quick tip:

It is required that each Windows account (that will be used as a TFS (Azure DevOps Server) user) on a machine where TFS (Azure DevOps Server) is installed is password protected, in case the Windows authentication is not used (in case the user is not in the same domain/workgroup).

To avoid entering a password each time ApexSQL Source Control needs to communicate with the repository, check the Remember password option.

Quick tip icon

Quick tip:

In case TFS (Azure DevOps Server) is in a domain (a domain on a server machine or even on Azure) or the same workgroup, and the machine where the user is linking a database from SSMS is a part of the same domain/workgroup, there is no need to explicitly specify Azure DevOps Server credentials (user and password). Instead, the user can check the Use Windows authentication option.

TFS (Azure DevOps Server) repository path (URL)

In the TFS Server field, the URL of the TFS (Azure DevOps Server) repository should be specified. For TFS (Azure DevOps Server) on-premise, the URL must be specified as follows:

<protocol>://< tfs_server_name (Azure DevOps_server_name)>:<port>/<virtual_directory_name>

The following is an explanation for each part enclosed in angle brackets:

  • <protocol> – TFS (Azure DevOps Server) can be used with HTTP or HTTPS protocols
  • <tfs_server_name (Azure DevOps_server_name)> – host machine, NetBIOS, or DNS name
  • <port> – by default 8080 is used
    Quick tip icon

    Quick tip:

    To find out more about required ports when installing and using TFS (Azure DevOps Server), check the Ports required for Azure DevOps Server article.

  • <virtual_directory_name> – default name is tfs, but this can be changed during the TFS configuration process
    Quick tip icon

    Quick tip:

    All the above-mentioned fields are mandatory. The error will appear in case any segment is not specified, or if it is incorrectly specified.

The following is an example of a TFS (Azure DevOps Server) repository URL:

http://dev-machine01:8080/tfs

In the above example, HTTP is used as a protocol, the machine name is dev-machine01, default port (8080) is used as well as the default virtual directory name (tfs).

TFS (Azure DevOps Server) collection

In the Collection field, Team Project Collection must be specified. By default that is DefalutCollection. To choose any other already existing collection, click on the Refresh button next to Collection field and from a drop-down list choose the other collection:

Specifying the collection under the System login tab in the Source control setup window when linking a database to  the TFS (Azure DevOps Server) repository

TFS (Azure DevOps Server) project

In the Project field, the user specifies the project inside the repository where a database will be linked. It is strongly recommended that each database has a project. However, a single project still can be used for multiple databases, but in that case, all objects from all linked databases will be tracked at once. Using the same project is suggested in case the same database is linked from different environments or by different users where the project will be a central place for all the changes.

Browsing the repository, the user can choose any of the existing projects, as well as to create a new one, directly from the Source control setup window, using the New button in the lower-left corner of the Choose project window which will be shown when the Browse for folder button is clicked:

The Choose project window in the Source control setup window

Azure DevOps Services

Azure DevOps Services username

As a username for the Azure DevOps Services repository, the email that is used to connect to the Azure DevOps Sevices account or the alternate username should be specified. Each user that has the appropriate permissions against Azure DevOps Services repository will be able to link a database to source control repository.

Azure DevOps Services password

As a password, the alternate password can be used. In order to use alternate credentials, the option must be enabled on the repository. To enable it, navigate to the home page of the Azure DevOps Services account and choose the Alternate credentials option from the User settings drop-down list:

The Alternative credentials option in the User setting drop-down list from the Azure DevOps Services page

In the Alternate credentials tab specify the username and password and click the Save button:

Creating the alternate credentials for the Azure DevOps Services account

The best practice, when linking a database to Azure DevOps Services repository, is to use a Personal access token instead of an alternate password.

To create a Personal access token, click the Personal access tokens option from the User settings drop-down list:

The Personal access tokens option in the User settings drop-down list of the Azure DevOps Services page

In the Personal access tokens tab click the New Token button:

The New Token option in the Personal access tokens tab

The Create a new personal access token dialog will appear, where a name, organization and expiration date should be set:

Specifying the Token name, organization and expiration data in the Create a new personal access token window

Note: Be sure the Full access option is chosen under the Scopes section:

Choosing the Full access option in the Create a new personal access token window

After the Create button is clicked, the following message will appear:

Successfully created personal access token message

Note: Personal access token should be copied and saved from the above window because there is no way to ‘’see’’ it again.

Azure DevOps Services repository path (URL)

Azure DevOps Services repository URL under the System login tab of the Source control setup window must be specified as follows:

<protocol>://dev.azure.com/<Azure DevOps Services_account_name>

The following is an explanation for each part enclosed in angle brackets:

  • <protocol> – Azure DevOps Services can be used with HTTPS protocol
  • <Azure DevOps services_account_name> – The name of the Team Services account. It can be changed additionally
Quick tip icon

Quick tip:

When changing the name of the Azure DevOps Services account, the existing work will not be automatically redirected. Check the Rename your organization in Azure DevOps article.

Quick tip icon

Quick tip:

Team Project collections are not yet implemented in Azure DevOps Services, and for that reason, it is assumed that DeafultCollection is used, thus it can be omitted from the Azure DevOps URL or it can be specified, the result will be the same.

Azure DevOps Services repository permissions

Another important point when it comes to linking a database to Azure DevOps Services repository is what level of permissions are required for the user to link a database to Azure DevOps Services repository. Whether an Azure DevOps Services user is a part of any Azure DevOps group or added separately, the following permissions on an Azure DevOps Services repository must be granted in order for the user to link a database and communicate with the repository:

  • Create workspace
  • View project-level information
  • Edit project-level information
Quick tip icon

Quick tip:

Mentioned permissions are required for Azure DevOps Server on premise, as well as forAzure DevOps Services.

Granting permissions on Azure DevOps Services repository

To grant the Create workspace permission for the user collection level, navigate to the home page of your Team Services account, and click the Organization settings option in the lower-left corner of the home page:

The Organization settings in the Azure DevOps Service page

In the Organization Settings page click the Permissions tab:

The Permission tab under the Organization settings in the Azure DevOps Service page

This initiates the collection level security section where all pre-defined Azure DevOps Services groups are shown along with the appropriate users for each group and a set of collection-level permissions for each group. For instance, we’ll add a user called Nik to a group DevTeam01. To do so, click on DevTeam01 group in the list:

The groups sub-tab in The Permission tab under the Organization settings in the Azure DevOps Service page

Go to the Members tab and click on the Add button:

The Members sub-tab under the Groups tab

In the Invite members to DevTeam01 window specify a user to add, and click the Save button:

The Invite members window

By default, each new group will be a member of the Project Collection Valid Users group. This means that any new group will inherit its granted permissions set. Checking the Permissions section for the DevTeam01 group, we can see that the Create workspace permission is already granted:

The Create a workspace permission

The tooltip next to the Create a workspace permission explains the inherited Allow permission:

The tooltip for the inherited Allow permission

Quick tip icon

Quick tip:

In case that the following permission is not granted for any reason, in the Project Collection Valid Users group, you can either grant it in that group or the newly created group, in this case, DevTeam01. Keep in mind that by granting permission for a group, you are granting it to all the users that belong to a specific group.

To add the required project-level permissions, select the specific project, we’ll use the ADO Permissions project. From the Project home page, select the Project settings option in the lower-left corner of the page:

The Project settings option in the Azure DevOps Services page

By default, for each project, the appropriate team will be created. To check this go to Teams tab, in this case, ADO Permissions Team  will appear:

The Teams tab in the Azure DevOps Services page

Click on the team name to see all team members. In our case it is only one member Nik:

Members in the Azure DevOps Services team

To check permissions that the default team has against the project, switch from Teams to the Permissions  tab in the Project Setting page, and choose a team (in our case the ADO Permissions Team) from the Groups tab:

The Groups sub-tab under the Permissions tab in the Project Settings page

In the Permission tab for the Edit project-level information and View project-level information permissions in the drop-down list set the Allow option, to give the team the necessary permissions:

The Permissions tab in the Project settings page

Quick tip icon

Quick tip:

Mentioned permissions can be granted similarly for the Azure DevOps Server on-premise, using the Web access URL (http://dev-machine01:8080/tfs) and accessing the Security panels for the appropriate collection and project.

FAQs

Q: Can I connect to Azure DevOps Services hosted by Azure?

A: Yes, this is supported. Azure DevOps Services can be used even with the AD account if such an environment is set.

Q: Do I need additional permissions to create a TFS (Azure DevOps Server/Services) project through the add-in to the Source control setup window?

A: Yes, the Create project and Delete project permissions are required to create and delete TFS projects respectively.

Q: Do I need a separate project for each database that I want to link to TFS (Azure DevOps Server/Services) repository?

A: Each database must have a separate project on TFS Azure DevOps Server/Services) repository, as ApexSQL Source Control creates its structure on the repository. The exception would be for a database that is the same across different environments (dev, QA, production) that should be synced using the single repository.

Q: How can I change the password for linking a database, in case it is changed for the specific TFS Azure DevOps Server/Services) user?

A: Simply by unlinking a database and linking it back to the same repository, you will be able to specify a new password.