Manu's questions

I want to deploy some stored procedures across multiple MSSQL instances on multiple server. In my playbook I have a task which gathers all the instances on a server which I use in the following tasks to loop everything on any instance. I use this method on many of my playbooks but in this one I also need to be able to use a previously gathered variable in a when condition while looping the task.

These are the relevant parts of my playbook:

#################### GATHER INSTANCES ####################
    - name: list instances
      win_shell: (Find-DbaInstance -ComputerName localhost | Where Availability -eq 'Available').instancename
      changed_when: False
      register: sql_instances

#################### GATHER PROCEDURES ####################
    - name: list procedures
      win_shell: (Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }} -Database master -Schema dbo).Name
      changed_when: False
      register: sql_procedures
      loop: "{{ sql_instances.stdout_lines }}"  
      
#################### INSTALL CUSTOM PROCEDURES ####################
    - name: my_procedure
      ansible.windows.win_powershell: 
        script: |
          $batch_install_prodecure = @'
            --My Stored Procedure
          '@
          Invoke-DbaQuery -SqlInstance localhost\{{ item }} -Query $batch_install_prodecure
      when: sql_procedures.stdout_lines.find('my_procedure') != 0
      register: my_procedure
      loop: "{{ sql_instances.stdout_lines }}" 

This is the output:

#################### GATHER INSTANCES ####################
  "stdout_lines": [
    "MSSQLSERVER2",
    "MSSQLSERVER"
  ]
  
 #################### GATHER PROCEDURES ####################
   "stdout_lines": [
    "sp_Something",
    "sp_Somethingelse"
  ],
  "stderr_lines": [],
  "_ansible_no_log": false,
  "item": "MSSQLSERVER2",
  "ansible_loop_var": "item",
  "_ansible_item_label": "MSSQLSERVER2"
}
############################
  "stdout_lines": [
    "sp_Something",
    "sp_Somethingelse"
  ],
  "stderr_lines": [],
  "_ansible_no_log": false,
  "item": "MSSQLSERVER",
  "ansible_loop_var": "item",
  "_ansible_item_label": "MSSQLSERVER"
}

#################### INSTALL CUSTOM PROCEDURES ####################
The conditional check 'sql_procedures.stdout_lines.find('custProc_Backupcheck') != 0' failed. The error was: error while evaluating conditional (sql_procedures.stdout_lines.find('custProc_Backupcheck') != 0): 'dict object' has no attribute 'stdout_lines'

I kinda get why it does not work. I have to somehow describe with which item the variable is found since it is generated in a loop. I just don't know how to do it.

The furthest I've come so far is by trying to implement something similar as it was done in this post but I guess this method does not apply in my case

I know I could easily just do all this directly in PowerShell but in the end my goal is to install multiple procedures (one per task) and also be able to easily remove older ones for keeping a identical inventory of procedures on my MSSQL instances.

Can someone tell me how I have to set the when condition to achieve my goal?

Edit:

See the accepted answer for why it does not work.

I've now changed to check, weather the procedure is already installed or not, in PowerShell:

#################### INSTALL CUSTOM PROCEDURES ####################
    - name: my_procedure
      ansible.windows.win_powershell: 
        script: |
          $name_procedure = 'my_procedure'
          $install_prodecure = @'
            --My Stored Procedure
          '@
          if ((Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }}  -Database master -Schema dbo).Name -contains $name_procedure) {
              write-host "OK: SP $name_procedure is already installed"
          } else {
              write-host "SET: SP $name_procedure needs to be installed"
              Invoke-DbaQuery -SqlInstance localhost\{{ item }} -Query $install_prodecure
              if ((Get-DbaDbStoredProcedure -SqlInstance localhost\{{ item }}  -Database master -Schema dbo).Name -contains $name_procedure) {
              write-host "OK: SP $name_procedure sucessfully installed"
              } else {
                  write-host "ERROR: Was not able to install $name_procedure"
              }
          }
      register: my_procedure
      loop: "{{ sql_instances.stdout_lines }}" 
      failed_when: my_procedure.host_out.find('ERROR:') != -1
      changed_when: my_procedure.host_out.find('SET:') != -1

Overview

We log into Gucamole with a User from DomainA where we select a rdp-connection to a server from DomainB.

Trusts

DomainA to DomainB and vice versa:

• Type: External
• Kerberos AES Encryption support: no
• Direction: two-way
• Transitivity: no
• Authentication: Domain-wide

Permissions

User from DomainA has been joined to the local Remote Desktop Users group on the Server from DomainB. Have temporarily also tried with the local Administrators group.

Guacamole

