Access denied while installing SQL Server

Get Help
1

Hello everyone,

I am currently struggling with an automated SQL Server installation on a Windows Host using Ansible.

The Issue

The installation task works perfectly fine as long as I have an active RDP session open on the target machine. However, when I run the playbook on a freshly created VM without being logged in via RDP, the installation fails with the following error:

There was an error generating the XML document. Access denied

My Environment

  • Authentication: Currently using NTLM.
  • Privileges: I am connecting as a local Administrator.
  • Target OS: Windows Server (various versions).
  • Ansible Task:
- name: Install SQL Server
  ansible.windows.win_package:
    path: "{{ disk_image_out.mount_paths[0] }}setup.exe"
    arguments: >
      /ConfigurationFile=C:\install\sqlserver\Configuration.ini
      /IAcceptSQLServerLicenseTerms
    state: present
  become: true
  become_method: runas
  become_user: Administrator

I’ve read that CredSSP is a common fix, but it is disabled by default in my environment. Is Kerberos a viable alternative here?

2

I would like to share an update on this matter.

I read the following blog post by @jborean : Demystifying WinRM – Blogging for Logging.

As I understand it, the following happens when I connect to a remote host via WinRM without using CredSSP:

  1. A network logon session is created when I connect with the connection user via WinRM to the target machine.
  2. A token is created for the connection user.

So the password will not be provided to other resources.

Therefore, when I use “become” in my playbook, privilege escalation works with the previously created token.

Until here Everything is fine. I tested the installation with CredSSP to deliver the password, and it worked!

Then, I found this example in the Ansible Windows Win_Package module.

- name: Install Microsoft® SQL Server® 2019 Express (DPAPI example)
  ansible.windows.win_package:
    path: C:\temp\SQLEXPR_x64_ENU\SETUP.EXE
    product_id: Microsoft SQL Server SQL2019
    arguments:
      - SAPWD=VeryHardPassword
      - /ConfigurationFile=C:\temp\configuration.ini
  become: true
  vars:
    ansible_become_method: runas
    ansible_become_user: "{{ user }}"
    ansible_become_pass: "{{ password }}"

I tested this with NTLM again, and it also worked. Now I’m very confused.
I thought I couldn’t provide the become password to other resources without using CredSSP, which allows credential delegation. Why does this still work, then?

Could one of you explain this behavior to me, please?

3

Become and CredSSP are two different ways to achieve credential delegation in the task. Using CredSSP will delegate the username/password of the connection user as part of a connection/transport specific mechanism. Become uses an Ansible specific method that is agnostic to the connection allowing it to be used in more scenarios where CredSSP may not be available. You do not need CredSSP for become to work but they can be used together for whatever reason.

4

Thank you very much! I misunderstood and mixed some things up. In the blog post, one sentence led me to believe that I can only address DPAPI with CredSSP and that because CredSSP supports credential delegation, it only works properly with it.

Windows Data Protection API DPAPI is not accessible unless using CredSSP

To understand the whole thing correctly: Become and the authentication methods must be considered separately.

Credential delegation in authentication methods has nothing to do with become, right?

An example to help me understand it better:

I have a user A who is a local administrator on the target host.

I want to configure the machine with Ansible and connect via NTLM and WINRM, for example.

When connecting, a token is generated and I initially work with the limited token. When I then set “become,” I work with the full token, correct? To do this, however, I have to provide the become password.

So, theoretically, I can use any authentication method and most administrative tasks will still work with become? Except, of course, for the WinRM/Windows restrictions such as mounting and using SMB shares. Is that correct?

5

Yep, both can achieve a similar outcome just through a different mechanism.

Yep become is there to support credential delegation (amongst a few other things) that is agnostic to the connection plugin and authentication method used.

This is a slightly different problem but somewhat related but yes. Credential delegation solves the problem of authenticating with a downstream server your remote process is interactive with. The SMB mounting to a drive is some that Windows does not an interactive logon through things like RDP. While you could temporarily mount a drive in a task as the remote logon is then closed it will go away on the next task so it’s just not feasible to use in Ansible.

1 Like