Any tips on working with SQL Express on Windows

[Justin Seiser]

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,

[J Hawkesworth]

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

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

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.

Jon

[Justin Seiser]

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.

[J Hawkesworth]

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

[Justin Seiser]

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.

[J Hawkesworth]

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

Jon