TASK [mssql : Add MSSQL admin] - Could not open a connection to SQL Server

[oppsec]

Hi!

I'm using Arch Linux and trying to install GOAD to setup an Active Directory lab. After a long time, when the setup reaches the MSSQL section everything works fine, except when the script tries to create the MSSQL admin.

TASK [mssql : Add MSSQL admin] ***************************************************************************************************************************
changed: [srv02] => (item=NORTH\jon.snow)
failed: [srv03] (item=ESSOS\khal.drogo) => {"ansible_loop_var": "item", "changed": true, "cmd": "SqlCmd -E -Q \"CREATE LOGIN [ESSOS\\khal.drogo] FROM WINDOWS\"\nSqlCmd -E -Q \"SP_ADDSRVROLEMEMBER 'ESSOS\\khal.drogo','SYSADMIN'\"", "delta": "0:00:18.643000", "end": "2024-02-28 16:05:47.607222", "item": "ESSOS\\khal.drogo", "msg": "non-zero return code", "rc": 1, "start": "2024-02-28 16:05:28.964222", "stderr": "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .\r\nSqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.\r\nSqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..\r\nSqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .\r\nSqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.\r\nSqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..", "stderr_lines": ["Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .", "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.", "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..", "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .", "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.", "Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.."], "stdout": "", "stdout_lines": []}

I already have re-run the installer. This is my MSSQL YAML: /GOAD/ansible/roles/mssql/tasks/main.yaml:

- name: Reboot before install (long timeout in case of update)
  win_reboot:
    reboot_timeout: 1200

- name: create a directory for installer download
  win_file:
    path: c:\setup
    state: directory

- name: create a directory for installer extraction
  win_file:
    path: c:\setup\mssql
    state: directory

- name: create a directory for media extraction
  win_file:
    path: c:\setup\mssql\media
    state: directory

- name: create the configuration file
  win_template:
    src: files/sql_conf.ini.j2
    dest: c:\setup\mssql\sql_conf.ini

- name: check downloaded file exists
  win_stat:
    path: c:\setup\mssql\sql_installer.exe
  register: installer_file

- name: get the installer
  win_get_url:
      url: "{{download_url}}"
      dest: 'c:\setup\mssql\sql_installer.exe'
  when: not installer_file.stat.exists

- name: Add service account to Log on as a service
  win_user_right:
    name: SeServiceLogonRight
    users:
    - '{{ SQLSVCACCOUNT }}'
    action: add
  when: not SQLSVCACCOUNT == "NT AUTHORITY\\NETWORK SERVICE"

# - name: Setup service account
# Set-ADUser -Identity "{{SQLSVCACCOUNT}}" -ServicePrincipalNames @{Add='MSSQLSvc/castelblack.north.sevenkingdoms.local'}
# Get-ADUser -Identity "{{SQLSVCACCOUNT}}" | Set-ADAccountControl -TrustedToAuthForDelegation $true
# Set-ADUser -Identity "{{SQLSVCACCOUNT}}" -Add @{'msDS-AllowedToDelegateTo'=@('CIFS/winterfell.north.sevenkingdoms.local','CIFS/winterfell')}

- name: check install already done
  win_stat:
    path: "C:\\Program Files\\Microsoft SQL Server\\MSSQL15.{{sql_instance_name}}"
  register: mssql_install_already_done

# Install the database with a domain admin user
- name: Install the database
  win_command: c:\setup\mssql\sql_installer.exe /configurationfile=c:\setup\mssql\sql_conf.ini /IACCEPTSQLSERVERLICENSETERMS /MEDIAPATH=c:\setup\mssql\media /QUIET /HIDEPROGRESSBAR
  args:
    chdir: c:\setup
  vars:
    ansible_become: yes
    ansible_become_method: runas
    ansible_become_user: "{{domain_admin}}"
    ansible_become_password: "{{domain_admin_password}}"
    ansible_become_flags: logon_type=new_credentials logon_flags=netcredentials_only
  register: mssqlinstall
  until: "mssqlinstall is not failed"
  retries: 3
  delay: 120
  when: not mssql_install_already_done.stat.exists

- name: Add or update registry for ip port
  win_regedit:
    path: 'HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.{{ sql_instance_name }}\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    name: TcpPort
    data: 1433
  register: win_reg

# - name: Add or update registry for named pipe
#   win_regedit:
#     path: 'HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.{{ sql_instance_name }}\MSSQLServer\SuperSocketNetLib\Np'
#     name: Enabled
#     data: 0x00000001
#     type: dword
#   register: win_reg

- name: Restart a service
  win_service:
    name: 'MSSQL${{ sql_instance_name }}'
    force_dependent_services: yes
    state: restarted
  when: win_reg.changed

- name: Firewall | Allow MSSQL through Firewall
  win_dsc:
    resource_name: xFirewall
    Name: "Access for MSSQL (TCP-In)"
    Ensure: present
    Enabled: True
    Profile: "Domain"
    Direction: "Inbound"
    Localport: "1433"
    Protocol: "TCP"
    Description: "Opens the listener port for MSSQL"

- name: Firewall | Allow MSSQL discover through Firewall
  win_dsc:
    resource_name: xFirewall
    Name: "Access for MSSQL (UDP-In)"
    Ensure: present
    Enabled: True
    Profile: "Domain"
    Direction: "Inbound"
    Localport: "1434"
    Protocol: "UDP"
    Description: "Opens the discover port for MSSQL"

