允许VSTS更新测试数据库 [英] Allow VSTS to update test database

问题描述

为了运行我的验收测试，我需要在SQL Azure上运行的数据库上定义一个已知的良好状态.我的测试在本地运行良好，并设置了连接字符串以更新我在Azure PaaS上的SQL实例.使用VSTS部署数据库后，将运行测试.为了使部署过程能够运行我的验收测试，我需要运行Visual Studio Team System测试的过程才能访问数据库. VSTS显然在美国东部Azure区域中运行.鉴于我可能需要将数百个IP地址列入白名单，是否有更安全的方法做到这一点，获取部署过程的IP地址，然后允许该IP地址作为部署的一部分访问数据库?

In order to run my acceptance tests I need to define a known good state on the database running on SQL Azure. I have the tests running fine locally and have set up the connection string to update my instance of SQL on Azure PaaS. The tests will run after the database is deployed using VSTS. In order for the deploying process to run my accceptance tests I need the process running Visual studio team system tests to have access to the database. VSTS apparently runs in the East US Azure zone. Given there are potentially hundreds of ip addresses I would need to whitelist, is there a more secure way of doing this, grabbing the ip address of the deploying process and then allowing this IP address access to the database as part of the deployment?

推荐答案

您可以通过调用删除-AzureRmSqlServerFirewallRule powershell命令.

在构建/发布期间，请参考以下这些线程来进行操作:

Refer to these thread below to do it during the build/release: Deploy Dacpac packages via power shell script to Azure SQL Server

首先，您需要添加防火墙规则才能连接到Azure SQL 服务器.

First, you need to add firewall rule in order to connect to Azure SQL Server.

1.编辑您的构建定义

1.Edit your build definition

2.选择选项"选项卡，然后选中允许脚本访问OAuth令牌"

2.Select Option tab and check Allow Scripts to Access OAuth Token

3.添加Azure PowerShell步骤(参数:-RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -令牌$(System.AccessToken)-RG [资源组]-服务器[服务器名称] -ruleName $(Build.BuildNumber)

3.Add Azure PowerShell step (arguments: -RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -Token $(System.AccessToken) -RG [resource group] -Server [server name] -ruleName $(Build.BuildNumber)

代码:

param (
    [string]$RestAddress,
    [string]$Token,
    [string]$RG,
    [string]$Server
    )
$basicAuth = ("{0}:{1}" -f 'test',$Token)
$basicAuth = [System.Text.Encoding]::UTF8.GetBytes($basicAuth)
$basicAuth = [System.Convert]::ToBase64String($basicAuth)
$headers = @{Authorization=("Basic {0}" -f $basicAuth)}
$result = Invoke-RestMethod -Uri $RestAddress -headers $headers -Method Get
Write-Host $result.value
New-AzureRmSqlServerFirewallRule -ResourceGroupName $RG -ServerName $Server -FirewallRuleName "UnitTestRule" -StartIpAddress "$($result.value)" -EndIpAddress "$($result.value)"  

更新:

允许脚本访问OAuth令牌以进行发布:

Allow Scripts to Access OAuth Token for release:

1. 编辑版本定义
2. 点击在代理上运行
3. 选中允许脚本访问OAuth令牌"选项

这篇关于允许VSTS更新测试数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！