Forum Discussion

slash83's avatar
slash83
Copper Contributor
Oct 19, 2023

ansible.windows.win_dsc resource SqlPermission Not found

Hi 

I am trying to do SQL (SQL Server 2022) Automation along with Always_ON and failover Cluster setup using Ansible on Azure VM (Windows 2019 Server) . Since SQL VM do not have internet connectivity , I tried to install below module manually by downloading respective nupkg files.

 

  • PowershellGet
  • SqlServerDsc

 

When we are trying to configure SQL Server Permission using below Ansible task, we receive following error messages ("msg": "Resource 'SqlPermission' not found.")

 

- name: configure SQL server permissions

  ansible.windows.win_dsc:

    resource_name: SqlPermission

    ServerName: 'LocalHost'

    InstanceName: '{{ sql_server_instance_name }}'

    Name: '{{ item }}'

    PermissionToInclude:

      - State: Grant

        Permission:

          - 'AlterAnyAvailabilityGroup'

          - 'ViewServerState'

          - 'AlterAnyEndPoint'

          - 'ConnectSql'

    Credential_username: '{{ mssql_username }}'

    Credential_password: '{{ mssql_password }}'

  loop: "{{ [sql_server_svc_account, sql_server_agt_svc_account] | unique }}"

 

Error Received

 

failed: [<hostname>.<domain_name>] (item=<domain_name>\<Serviceaccount>) => {
    "ansible_loop_var": "item",
    "changed": false,
    "item": "<domain_name>\\<service_account>",
    "msg": "Resource 'SqlPermission' not found."
}
[<hostname>.<domain_name>] TASK: set_alwayson_perm : configure SQL server permissions (debug)> User interrupted execution.

 

 

When we execute command Get-DscResource – Name SqlPermission, It errors out.

 

I believe I am missing on some steps or configuration while doing DSC Module SQLServerDsc

 

Please support.

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    Hi slash83,

    The easiest  solution to try to resolve the "Resource 'SqlPermission' not found" error is to install the latest version of the SQLServerDsc module. Here are the steps:

    1. On the Windows VM where you are running Ansible, open a PowerShell console.
    2. Run the following command to update the SQLServerDsc module:
     

     

    Update-Module -Name SQLServerDsc

     

    If the module is not installed, you can install it using the following command:

     

     

    Install-Module SqlServerDsc -Force

     

    1. Once the module is updated, verify that the SqlPermission resource is available by running the following command:
     

     

    Get-DscResource -Name SqlPermission

     

    If the command returns the SqlPermission resource, you should be able to configure SQL Server permissions using Ansible and DSC without any errors.

    To verify that the SqlPermission resource is defined correctly in your DSC configuration script, check the following:

    • Make sure that you are using the correct name of the resource (SqlPermission).
    • Make sure that the resource name is case-sensitive.
    • Make sure that all of the required properties are specified for the resource.


    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic
    (LinkedIn)

Resources