- name: Add MSSQL admin
  win_shell: |
    SqlCmd -E -Q "CREATE LOGIN [{{item}}] FROM WINDOWS"
    SqlCmd -E -Q "SP_ADDSRVROLEMEMBER '{{item}}','SYSADMIN'"
  become: yes
  become_method: runas
  become_user: "{{SQLSVCACCOUNT}}"
  vars:
    ansible_become_pass: "{{SQLSVCPASSWORD}}"
  loop: "{{sql_sysadmins}}"

- name: Add IMPERSONATE on login
  win_shell: |
    SqlCmd -E -Q "CREATE LOGIN [{{item.key}}] FROM WINDOWS"
    SqlCmd -E -Q "GRANT IMPERSONATE ON LOGIN::[{{item.value}}] TO [{{item.key}}]"
  become: yes
  become_method: runas
  become_user: "{{SQLSVCACCOUNT}}"
  vars:
    ansible_become_pass: "{{SQLSVCPASSWORD}}"
  with_dict: "{{executeaslogin}}"

- name: Add IMPERSONATE on user
  win_shell: |
    SqlCmd -E -Q "CREATE LOGIN [{{item.key}}] FROM WINDOWS"
    SqlCmd -E -Q "USE {{item.value.db}};CREATE USER [{{item.value.user}}] FOR LOGIN [{{item.value.user}}]"
    SqlCmd -E -Q "USE {{item.value.db}};GRANT IMPERSONATE ON USER::[{{item.value.impersonate}}] TO [{{item.value.user}}]"
  become: yes
  become_method: runas
  become_user: "{{SQLSVCACCOUNT}}"
  vars:
    ansible_become_pass: "{{SQLSVCPASSWORD}}"
  with_dict: "{{executeasuser}}"

- name: Enable sa account
  win_shell: |
    SqlCmd -E -Q "ALTER LOGIN sa ENABLE"
    SqlCmd -E -Q "ALTER LOGIN sa WITH PASSWORD = '{{sa_password}}' , CHECK_POLICY=OFF"
  become: yes
  become_method: runas
  become_user: "{{SQLSVCACCOUNT}}"
  vars:
    ansible_become_pass: "{{SQLSVCPASSWORD}}"

- name: enable MSSQL authentication and windows authent
  win_shell: |
    SqlCmd -E -Q "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2"
  become: yes
  become_method: runas
  become_user: "{{SQLSVCACCOUNT}}"
  vars:
    ansible_become_pass: "{{SQLSVCPASSWORD}}"

- name: Restart service
  win_service:
    name: 'MSSQL${{ sql_instance_name }}'
    force_dependent_services: yes
    state: restarted

Final results:

PLAY RECAP ***********************************************************************************************************************************************
dc01                       : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
dc02                       : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
dc03                       : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
srv02                      : ok=39   changed=12   unreachable=0    failed=0    skipped=6    rescued=0    ignored=0
srv03                      : ok=14   changed=1    unreachable=0    failed=1    skipped=3    rescued=0    ignored=0

[!] Fatal error from ansible with exit code: 2
[!] We are going to retry
[!] 3 restarts occurred, exiting...

[SamBugler]

Same issue today.

[410-sh]

I've been having the same issue over the last few days trying to build NHA. It worked when I installed different versions previously but seems to be broken now.

[oppsec]

I've been having the same issue over the last few days trying to build NHA. It worked when I installed different versions previously but seems to be broken now.

Would you happen to know which version worked before? I'll try installing it and see if it works

[410-sh]

I've been having the same issue over the last few days trying to build NHA. It worked when I installed different versions previously but seems to be broken now.

Would you happen to know which version worked before? I'll try installing it and see if it works

I built GOAD-Light around 4 or 5 days ago and it provisioned everything successfully. I had to rerun the script multiple times due to timeout of the VMs but eventually it all configured properly

[SamBugler]

In the end, I built it, then deleted just the machine on which it didn't work and rebuilt it again and everything worked. No idea why but might just be a race condition of some sort. On Sunday, March 3rd, 2024 at 6:55 PM, 410-sh @.***> wrote:

I've been having the same issue over the last few days trying to build NHA. It worked when I installed different versions previously but seems to be broken now. image

Would you happen to know which version worked before? I'll try installing it and see if it works

I built GOAD-Light around 4 or 5 days ago and it provisioned everything successfully. I had to rerun the script multiple times due to timeout of the VMs but eventually it all configured properly

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

[oppsec]

I removed MSSQL tasks from ansible/servers.yml file and everything works now. I'll try installing MSSQL now again and see if it works.

servers.yml

---
# Load datas
- import_playbook: data.yml
  vars:
    data_path: "../ad/{{domain_name}}/data/"
  tags: 'data'

- name: "Install IIS"
  hosts: iis
  roles:
    - { role: 'iis', tags: 'iis'}

- name: "Install Webdav"
  hosts: webdav
  roles:
    - { role: 'webdav', tags: 'webdav'}

Final results:

[✓] Command successfully executed
[✓] your lab is successfully setup ! have fun ;)

[Mayfly277]

if you got issue on mssql best way is going on the vm, delete all mssql programs if already installed (add/remove program), delete the c:\setup folder and the C:\Program Files\Microsoft SQL Server folder and relaunch the goad install script