Azure DevOps MS SQL firewall

[rodchenkov]

Hi all and thank you for really great provider.

I have issue with Azure DevOps pipeline when using mssql_user. During pipeline execution Azure DevOps has public IP, so we need to add it to MS SQL firewall to plan / apply mssql_user Is it possible to add inside of mssql_user task adding and removing current public IP firewall rule?

Or maybe you can suggest a workaround?

Thank you

[alxy]

This is a generic mssql provider, so this is very likely not really a fit for the code base of this provider. What you can do (if your security policies allow that) is to enable Allow access to Azure services on your server firewall, as explained here. As Azure pipelines run on Azure VMs, that will give the required access.

[rodchenkov]

@alxy thanks for suggestion, but looks like when SQL connection than this approach is not working. E.g. when we are using SqlAzureDacpacDeployment@1 in yaml CI/CD we have IpDetectionMethod: 'AutoDetect' and during running of this tasks it before run setup firewall rule and after completion removes it.

I'm not an expert at terraform, but maybe you can suggest something like before_hook, local-exec or remote-exec so that we can enable SQL firewall using cmd before terraform plan / apply and after finish remove those SQL firewall rule?

Thank you

[alxy]

I think doing it within Terraform itself is not a good idea. Why cant you just create extra steps in your pipeline to create a firewall rule and remove it? I don't have the exact code, but should be farily easy using azure cli, e.g.: az sql server firewall-rule create -g $(resourceGroupName) -s $(sqlServerName) -n $(ruleName) --start-ip-address ${{ pipelineIp }} --end-ip-address ${{ pipelineIp }}'

You need to find a good way how to get the pipeline ip however, not sure if there is a good process to do that, probably by calling some API that returns your public ip.

[rodchenkov]

@alxy yes for CI/CD it is possible to do, but we need to keep this in mind. Also for local machine you will have to go and do it manually.

Maybe you know about terraform options what is better to try first?

Thank you1

[alxy]

I don't like the idea of putting it in Terraform, remember that this is a descriptive language - so you are actually describing a desired state in your terraform configuration. There is no such thing as transient objects (like this firewall rule) in Terraform.

You can certainly achieve that with null_resources and couple of depends_on blocks, and triggers that always trigger (time-based). However, you will never get a clean plan, and it really feels hacky to me.

[rodchenkov]

@alxy thanks for your suggestions. One more point, can we change error message so that user will see SQL exception about firewall same as e.g. MS SQL Management Studio shows when blocked by firewall?

[alxy]

I'm not aware that this would be natively possible by terraform unforutnately :(

[rodchenkov]

@alxy thanks for all your help! Looks like we can close this issue