How to link a SQL Server database to a Team Foundation Server repository

Applies to
ApexSQL Source Control

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

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

Description

Quick tip icon

Quick tip:

SQL Server management Studio 2016 and SQL Server 2016 will be used since the version of SQL Server/SSMS is not relevant in this case.

In order to link a database, follow the instructions on how to proceed through the source control wizard 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 is the selected version control system.

Make sure that the Team Foundation Server item is selected in drop down list under the Connection type step of the source control wizard:

In the System login step, credentials and the TFS repository URL should be specified:

TFS on premise

Username

A TFS user is a Windows user (account) from a machine where TFS is installed. In addition to this, a TFS user can be any other Windows account or group that is in the same domain as the machine where TFS 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 dialog when the specified user commits changes, as well as the user who created labels in the Label dialog.

Password

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

Quick tip icon

Quick tip:

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

In order to avoid entering a password each time ApexSQL Source Control needs to communicate with the repository, check the Store encrypted password box. This way, the encrypted password will be saved even after re-linking a database for any reason.

Quick tip icon

Quick tip:

In case a TFS is in a domain (a domain on a server machine or even on Azure) or in 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 TFS credentials (user and password). Instead, the user can check the Use Windows authentication box.

TFS server

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

<protocol>://<tfs_server_name>:<port>/<virtual_directory_name>

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

  • <protocol> – TFS can be used with http or https protocols
  • <tfs_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, check the Ports required for installation of Team Foundation Server.

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

Quick tip:

All 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 repository URL:

http://dev-machine01:8080/tfs

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

Using the Default Collection

If the TFS repository URL is specified like shown above, ApexSQL Source Control will, by default, try to connect to the Default Collection. In case the collection is stopped, the following error is returned by TFS:

Quick tip icon

Quick tip:

The same error appears in case any other Team Project collection is specified, if it is stopped.

Using other collection

In case the specific Team Project collection is used (instead of the default one), it has to be explicitly specified in the TFS repository URL, after the virtual directory name. The following is an example of the TFS URL with the specified Team Project collection called Production:

http://dev-machine01:8080/tfs/Production

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 its own 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 wizard, using the New button in the lower left corner of the Choose project dialog:

Visual Studio Team Services

Username

As a username for VSTS, the email that is used to connect to the VSTS account should be specified. Each user that has the appropriate permissions against VSTS repository will be able to link a database.

Password

As a password, alternate credentials should be used. In order to use alternate credentials, the option must be enabled. To enable it, navigate to the home page of the VSTS account and choose the Security option from the Account name drop down list:

Switch to the Alternate authentication credentials tab, and specify the password:

Quick tip icon

Quick tip:

Optionally, you can specify a secondary user name, and use it in combination with the alternate password. Otherwise the primary user name (which is an email address used to access the VSTS account) should be used in combination with the secondary (alternate) password.

TFS server

URL of the Visual Studio Team Services repository where a database will be linked

TFS URL must be specified as follows:

<protocol>://<vsts_account_name>.visualstudio.com

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

  • <protocol> – VSTS can be used with https protocol
  • <vsts_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 VSTS account, the existing work will not be automatically redirected. Check the Things to do before and after changing the VSTS account name.

Quick tip icon

Quick tip:

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

TFS permissions

Another important point when it comes to linking a database to TFS is what level of permissions are required in order for the user to link a database to TFS. Whether a TFS user is a part of any TFS group or added separately, the following permissions on a TFS 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 TFS on premise, as well as for Visual Studio Team Services.

Granting permissions on Visual Studio Team Services

To grant the Create workspace permission for the user collection level, navigate to the home page of your Team Services account, and from the Settings drop down list, select the Security option:

This initiates the collection level security section where all pre-defined TFS 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 TestUser to a group DevTeam01. To do so, highlight the TFS group in the list on the left, and click the Add button:

In the Add users and groups, the TestUser is specified and added by clicking the Save changes button:

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:

Hoovering over the inherited allow and clicking the Why? (this means why this permission is inherited), a brief description appears:

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 in 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 belongs to a specific group.

To add the required project level permissions, select the specific project inside the collection. We’ll use the VSTSPermissions sample project. From the Project home page, select the Security option from the Settings drop down list:

By default, for each project, the appropriate Team will be created, by default. Besides the default Team, pre-defined groups are also created and shown in the panel on the left side:

In this case, we’ll have VSTSPermissions Team and the TestUser already added. To check permissions that the default team has against the project, let’s switch from the Members tab to the Permissions tab. In this case, VSTSPermissions Team already inherited minimum permissions required:

Quick tip icon

Quick tip:

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

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

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

Q: Do I need additional permissions to create a TFS project through the add-in source control wizard?

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

Q: Do I need a separate project for each database that I want to link to TFS?

A: It is mandatory that each database has a separate project on TFS, as ApexSQL Source Control creates its own 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 changes for the specific TFS user?

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