odbc module: ALTER SERVER AUDIT STATEMENT failed

GowthamSM12 commented 2 years ago

Summary

Hi, I am trying to execute ALTER SERVER AUDIT STATEMENT for Azure SQL Managed Instance, using Community.General.ODBC module with Commit parameter as False, but still getting the error as "ALTER SERVER AUDIT CANNOT BE USED INSIDE A USER TRANSACTION" message.

Issue Type

Bug Report

Component Name

Community.general.odbc

Ansible Version

$ ansible --version
ansible 2.9.18
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/home/hpansl01/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, Aug 13 2020, 02:51:10) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]

Community.general Version

$ ansible-galaxy collection list community.general
This command is not returning any results

Configuration

$ ansible-config dump --only-changed

OS / Environment

No response

Steps to Reproduce

- name: Enable Server Auditing

  community.general.odbc:

    dsn: "DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:xxx.yyy.database.windows.net;Database=master;UID={{ sqlmi_user}};PWD={{ sqlmi_password }};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

    query: "{{ Alter_Server_Audit }}"

    commit: no

  changed_when: no

  register: r_enableserveraudit

  delegate_to: localhost

Expected Results

I expecting the task to enable to server audit created in azure sql managed instance. Please refer to the below article from Microsoft for the same, https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure

Actual Results

{
    "exception": "WARNING: The below traceback may *not* be related to the actual failure.\n  File \"/tmp/ansible_community.general.odbc_payload_crzqeQ/ansible_community.general.odbc_payload.zip/ansible_collections/community/general/plugins/modules/odbc.py\", line 129, in main\n",
    "_ansible_no_log": false,
    "_ansible_delegated_vars": {},
    "changed": false,
    "invocation": {
        "module_args": {
            "dsn": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "query": "ALTER SERVER AUDIT [SQLAUDITING] WITH (STATE=ON)",
            "params": null,
            "commit": false
        }
    },
    "msg": "Failed to execute query: ('42000', u'[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ALTER SERVER AUDIT statement cannot be used inside a user transaction. (574) (SQLExecDirectW)')"
}