I'm looking to start a project working against an Amazon image with SQL Express preinstalled. I know that it comes with windows auth. I know I can also configure the authentication mode to use a SA account. My real issue is that this will have to be done with powershell, and I can't not find a powershell command to return the current auth mode. I would really like some tips for doing things like this, so it's not going to flag itself as a change each run.
Has anyone already dealt with this? Things like enable sa user, enable tcp pipes etc.
Then I guess you can get the current state and return it into a registered variable (with
changed_when: False ),
then i
then only run the
#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
# Make the changes
$srv.Alter()
bit when the login mode isn’t what you expect. You could also then notify a handler to restart SQL Express to pick up the changed login mode.
Alternatively you could do the check and change in a simple (custom) module and if the module returns ‘changed: true’ then trigger a restart that way. Its a little more work to create a custom powershell module but worth having a look at some of the included ones (win_environment for example) as often the only extra things they do that you probably wouldn’t have to do in a one-shot powershell script is handling the module args and creating the hash that is used to return the results of the module execution.
Sorry its a bit vague I haven’t made much use of SQL Express.
Hope this helps to give an idea of how you could do this.
I think i can work from that, so thanks. My real issue is how to get the return of those powershell commands. Im sure im missing it in the ansible documentation but I dont know how to register and access the output of the script itself.
Its worth using ‘debug’ module to see what form the results take.
You will probably find that the output is in your_variable.stdout_lines so you can pick a single line from the results like this (using grant_results registered variable as an example - assuming the interesting bit is on 3rd line (first line will be [0]).
`
name: set grant result fact
set_fact:
grant_status: “{{ grant_results.stdout_lines[2] }}”
`
Powershell loves to spit out lots of space so you might want to use ‘trim’ filter to get rid of any trailing space, like this
`
name: set grant result fact avoiding spaces
set_fact:
grant_status: “{{ grant_results.stdout_lines[2]|trim }}”
`
Thanks, this is actually working out fine for me. I still have 2 ‘changed’ each run, but they are just scripts checking if my setting is already configured, and if not, we register that and then run a second script to actually do the change.
`
name: Run Check SQL TCP Script
win_shell: C:\Script\check_sql_tcp.ps1
register: check_sql_tcp
name: Run Set SQL TCP Script
win_shell: C:\Script\set_sql_tcp.ps1
register: check_sql_tcp
when: