Ansible remote installation of SQL server 2016.

[1217...@qq.com]

hello everybodyDuring the installation of SQL server 2016, I encountered some problems:

1.In ansible, when deployed with ‘raw’, background hint: This SQL Server setup media does not support the language of the OS;

    When you deploy with ‘win_command‘, the installation error, log tip: the installer fails when the job UpdateResult is running.

    But it's okay to run directly on Windows CMD.Windows is Chinese and ansible is English.

2.How does ansible decompress the iso file format?

As follows，thinks:

[1217...@qq.com]

I think it might be Windows rights again.

[1217...@qq.com]

I looked at the Detail.txt file that was installed, and found that it was the following error.

Access denied，Should it be a question of permissions, how do you get around Windows authorization or generate credentials?

 

[Jordan Borean]

Installing SQL server requires access to the DPAPI and because a normal logon process does not have access to the user's credentials it is unable to access DPAPI. To get around this your either need to;

* Used CredSSP as the authentication transport, CredSSP sends the user's credentials and not just a hash so the WinRM session has the credentials required to access DPAPI
* Use become with the process, like CredSSP it would then have credentials to access DPAPI
* Use win_psexec, this is like become but is available pre-2.5
* Use a scheduled task, this is probably too fiddly and the above 3 options are easier to do so use this as a last resort.

Thanks

Jordan

[1217...@qq.com]

Ok, thank you very much!

Many of the previous failures of the Windows credentials , can be solved with CredSSP?

Windows does have a lot of limitations, and it will be much easier to deploy on Linux later.

在 2018年3月22日星期四 UTC+8下午7:09:30，Jordan Borean写道：

[Jordan Borean]

Unfortunately Windows has all these issues when it comes to credentials not being able to to the WinRM session where the major ones are;

* Being able to access a network share with the current users' credentials
* Being able to access DPAPI
* Being able to access the Windows Update API
* Some others like mapped drives

CredSSP solve the first 2 parts as the credentials are delegated as part of the protocol but the others should be solved by using become with Ansible as it changes the process from being spawned on a network logon to an interactive logon (like running it locally). The only thing I found become doesn't solve is mapped drives and I don't know any ways of getting it working. Luckily you can use become to set the credentials to authenticate with on network resources making mapped drives pretty much a moot point.

Thanks

Jordan

[1217...@qq.com]

Hi, I used the CredSSP document for some configuration, but I don't know what to do with the value of $certificate_thumbprint.

# note the value $certificate_thumbprint will be different in each
# situation, this needs to be set based on the cert that is used.
$certificate_thumbprint = "7C8DCBD5427AFEE6560F4AF524E325915F51172C"

As mentioned above：the value $certificate_thumbprint will be different in each. but how do I get the corresponding value?

I did the other configuration in the document to connect to the normal Windows host, but the corresponding domain user was still unable to connect.hosts:

[dbServer]

192.168.227.199

[adServer]

xll-win

dbServer.yml: 

ansible_user: Administrator

ansible_password: "Test123"

ansible_ssh_port: 5986

ansible_connection: winrm

ansible_winrm_transport: credssp

ansible_winrm_server_cert_validation: ignore

adServer.yml（Active Directory）:
ansible_user: xiell
ansible_pass: "Test123456"

ansible_port: 5986

ansible_connection: winrm

ansible_winrm_transport: credssp

ansible_winrm_server_cert_validation: ignore

http://www.absolutejam.co.uk/blog/ansible-windows-credssp/，In this case, mentioned that CredSSP has client and server, Do I need to configure it?

thanks

[Jordan Borean]

The docs should be more explicit, you don't need to specify a certificate thumbprint, it will use a random one generated each time the host starts. Those docs are only there if you wanted to set an explicit cert but that isn't required for CredSSP. The reason why it failed to connect is you need to set ansible_password not ansible_pass, the error message is complaining no password was set.

In this case, mentioned that CredSSP has client and server, Do I need to configure it?

No you only need to configure the server components, the client side is if you are using that WIndows host to connect to another host with CredSSP. Because you are just using Ansible to Windows, only the server side is needed.

Thanks

Jordan

[1217...@qq.com]

Thank you very much. I did write the wrong one.

However, after configuring the CredSSP, the installation of SQL server 2016 is still the same mistake as before, and what needs to be done to install it?

在 2018年3月23日星期五 UTC+8下午6:23:23，Jordan Borean写道：

[1217...@qq.com]

After installing CredSSP, ansible can access Windows via the domain user, but it will fail in ansible tower.

Error: CredSSP: requests auth method is CredSSP, but requests- CredSSP is not installed.

在 2018年3月23日星期五 UTC+8下午6:23:23，Jordan Borean写道：

The docs should be more explicit, you don't need to specify a certificate thumbprint, it will use a random one generated each time the host starts. Those docs are only there if you wanted to set an explicit cert but that isn't required for CredSSP. The reason why it failed to connect is you need to set ansible_password not ansible_pass, the error message is complaining no password was set.