# Any tips on working with SQL Express on Windows

**URL:** <https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538>\
**Category:** Ansible Project\
**Tags:** windows\
**Created:** [January 23, 2018, 2:46am UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538 "2018-01-23T02:46:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Justin2](https://avatars.discourse-cdn.com/v4/letter/j/d2c977/32.png) [@Justin2](https://forum.ansible.com/u/Justin2)\
**Post date:** [January 23, 2018, 2:46am UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/1 "2018-01-23T02:46:09Z")

</div>

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.

Thanks,

---

<div class="post-metadata">

**Author:** ![j.r.hawkesworth](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@j.r.hawkesworth](https://forum.ansible.com/u/j.r.hawkesworth)\
**Post date:** [January 23, 2018, 5:34pm UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/2 "2018-01-23T17:34:38Z")

</div>

I don’t know if there’s any difference with Express but this guy seems to have done something similar  
[http://sqlblog.com/blogs/allen\_white/archive/2011/05/19/change-sql-servers-authentication-mode-with-powershell.aspx](http://sqlblog.com/blogs/allen_white/archive/2011/05/19/change-sql-servers-authentication-mode-with-powershell.aspx)

Assuming you can load the SMO dll (like here):

[http://sqlblog.com/blogs/allen\_white/archive/2008/01/09/create-agent-jobs-to-run-powershell-scripts.aspx](http://sqlblog.com/blogs/allen_white/archive/2008/01/09/create-agent-jobs-to-run-powershell-scripts.aspx)

Then I guess you can get the current state and return it into a registered variable (with

```auto
changed_when: False ),
then i

```

then only run the

```auto
#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.

Jon

---

<div class="post-metadata">

**Author:** ![Justin2](https://avatars.discourse-cdn.com/v4/letter/j/d2c977/32.png) [@Justin2](https://forum.ansible.com/u/Justin2)\
**Post date:** [January 23, 2018, 10:37pm UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/3 "2018-01-23T22:37:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![j.r.hawkesworth](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@j.r.hawkesworth](https://forum.ansible.com/u/j.r.hawkesworth)\
**Post date:** [January 25, 2018, 8:11am UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/4 "2018-01-25T08:11:14Z")

</div>

Sure, you need to use ‘register’ to capture the output from your script - something like this

`

- name: grant automation user access to the new cert  
script: grantRightsToMyCerts.ps1 {{ signing\_cert\_thumbprint }}"  
register: grant\_results

- name: debug grant\_results  
debug: var=grant\_results  
`

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 }}”  
`

Hope this helps,

Jon

---

<div class="post-metadata">

**Author:** ![Justin2](https://avatars.discourse-cdn.com/v4/letter/j/d2c977/32.png) [@Justin2](https://forum.ansible.com/u/Justin2)\
**Post date:** [January 25, 2018, 1:27pm UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/5 "2018-01-25T13:27:48Z")

</div>

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:

- check\_sql\_tcp.stdout\_lines[0] != “True”  
notify: Restart MSSQL  
`

Thanks for your assistance on these.

---

<div class="post-metadata">

**Author:** ![j.r.hawkesworth](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@j.r.hawkesworth](https://forum.ansible.com/u/j.r.hawkesworth)\
**Post date:** [January 25, 2018, 9:05pm UTC](https://forum.ansible.com/t/any-tips-on-working-with-sql-express-on-windows/26538/6 "2018-01-25T21:05:13Z")

</div>

You are welcome

You can probably use changed\_when: False for these scripts which are just gathering information

See [http://docs.ansible.com/ansible/latest/playbooks\_error\_handling.html#overriding-the-changed-result](http://docs.ansible.com/ansible/latest/playbooks_error_handling.html#overriding-the-changed-result)

Jon