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 ________________________________ From: [email protected] <[email protected]> on behalf of Ryad karkar <[email protected]> Sent: Monday, June 3, 2019 12:42:25 PM To: Ansible Project Subject: Re: [ansible-project] Ansible with Windows hosts Hi, Thanks for answers community ansible !! ;) Regards, Le samedi 25 mai 2019 11:59:04 UTC+2, Igor Turovsky a écrit : 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: - use become to become another user with permission to execute this script. (https://docs.ansible.com/ansible/latest/user_guide/become.html#become-and-windows) - use credssp - use Kerberos delegation 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. 2. (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 <[email protected]>: 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 :) Stefano Il giorno ven 24 mag 2019 alle ore 10:03 Ryad karkar <[email protected]> ha scritto: 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 !! :) Regards, -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/2599a68d-900c-419e-b698-fe748fae65eb%40googlegroups.com<https://groups.google.com/d/msgid/ansible-project/2599a68d-900c-419e-b698-fe748fae65eb%40googlegroups.com?utm_medium=email&utm_source=footer>. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAHSw9cq%3DMfL1%2B-VbkvnQ7bsc7CkPW%3DLJ6Wkn1LNxT9ZuAYPsSA%40mail.gmail.com<https://groups.google.com/d/msgid/ansible-project/CAHSw9cq%3DMfL1%2B-VbkvnQ7bsc7CkPW%3DLJ6Wkn1LNxT9ZuAYPsSA%40mail.gmail.com?utm_medium=email&utm_source=footer>. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To post to this group, send email to [email protected]<mailto:[email protected]>. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/02a1b52d-fd9c-4f39-8b6b-60acaf5d908f%40googlegroups.com<https://groups.google.com/d/msgid/ansible-project/02a1b52d-fd9c-4f39-8b6b-60acaf5d908f%40googlegroups.com?utm_medium=email&utm_source=footer>. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/SN6PR01MB40480E7DA87AEE35948229EBAB140%40SN6PR01MB4048.prod.exchangelabs.com. For more options, visit https://groups.google.com/d/optout.
