Any tips on working with SQL Express on Windows

Archives Ansible Project
1

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,

2

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

3

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.

4

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

5

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.

6

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