SQL Server does not allow for firewall update - Error creating the Firewall Rule instance

Question

Hello community i am running the below ansible playbook through azure cli. Also i get similar results from an Ubuntu 18.04.4 LTS.

Also i have raised a github issue: https://github.com/Azure/Ansible/issues/21

What i mean using Azure CLI?

Go to Azure:

-> Create a new Cloud Shell

-> Ansible is already installed

-> create a new yml file. Copy the below script.

-> Run the below playbook. With this command ansible-playbook nameofyourfile.yml

-> The below script fails

- hosts: localhost
  connection: local
  vars:
    resource_group: ansibleResourceGroupName
    webapp_name: ansibleWebAppName
    plan_name: ansibleWebPlanName
    location: westeurope
    server_name: AnisbleDemoSqlServer
    database_name: AnsibleDemoSqlDatabase
  tasks:
    - name: Create a resource group
      azure_rm_resourcegroup:
        name: "{{ resource_group }}"
        location: "{{ location }}"

    - name: Create App Service on Linux with dotnetcore
      azure_rm_webapp:
        resource_group: "{{ resource_group }}"
        name: "{{ webapp_name }}"
        plan:
          resource_group: "{{ resource_group }}"
          name: "{{ plan_name }}"
          is_linux: true
          sku: S1
          number_of_workers: 1
        frameworks:
          - name: "dotnetcore"
            version: "3.1"

    - name: Create (or update) SQL Server
      azure_rm_sqlserver:
        resource_group: "{{ resource_group }}"
        name: "{{ server_name }}"
        location: "{{ location }}"
        admin_username: panoskarajohn
        admin_password: Testpasswordxyz12!

    - name: Create (or update) SQL Database
      azure_rm_sqldatabase:
        resource_group: "{{ resource_group }}"
        server_name: "{{ server_name }}"
        name: "{{ database_name }}"
        location: "{{ location }}"

    - name: Create (or update) Firewall Rule
      azure_rm_sqlfirewallrule:
        resource_group: "{{ resource_group }}"
        server_name: "{{ server_name }}"
        name: firewallruleAllowAll
        start_ip_address: 0.0.0.0.
        end_ip_address: 255.255.255.255

My sqlserver is created. But the firewall rule fails, with an unauthorised error.

At the end i have provided the errors

Also when i try to do it manually to add a firewall rule through the azure portal. Everything is deactivated. Also the add client ip seems to be inactive.

Also even with the choices i am allowed to change, the save button is unresponsive. The whole firewalls page seems unresponsive.

See image for more info.

When i create through the azure portal a new sql server, everything seems to operate.

Any help is appreciated.

Error i get:

fatal: [localhost]: FAILED! => {"changed": false, "msg": "Error creating the Firewall Rule instance: 400 Client Error: Bad Request for url: -> Some url.

When i click on the url i get this json -> {"error":{"code":"AuthenticationFailed","message":"Authentication failed. The 'Authorization' header is missing."}}

Ansible Version for my Ubuntu Machine

ansible 2.9.6 config file = None configured module search path = [u'/home/pkaragiannis/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules'] ansible python module location = /usr/local/lib/python2.7/dist-packages/ansible executable location = /usr/local/bin/ansible python version = 2.7.17 (default, Nov 7 2019, 10:07:09) [GCC 7.4.0]

Ansible Output from Ubuntu VM

PLAY [localhost] ***************************************************************************************

TASK [Gathering Facts] ********************************************************************************* ok: [localhost]

TASK [Create a resource group] ************************************************************************* changed: [localhost]

TASK [Create App Service on Linux with dotnetcore] ***************************************************** changed: [localhost]

TASK [Create (or update) SQL Server] ******************************************************************* [WARNING]: Azure API profile latest does not define an entry for SqlManagementClient changed: [localhost]

TASK [Create (or update) SQL Database] ***************************************************************** changed: [localhost]

TASK [Create (or update) Firewall Rule] **************************************************************** fatal: [localhost]: FAILED! => {"changed": false, "msg": "Error creating the Firewall Rule instance: 400 Client Error: Bad Request for url: https://management.azure.com/subscriptions/******-*****-*******/resourceGroups/ansibleResourceGroupName/providers/Microsoft.Sql/servers/AnisbleDemoSqlServer/firewallRules/firewallruleAllowAll?api-version=2014-04-01"}

PLAY RECAP ********************************************************************************************* localhost : ok=5 changed=4 unreachable=0 failed=1 skipped=0 rescued=0 ignored=0

Answer 1

When you want to create the SQL server and the firewall rules for it, I see you use the service principal to authenticate. So first of all, the service principal should have the Contributor role of the subscription or the resource group that you would use.

Log in with the service principal, and then the Azure CLI command to create the SQL server and the firewall rules:

az sql server create -l westus -g mygroup -n myserver -u myadminuser -p myadminpassword
az sql server firewall-rule create -g mygroup -s myserver -n myrule --start-ip-address 1.2.3.4 --end-ip-address 5.6.7.8

With the Ansible, it should also work and I did not see any problems in the code. Just set the credentials for it with the service principal correctly.

Update:

Here is the screenshot of the ansible:

Update-1:

Here is the YAML file of above screenshot:

- hosts: localhost
  connection: local
  tasks:
    - name: Create (or update) SQL Server
      azure_rm_sqlserver:
        resource_group: mygroup
        name: mysqlname
        location: eastus
        admin_username: username
        admin_password: password

    - name: Create (or update) Firewall Rule
      azure_rm_sqlfirewallrule:
        resource_group: mygroup
        server_name: mysqlname
        name: FirewallRule1
        start_ip_address: 10.0.17.62
        end_ip_address: 10.0.17.62