Windows installer fails via Ansible

[Justin Dugan]

I have a windows installer that fails when executed via Ansible. This installer connects to a SQLServer database during the install. It fails when attempting to connect to the database. 

ansible_winrm_kerberos_delegation is set and works fine when connecting to file shares. Database permissions are also correct (the installer runs fine when executed using powershell via RDP). Any assistance is greatly appreciated.

[Trond Hindenes]

I'm not completely sure what the delegation setting actually does - as far as I understand ansible it's up to your SQL Server to allow a delegated credential (I may be wrong here)

[Justin Dugan]

Thanks! So if I understand correctly, there is a configuration setting in SQL Server to allow delegated connections? Any idea what this configuration setting is? 

To further test my understanding.. Since it doesn't pop up and ask for credentials when executing the installer from windows (RDP connection to the server, then run the installer via powershell), isn't this also a connection via delegated credentials?

[Trond Hindenes]

No, there's a setting on the Computer object where you can trust a computer for delegation. 

So you have Ansible-->Server-->SQL Server. 

If my suspicions are right, you would have to edit "Server"'s computer object in AD and mark it with "Trust for delegation". 

Hopefully Matt Davis will chime in here, he's the guy who knows Ansible's WinRM implementation best.

[J Hawkesworth]

Rdp to the server and connecting to sql server is just one hop. Connecting from ansible to server and from server to sql server is a second hop, which is where the delegated credential becomes important.

If you want to check if delegation is working, you could try writing a file to a share on a second machine from ansible via your windows server.

It's been a while since I installed SQL Server, but if I recall there is/was a Surface Area configuration tool where you can set various options regarding things like ports and available authentication protocols. If that is still a thing, that might be worth looking at too.

Jon

[Justin Dugan]

The delegation is working (at least for Windows shares). The installer file is copied from a Windows file share before the install occurs and this completes successfully. Is it possible for the delegation to work for a file share and fail for SQL Server? 

[J Hawkesworth]

I would think the authentication delegation may be working - you may be able to examine the security event log on the SQLServer and see if there is a successful logon from whatever user ansible is connecting as.

but I think its possible that SQL Server has other requirements but worth checking the event log.

Also, does the installer write a log file or write to the event log.  It might be possible to determine why is failing from the installer's own logging.

Hope this helps,

Jon

[Justin Dugan]

The event log shows an install failure with code 1603. The application log shows the following:

[000433][06:45:02:0024] [15][ExecuteSequence.InstallDatabase][CreateConnection]

[000434][06:45:02:0397] [16][SQLServer.CreateConnection][CreateConnection] SqlExceptionLogin failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

[000435][06:45:02:0397] [14][StandardActions.DisplayRollBackError][DisplayRollBackError]

[000436][06:45:02:0397] [14][StandardActions.DisplayRollBackError]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

[000437][06:45:02:0398] [13][ExecuteSequence.InstallExecuteSeq]

Again, the installer runs fine if executed via powershell directly on the server. It's only failing if executed via ansible.

Thanks,

Justin

[Justin Dugan]

Does this help? Do you need anything else from me?

[J Hawkesworth]

1603 is unfortunately a generic 'something went wrong' message and so is of little value when working out what is failing.

The anonymous user login failure is the issue. Presumably when you run install interactively your current logged on user is used and your installer continues.

So it sounds like your ansible session credentials aren't available when installer runs via ansible.

I suggest you attempt to write to a temporary share on the sql server via ansible to prove or disprove that delegation is working for you. I think just reading from a share might not prove whether the delegation is working - writing a file using a UNC style path (i.e. \\someswrver\share\ ) would be a better test.

From the information available it seems the user credentials aren't available so I think first step is proving delegation is correctly set up.

Hope the helps,

Jon

[Justin Dugan]

Thanks Jon! Hopefully this proves that delegation is properly set up.

SWServer = The server where the application installer is executed

DBServer = The SQL Sever

[jdugan@centos7 ansible]$ ansible -m raw -a 'New-Item -path "\\DBServer\test\test" -type file -force' CDG --limit=SWServer

SWServer | SUCCESS | rc=0 >>

    Directory: \\DBServer\test

Mode                LastWriteTime     Length Name

----                -------------     ------ ----

-a---        03/20/2017     10:47          0 test

[J Hawkesworth]

That does seem likely to be working.

However, this line in your log output

[000434][06:45:02:0397] [16][SQLServer.

CreateConnection][CreateConnection] SqlExceptionLogin failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

suggests to me that it is attempting an anonymous login to the database during the installation.

Is there an option in the installer to pass credentials to the installer?

Jon

[Justin Dugan]

Jon,

I have checked with the installer developers and they have confirmed there are no options for specifying credentials. Any ideas why it's trying an anonymous login only when executed via ansible?

Thanks,

Justin

[J Hawkesworth]

Sorry, short answer is no, that's beyond my understanding at the moment.

Might be worth looking through your security policy settings (secpol.msc for local ones, speak to AD administrators for group policies).  But I admit I am clutching at straws here.

If you have access to installer developers would be worth asking them for a solution too.

Jon