The whole setup was not done by me and i don't have a lot of insight since it is managed by another team. What I know is that it works fine with a User from DomainA to a Server from DomainA. Users log into it with the upn and use 2FA by OpenOTP. If you guys think it would help to share some configurations of Guacamole let me know what you want to see and I check with the team.

Connections on Guacamole

• protocol: rdp
• hostname: ip of server from DomainB
• port: 3389
• username: ${GUAC_USERNAME}
• password: ${GUAC_PASSWORD}
• domain: blank
• security mode: NLA
• disable authentication: no
• ignore server certificate: yes
• everything else is set to default / not configured

Of course have we tried multiple different settings.

Symptoms

Now here is what happens.

• I log into Guacamole with the User from DomainA
• receive OpenOTP push and confirm
• am logged into Guacamole and select the connection to the server of DomainB
• receive error message:

The remote desktop server is currently unreachable. If the problem presists, please notify your system administrator, or check your system logs.

• after a few retrys I sometimes get this message:

This connection has been closed because the server is taking too long to respond. This is usually cuased by network problems, such as a spotty wireless signal, or slow network speeds. Please check your network connection and try again or contact your system administrator.

• and now comes the funny part that drives me crazy: I am able to log into the server of DomainB with the User from DomainA via direct rdp and if I do so, keep the connection open and start the connection on Guacamole, I am able to take over the session!!

Logs

Guacamole Logs show absolutely nothing useful.

Windows Security Log shows this event on the login error:

EventID 4625, Logon
An account failed to log on.

Subject:
    Security ID:        NULL SID
    Account Name:       -
    Account Domain:     -
    Logon ID:       0x0

Logon Type:         3

Account For Which Logon Failed:
    Security ID:        NULL SID
    Account Name:       User (correct)
    Account Domain:     DomainA (correct)

Failure Information:
    Failure Reason:     An Error occured during Logon.
    Status:         0xC000005E
    Sub Status:     0x0

Process Information:
    Caller Process ID:  0x0
    Caller Process Name:    -

Network Information:
    Workstation Name:   f7054e3b9dd7
    Source Network Address: Guacamole-Server-IP
    Source Port:        0

Detailed Authentication Information:
    Logon Process:      NtLmSsp 
    Authentication Package: NTLM
    Transited Services: -
    Package Name (NTLM only):   -
    Key Length:     0

This event is generated when a logon request fails. It is generated on the computer where access was attempted.

The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).

The Process Information fields indicate which account and process on the system requested the logon.

The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

The authentication information fields provide detailed information about this specific logon request.
    - Transited services indicate which intermediate services have participated in this logon request.
    - Package name indicates which sub-protocol was used among the NTLM protocols.
    - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

Troubleshooting

Now at this point I don't know where to set my next focus. We have tried multiple settings on the Guacamole rdp connection. Did a lot of research online but were not able to find examples with information where someone tries the same thing as we do. Since regular rdp works fine we think our trusts and permissions should be fine.

Can you guys give me any hints in which direction I should investigate?

Does someone use Guacamole in a similar setup?

Edit Additional Event Viewer info suggested from User Swisstone:

RemoteDesktopServices-RdpCoreTS
08:38:23 Info: The server accepted a new TCP connection from client *Guacamole-IP*:53494.
08:38:23 Info: Connection RDP-Tcp#78 created 
08:38:23 Info: Interface method called: PrepareForAccept
08:38:23 Info: Interface method called: SendPolicyData
08:38:23 Info: PerfCounter session started with instance ID 78
08:38:23 Warning: TCP socket was gracefully terminated
08:38:23 Info: Interface method called: OnDisconnected
08:38:23 Info: The server has terminated main RDP connection with the client.
08:38:23 Info: During this connection, server has not sent data or graphics update for 0 seconds (Idle1: 0, Idle2: 0).
08:38:23 Info: Channel rdpinpt has been closed between the server and the client on transport tunnel: 0.
08:38:23 Info: Channel rdpcmd has been closed between the server and the client on transport tunnel: 0.
08:38:23 Info: Channel rdplic has been closed between the server and the client on transport tunnel: 0.
08:38:23 Info: The disconnect reason is 14

TerminalServices-LocalSessionManager
nothing during this time

TerminalServices-RemoteConnectionManager
nothing during this time

Forgot to mention, the Server is 2019 Version 1809

Edit2

Ok, I got it working now by changing the security mode on the connection to tls. I remember having tried tls before when it was not working. Maybe some of the changes I did during the whole troubleshooting process made the difference. By now I can't tell what it was.

I came to this "solution" by randomly trying different options to connect using freerdp after I discovered that it is used by guacamole for rdp connections.

Does someone see any concerns in using tls instead of nla in this case?