Tonie Huizer

16 July 2021

• SQL Source Control
• Database Source Control

Using Multiple Azure DevOps Organizations with SQL Source Control

Version controlling databases using Azure repos, TFVC and SQL Source Control

Developers should track and manage changes to database objects in the same way as they do for application code, whether it’s .NET, JavaScript, or any other language. This means that the database source code needs to go in a source control system as well! By doing so, you can version both the application and the database, better coordinate the database changes requires for each release. Also, everyone can see who made what changes, when, and why.

Azure DevOps (AzDo) provides developer services for teams to plan work, collaborate on code development, and build and deploy applications. AzDo offers many difference services but in this article, we’re using only the source control service of AzDo, called Azure Repos, in combination with Redgate SQL Source Control.

The following diagrams gives a high-level view of how one of my customers set up the source control architecture for all their development projects. Each division, or team, hosts their development projects within their own AzDo organization. Each project has its own database, and its own centralized TFVC repo, hosted on AzDo. When working on a project, a developer uses the SQL Source Control plugin for SQL Server Management Studio (SSMS) to create a direct link between the TFVC repo and the development copy of the database. They can commit changes directly to the centralized repo, as well as update the local working folders with changes made by others.

A developer is often required to work on more than one project and therefore needs access to the source code repo for multiple AzDo organizations.

Connecting from multiple organizations to the central version control system

A database developer, working in SSMS with SQL Source Control installed, needs to connect the development database to the correct Database folder, within an organization, which in turn connects to the centralized repo on the AzDo-hosted TFS server. SQL Source Control works with a range of different systems, both centralized and distributed, including TFVC but also Subversion, Git, Mercurial Perforce and others.

Each dev has credentials stored in a single Azure Active Directory (AAD) domain. This (in theory) allows them to connect to any repo from any organization. Here is a simple architectural overview of the setup of Azure Active Directory with two different organizations.

A dev can set up the link between the development database and the TFS server hosting the central TFVC repo by providing the Server URL and the Database folder location on that repo.

As discussed earlier, added may need to work on several different databases, across multiple projects, and so needs to connect to multiple organizations and repos. In this case, th repos are all hosted at the same Server URL (https://dev.azure.com).

With source code in a source control system the database DevOps process can start. Unfortunately, if you have a similar set up to the above, then you may hit a tricky authentication issue that prevents developers connecting to the central repo, as my customer did.

Authentication to multiple Azure DevOps organizations

Although the set up worked initially, and delivered the system my customer needed, they subsequently started experiencing a reproduceable authentication error where, over time, the authentication fails when

As soon as you hit “browse” at the database folder input or when you hit “link”, the authentication process will start. Under the hood this creates a token in the Windows Registry of the local machine that allows the user to connect to the repo for that organization. The authentication was succeeding initially, but then periodically failing, subsequently, as developers were trying to commit changes to the repo.

Even though the username is the same, we get the error:

“We were unable to establish the connection because it is configured for user tonie.huizer@global.com but you attempted to connect using user tonie.huizer@global.com. To connect as a different user perform a switch user operation. To connect with the configured identity just attempt the last operation again.”

Huh, what? Tonie is not Tonie? Whisky is not Whisky? What is happening?

The initial workaround: unlinking and relinking the database

The customer, uncertain what was causing the problem or how to solve it, managed to get around it, initially, by unlinking and relinking the database, whenever the error recurred:

1. Unlink the database.
2. Re-link the database like this:
   1. First select a folder in Organization1 Source Control.
   2. Change the Server URL to Organization2.
   3. Switch to a folder in Organization2.
3. Authentication success.

Unfortunately, unlinking and relinking to a large database is a very time-consuming process. We needed a proper fix, or at least a better workaround.

The better ‘fix’: resetting authentication data

I did some further research, and it became clear that the underlying problem was not with SQL Source Control. The error was being thrown by the Visual Studio Service (TFS) Client (version 14) that SQL Source Control is using to connect to the AzDo-hosted TFVC version control system.

The problem resides in the authentication data (access tokens) cached on the local computer. It needs to store access tokens for each organization, but it turns out that the underlying TFS Client that SQL Source Control uses can only store one token per URL (https://dev.azure.com).

The token for tonie.huizer@global.com organization 1 isn’t always the same as the token for tonie.huizer@global.com organization 2.

Based on this I was able to implement a better and much faster ‘fix’ that enabled users to quickly reset the authentication data whenever the problem occurred. The only thing we need to do is remove the VssApp key from the registry, which contains the cached token used to authenticate against https://dev.azure.com.This can be done by hand…

…or with a little .reg file containing this code:

Windows Registry Editor Version 5.00  [-HKEY_CURRENT_USER\Software\Microsoft\VSCommon\14.0\ClientServices\TokenStorage\VisualStudio\VssApp]

After removal, you are prompted to login again. This time with success. and a new key (token) in the registry. When switching to another AzDo Org the error can occur again. The solution remains the same: remove the registry key, by hand or by rerunning the script again.

Conclusion

The combination of SQL Source Control and AzDo-hosted sourced control makes it much easier to track database changes during team-based development. However, if an error is preventing you from reaching the repo, then your Database DevOps journey can be spoiled before it even started!

In this example, the error occurs when trying to allow a user to connect SQL Source Control to the repos hosted in multiple organizations, while handling authentication through a single AAD domain. However, given the nature of the error, I see no reason why it couldn’t also occur in in a setup with multiple different Azure Active Directories and only one organization. The workaround might be a solution for that situation as well.

I hope this explanation and the solution helps you set up a similar solution for your teams, while avoiding any lost time and frustration from authentication issues.