In my last article, I wrote about how you can define a CI/CD pipeline using the Azure pipeline.
Everything went as expected until the client moved the databases from an Azure database service to a SQL Managed Instance (SQL MI). At this point, the access to the SQL MI is private, so only a few IPs have access. Microsoft publishes a new list of IPs for the Azure pipeline every week, so you can try to add access to the SQL MI for all the IPs, but the best choice is to use a self-hosted agent, so we are going to talk about this.
The first step is to create a new Personal access token, that we are going to use when we create the self-hosted agent on the server.
Enter to Azure DevOps account, go to User Settings, then Personal access token, and create a new token with Agen Pool (read and manage) scope.
Now we can go to Organization settings, Agent pools, Default agent, New agent, and download the new agent.
After creating the agent, we need to run .\config.cmd in PowerShell, to enter the Azure DevOps URL and after that to enter the token created before. The best option is to create the agent as a service.
And now the fun begins, because we are using a .NET 6 SDK for our solution, so we need to install the SDK on the server.
Also, we are using the SQL package task to apply the migrations to the databases. For that, we are going to install SQL Server Data Tools (SSDT) on the server. After installing and restarting the agent on the server, we are going to see the SqlPackage capability on the capabilities page.
Now we can deploy the SQL package on Azure SQL MI, but only if we whitelisted the server IP on the Azure firewall. For that, we need to add the server IP in the Network security group defined for SQL MI.
A step in our pipeline is to build the .NET 6 solution. For that, we need to install on the target server the MS Build for VS 2022. We can install it from here. After installation, we need to restart the agent service If we check again the agent capabilities page, we are going to find the MS Build v17 capability.
New-Item -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2" ; New-Item -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client" ; New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client" -Name DisabledByDefault -PropertyType DWord -Value 0 ; New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client" -Name Enabled -PropertyType DWord -Value 00000001 ; New-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\.NETFramework\v4.0.30319" -Name SchUseStrongCrypto -PropertyType DWord -Value 1 ; New-Item -Path "HKLM:\SOFTWARE\Microsoft\.NETFramework\v4.0.30319\System.Net.ServicePointManager.SecurityProtocol"; New-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\.NETFramework\v4.0.30319\System.Net.ServicePointManager.SecurityProtocol" -Name "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -PropertyType String -Value tls12;
Now let’s change the pipeline to use the Default agent pool and run it.
pool: name: 'Default'
This article showed how to configure and use an agent pool, self-hosted on a Windows Server 2019 machine.