Ansible with Windows hosts

Archives Ansible Project
1

Hey,

I would like to know if there is only one solution for that ansible communicate with Windows host ?

winrm and script powershell execute on Windows host ==> is the only solution for that ansible can communicate with Windows host ?

Because if i must configure all Windows host at hand, how do if i have 100 Windows server ?

There is one solution more simple for that ansible can communicate with Windows host please ? I want execute script sql on MSSQL on Windows Server 2012 r2 on port 1433

Thanks for your answer guy !! :slight_smile:

Regards,

2

Hi ,
Personally im using SSH ,installed from chocolatey repository.
Since Ansible 2.8 ,ansible start to support SSH on Windows comunication …

U have to dowload ansible 2.8 from github .It’s released some days ago :slight_smile:

Stefano

3

Hi Ryad,

Ansible indeed commuicates wth Windows via WinRM as standard Windows protocol for remote management.
Latest version of Ansible and latest version of Windows also support communication via SSH.

If you need to execute some SQL script on MS SQL, you have 2 directions:

  1. (simler for Windows guy). Use WinRM connection from Ansible to Windows Server where MS SQL is installed.
    Copy SQL script from Ansible host to Windows with win_copy module (https://docs.ansible.com/ansible/latest/modules/win_copy_module.html).
    Run sqlcmd with win_command module (https://docs.ansible.com/ansible/latest/modules/win_command_module.html) or win_shell (https://docs.ansible.com/ansible/latest/modules/win_shell_module.html) with Invoke-Sqlcmd cmdlet passing sql script as parameter.
    Since you are using WinRM you will hit double hop issue (i.e. your sql script will be running in context of local user and authentication will fail on connection to MS SQL since it will not recognize the user running the script - see see https://docs.ansible.com/ansible/latest/user_guide/windows_winrm.html#limitations). Workarounds are:

There methods will help in case you are using Windows only authentiation on SQL side.
If you want to use sql login, you supply alternate credentials in sqlcmd or invoke-sqlcmd in case you are using sql logins.

This would be rather quick way to achieve what you need though win_shell and win_command are anti-patterns for Ansible’s declarative approach.

  1. (simpler for Python guy) Use delegation and run query from Ansible host. You will have to create some python code (ideally in form of Ansible module) what will connect to SQL from Ansible controller and execute the script. In this case you will connect from Ansible host to MS SQL via 1433/tcp.
    AFAIK, there is no ready to use module to run SQL scripts on MS SQL from Ansible, but there is module for database creation/deletion, you may want to take a look on it (doc - https://docs.ansible.com/ansible/latest/modules/mssql_db_module.html#mssql-db-module, repo - https://github.com/ansible/ansible/blob/devel/lib/ansible/modules/database/mssql/mssql_db.py).

Thank you,

  • Igor

пт, 24 мая 2019 г. в 19:26, Stefano Leandro <mr.stefanoleandro@gmail.com>:

4

Hi,

Thanks for answers community ansible !! :wink:

Regards,

5

Actually, there is one more option in case your servers already have Powershell 5 - you can use Ansible’s win_dsc module and this resource https://github.com/PowerShell/SqlServerDsc/blob/dev/README.md#sqlscript from sql server dsc.

Thank you,

Igor

Thank you,

  • Igor
6

Hi Igor,

Thank you very much for your answer !! :slight_smile:

I will try this issue :wink:

Regards,