PowerShell MS SQL Server Module Examples With Bolt
Let's explore a little bit about using Bolt tasks in the Puppet module for Microsoft SQL Server. This can help make sharing your code, as well as tasks, with the rest of your team even easier.
Table of Contents:
- What is Bolt?
- How to Set Up Your Powershell MS SQL Module Environment
- PowerShell MS SQL Server Module Examples: Next Steps
What is Bolt?
Bolt is a multi-platform task automation application that lets you run one-off tasks against multiple machines at the same time, and makes it easy to share the tasks you're running with the rest of your team.
The tasks that we have implemented in the SQL Server module really showcase that ability to share code with your team. Did I mention it's agentless? If you, or a DBA you know doesn't want extra agents installed on the database boxes, don't worry. I get it, and it's not a problem.
And if you hadn’t already heard, we recently made the SQL Server module more freely available so that it’s easy for anyone to start managing it with Bolt.
Before I dive into the tasks themselves, if you want to just follow along by reading to the end that's great. If you want to follow along by trying it out as you read then you will need to install Bolt on your machine first. You should also consider reading this excellent blog post by Glenn Sarti detailing the more general subject of using PowerShell with Bolt.
How to Set Up Your Powershell MS SQL Module Environment
To see and use these tasks from the command line you will need to have Bolt installed on your machine and you will need to install the SQL Server module.
To install the module with Bolt ensure that your Bolt configuration directory has a file called Puppetfile in it. You should be able to find it in your $env:HOME directory at $env:HOME\.puppetlabs\bolt\Puppetfile. Ensure that the Puppetfile has the following line somewhere in it: mod 'puppetlabs-sqlserver'
Once that line is in place you should be able to run the following command: bolt puppetfile install, and you should get output something like the following:
PS >bolt puppetfile install
Successfully synced modules from C:/Users/<username>/.puppetlabs/bolt/Puppetfile to C:/Users/<username>/.puppetlabs/bolt/modules
PS >Get-ChildItem $env:HOME\.puppetlabs\bolt\modules
Directory: C:\Users\<username>\.puppetlabs\bolt\modules
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 3/21/2019 21:15 sqlserverYou will know it worked if you can run the following command and get output that includes tasks that have sqlserver:: in their name:
PS >bolt task show
facts Gather system facts
package Manage and inspect the state of packages
puppet_agent::install Install the Puppet agent package
puppet_agent::version Get the version of the Puppet agent package installed. Returns nothing if none present.
puppet_conf Inspect puppet agent configuration settings
reboot Reboots a machine
reboot::last_boot_time Gets the last boot time of a Linux or Windows system
service Manage and inspect the state of services
sqlserver::get_sql_logins Retrieve information about the logins configured for a SQL Server instance.
sqlserver::get_sqlagent_jobs Return information about SQL Agent jobs and job steps.
sqlserver::set_sql_logins Set IsDisabled, and Password properties of a SQL Login
MODULEPATH:
C:/Users/<username>/.puppetlabs/bolt/modules:C:/Users/<username>/.puppetlabs/bolt/site-modules:C:/Users/<username>/.puppetlabs/bolt/site
Use `bolt task show <task-name>` to view details and parameters for a specific task.The Tasks
With that said, we can get into what these new tasks are, and how you can use them on the command line or in the Puppet Enterprise GUI. When we're done with what these tasks can do, we'll talk about how they are implemented, and how you can even leverage some of the code we wrote to write custom tasks for your own environment.
sqlserver::get_sql_logins
We saw above that the task exists on our machine, now let’s get some additional information about it so we know how to use it.
PS >bolt task show sqlserver::get_sql_logins
sqlserver::get_sql_logins - Retrieve information about the logins configured for a SQL Server instance.
USAGE:
bolt task run --nodes <node-name> sqlserver::get_sql_logins instance_name=<value> login_name=<value> exact_match=<value> detailed=<value>
PARAMETERS:
- instance_name: Optional[Variant[Array[String], String]]
The name of the SQL Instance running on the machine to connect to. Leave blank for the default instance of MSSQLSERVER
- login_name: Optional[Variant[Array[String], String]]
The name of a particular login to search for. You can use partial names and any pattern that will work with the PowerShell '-match' operator.
- exact_match: Optional[Boolean]
If set to true it will force names passed to the LoginName parameter to be an exact match to a SQL Login to pass the filter.
- detailed: Optional[Boolean]
Return more detailed information from the server instead of the default summary information
MODULE:
C:/Users/<username>/.puppetlabs/bolt/modules/sqlserverThe output above tells you all you need to know about how to use the task. Notice that all of the parameters for this task are marked Optional. This is because if you just run the task against a SQL Server with no parameters at all, its default behavior will be to look at all sql instances on a server and return information about all of the logins that it finds. Let's see an example of that. In the command below to execute the task you will see that I have already set up a SQL Server called gu213n926ppeudt.delivery.puppetlabs.net. The ‘winrm://’ portion in front of the machine name tells Bolt that I want to connect to it over WinRm, not SSH. The --no-ssl-verify is there because this is a stand alone-machine, not domain joined, so my machine doesn’t trust the machine certificate of the test box I’m connecting to. Using --no-ssl-verify tells Bolt to skip verifying the machine certificate it receives from the test box and encrypt the connection anyway.
PS >bolt task run sqlserver::get_sql_logins --nodes $node --no-ssl-verify --user Administrator -p
Please enter your password:
Started on gu213n926ppeudt.delivery.puppetlabs.net...
Finished on gu213n926ppeudt.delivery.puppetlabs.net:
{
"logins": [
{
"Name": "##MS_PolicyEventProcessingLogin##",
"IsDisabled": true,
"IsLocked": false,
"IsPasswordExpired": false,
"CreateDate": "03/25/2019 14:26:32",
"DateLastModified": "03/25/2019 14:26:32"
},
{
"Name": "##MS_PolicyTsqlExecutionLogin##",
"IsDisabled": true,
"IsLocked": false,
"IsPasswordExpired": false,
"CreateDate": "04/30/2016 00:46:49",
"DateLastModified": "03/25/2019 14:26:32"
}
...<snip>...
{
"Name": "sa",
"IsDisabled": false,
"IsLocked": false,
"IsPasswordExpired": false,
"CreateDate": "04/08/2003 09:10:35",
"DateLastModified": "03/25/2019 14:28:32"
}
]
}
Successful on 1 node: gu213n926ppeudt.delivery.puppetlabs.net
Ran on 1 node in 5.80 secondsThere are a number of things to talk about in the output, so I'll stop and talk about them.
The
-pparameter at the end has to be the last parameter. It's how Bolt knows that it needs to ask you to type in a password.These tasks will always return data in
JSONformat, even though I didn't specify the--format jsondirective when I called Bolt. If you don't mind output like you see above, that's ok. If you want to capture the output into a variable and do some querying and filtering we will need to switch to the--format jsonstyle, which we'll see an example of shortly.This summary view of the logins contains only the information that we think most admins will want, most of the time. It's good for quickly determining if a login exists on a server, if it's disabled, if it's a domain login or a server login, all of those kinds of things. What you can't see here though is which sql instance the login belongs to. We think most admins will only run one sql instance at a time on a machine, so that information isn't in the summary view. If you want a more detailed view of the logins on a server you execute the task you can use the
detailedparameter and get more information. Below is an example of the information you will get for a single login using the detailed parameter:
{
"Name": "##MS_PolicyEventProcessingLogin##",
"CreateDate": "04/05/2019 14:22:46",
"DateLastModified": "04/05/2019 14:22:46",
"InstanceName": "GU213N926PPEUDT\\MSSQL2C04DFE8",
"DefaultDatabase": "master",
"DenyWindowsLogin": false,
"HasAccess": true,
"ID": 267,
"IsDisabled": true,
"IsLocked": false,
"IsPasswordExpired": false,
"IsSystemObject": false,
"Language": "us_english",
"LanguageAlias": "English",
"LoginType": 2,
"MustChangePassword": false,
"PasswordExpirationEnabled": false,
"PasswordHashAlgorithm": 2,
"PasswordPolicyEnforced": true,
"SQLSID": "0xE4AFA2CB37E9524E915BE32264BC15CA",
"ADSid": null,
"WindowsLoginAccessType": 99,
"UserData": null,
"State": 2,
"IsDesignMode": false
}Notice that the instance name property shows you that the machine I'm running these tests against has more than one instance running on it. One of them is the default instance, but this login belongs to a named instance. These tasks are all designed to handle named instances on a machine properly.
The great thing though is that since we're running these tasks from PowerShell we don't have to look at this stuff directly. We can use the --format json Bolt parameter with ConvertFrom-JSON to make this stuff much easier to deal with*. Let's run the command like this:
*Sorry PowerShell 2.0 people. You get no ConvertFrom-JSON love.
PS >$credential = Get-Credential -UserName Administrator
PowerShell credential request
Enter your credentials.
Password for user Administrator: ********
PS >$node = ‘winrm://gu213n926ppeudt.delivery.puppetlabs.net’
PS >$logins = bolt task run sqlserver::get_sql_logins detailed=true --format json --nodes $node --no-ssl-verify --user $credential.username --password $credential.GetNetworkCredential().password | ConvertFrom-JSON
PS >$logins.items.result.logins[0]
Name : ##MS_PolicyEventProcessingLogin##
CreateDate : 04/05/2019 14:20:31
DateLastModified : 04/05/2019 14:20:31
InstanceName : GU213N926PPEUDT
DefaultDatabase : master
DenyWindowsLogin : False
HasAccess : True
ID : 266
IsDisabled : True
IsLocked : False
IsPasswordExpired : False
IsSystemObject : False
Language : us_english
LanguageAlias : English
LoginType : 2
MustChangePassword : False
PasswordExpirationEnabled : False
PasswordHashAlgorithm : 2
PasswordPolicyEnforced : True
SQLSID : 0x787467886B571E4BB1CCB20BF614024E
ADSid :
WindowsLoginAccessType : 99
UserData :
State : 2
IsDesignMode : False
PS >$logins.items.result.logins | Format-Table Name, CreateDate, InstanceName, IsDisabled, ADSid
Name CreateDate InstanceName IsDisabled ADSid
---- ---------- ------------ ---------- -----
##MS_PolicyEventProcessingLogin## 04/05/2019 14:20:31 GU213N926PPEUDT True
##MS_PolicyTsqlExecutionLogin## 04/30/2016 00:46:49 GU213N926PPEUDT True
GU213N926PPEUDT\Administrator 04/05/2019 14:20:15 GU213N926PPEUDT False S-1-5-21-1814984220-635458450-2908094300-500
NT AUTHORITY\SYSTEM 04/05/2019 14:20:15 GU213N926PPEUDT False S-1-5-18
NT Service\MSSQLSERVER 04/05/2019 14:20:15 GU213N926PPEUDT False S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003
NT SERVICE\SQLSERVERAGENT 04/05/2019 14:20:16 GU213N926PPEUDT False S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430
NT SERVICE\SQLTELEMETRY 04/05/2019 14:20:16 GU213N926PPEUDT False S-1-5-80-2652535364-2169709536-2857650723-2622804123-1107741775
NT SERVICE\SQLWriter 04/05/2019 14:20:15 GU213N926PPEUDT False S-1-5-80-1402415987-66678372-3059512406-1823130485-2345841878
NT SERVICE\Winmgmt 04/05/2019 14:20:15 GU213N926PPEUDT False S-1-5-80-3750560858-172214265-3889451188-1914796615-4100997547
sa 04/08/2003 09:10:35 GU213N926PPEUDT False
##MS_PolicyEventProcessingLogin## 04/05/2019 14:22:46 GU213N926PPEUDT\MSSQL2C04DFE8 True
##MS_PolicyTsqlExecutionLogin## 04/30/2016 00:46:49 GU213N926PPEUDT\MSSQL2C04DFE8 True
GU213N926PPEUDT\Administrator 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-21-1814984220-635458450-2908094300-500
GU213N926PPEUDT\Extra SQLAdmin 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-21-1814984220-635458450-2908094300-1003
NT AUTHORITY\SYSTEM 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-18
NT Service\MSSQL$MSSQL2C04DFE8 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-80-3713986648-510256543-3554943493-2435044684-3233837257
NT SERVICE\SQLAgent$MSSQL2C04DFE8 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-80-2012735109-2806521570-2328710919-1260359127-2424513225
NT SERVICE\SQLTELEMETRY$MSSQL2C04DFE8 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-80-3012728466-2186142863-1999894670-3077041396-2403349152
NT SERVICE\SQLWriter 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-80-1402415987-66678372-3059512406-1823130485-2345841878
NT SERVICE\Winmgmt 04/05/2019 14:22:31 GU213N926PPEUDT\MSSQL2C04DFE8 False S-1-5-80-3750560858-172214265-3889451188-1914796615-4100997547
sa 04/08/2003 09:10:35 GU213N926PPEUDT\MSSQL2C04DFE8 FalseThe really cool thing that these examples above show us is that by returning JSON, we can use PowerShell to convert the JSON into objects!
All of the examples for this task so far have been running it with no parameters to get all of the logins on a server. Go back and look at the parameters it takes and you will see that you can also specify login names to search for, either using fuzzy matches, or exact names. You can also specify the instance name to connect to, so you don't get all of them. This way if you are using this data to take actions further on, or to inspect the results visually, you can cut down on the clutter.
sqlserver::set_sql_logins
Since we just got done using the get_sql_logins task as an example of some of the ways to use the tasks on the commandline, the rest of these will go a little faster.
Notice that in the output above, the sa login is still enabled. It's common to have corporate policies that it should be disabled, so let's take a look at how to do that. The first new thing we'll see in this task is using the --noop (No Op(eration)) parameter:
PS >$logins = bolt task run sqlserver::set_sql_logins login_name=sa enabled=false --noop --format json --nodes $node --no-ssl-verify --user $credential.username --password $credential.GetNetworkCredential().password | ConvertFrom-JSON
PS >$logins.items.result.changes | Select-object login, noop, properties_set
login noop properties_set
----- ---- --------------
sa {@{value=true; property_name=IsDisabled}} {}What we see is that in the objects we get back there is a set of changes that refers to a login that was matched to be modified. That object will have a set of noop changes, which are changes that would have gone through if this was a real run, and a set of properties_set which are things that were actually manipulated. If we run this again without the --noop parameter, and also change the login password we get this:
PS >$logins = bolt task run sqlserver::set_sql_logins login_name=sa enabled=false password=hypotheticalPassword --format json --nodes $node --no-ssl-verify --user $credential.username --password $credential.GetNetworkCredential().password | ConvertFrom-JSON
PS >$logins.items.result.changes | Select-object login, noop, properties_set
login noop properties_set
----- ---- --------------
sa {} {@{value=true; property_name=IsDisabled}, @{value=**********; property_name=password}}Now the change set is in the properties_set property, and we see that the account has been disabled, and that what we have changed is password.
sqlserver::get_sqlagent_jobs
For this task we can be even shorter here in this post, and I'll just give you an example of the kind of data that it returns about jobs and encourage you to play with this one on your own. The thing to note about this return data is that it returns most things you might reasonably want to know about an agent job including the names and properties of the steps in the job, but no data from the agent logs as that would be too much.
{
"items": [
{
"node": "winrm://gu213n926ppeudt.delivery.puppetlabs.net",
"status": "success",
"result": {
"jobs": [
{
"enabled": true,
"lastRunDate": "0001-01-01T00:00:00",
"steps": [
{
"lastRunRetries": 0,
"databaseName": "master",
"retryAttempts": 0,
"onSuccessStep": 0,
"name": "Verify that automation is enabled.",
"type": "TransactSql",
"lastRunOutcome": "Failed",
"retryIntervalMinutes": 0,
"onFailStep": 0,
"lastRunDate": "0001-01-01T00:00:00",
"lastRunDurationSeconds": 0,
"onSuccessAction": "GoToNextStep",
"onFailAction": "QuitWithSuccess"
},
{
"lastRunRetries": 0,
"databaseName": "master",
"retryAttempts": 0,
"onSuccessStep": 0,
"name": "Purge history.",
"type": "TransactSql",
"lastRunOutcome": "Failed",
"retryIntervalMinutes": 0,
"onFailStep": 0,
"lastRunDate": "0001-01-01T00:00:00",
"lastRunDurationSeconds": 0,
"onSuccessAction": "GoToNextStep",
"onFailAction": "QuitWithFailure"
},
{
"lastRunRetries": 0,
"databaseName": "master",
"retryAttempts": 0,
"onSuccessStep": 0,
"name": "Erase Phantom System Health Records.",
"type": "PowerShell",
"lastRunOutcome": "Failed",
"retryIntervalMinutes": 0,
"onFailStep": 0,
"lastRunDate": "0001-01-01T00:00:00",
"lastRunDurationSeconds": 0,
"onSuccessAction": "QuitWithSuccess",
"onFailAction": "QuitWithFailure"
}
],
"startStepID": 1,
"operatorToPage": "",
"instance": "GU213N926PPEUDT",
"description": "No description available.",
"ownerLoginName": "sa",
"lastRunOutcome": "Unknown",
"nextRunDate": "2019-04-06T02:00:00",
"dateLastModified": "2019-04-05T14:20:16",
"currentRunRetryAttempt": 0,
"operatorToNetSend": "",
"dateCreated": "2019-04-05T14:20:16",
"currentRunStep": "0 (unknown)",
"name": "syspolicy_purge_history",
"emailLevel": 0,
"operatorToEmail": "",
"category": "[Uncategorized (Local)]",
"currentRunStatus": "Idle"
}
...<snip>...
]
}
}
],
"node_count": 1,
"elapsed_time": 6
}Keep in mind that many of those times like lastRunDate are effectively zero because this is a test machine and I haven't kicked those jobs off yet.
sqlserver::start_sql_agent_job
This task will kick off an agent job from whatever job step you specify, or the first step if you don't use the step parameter. A few things to keep in mind about this one:
If you don't specify an instance name it will use the default instance. This is for convenience to avoid typing it out when that's what most people will want most of the time.
The job step numbers are a zero-based array. So to start a job at step 5 you will need to pass the number 4 to the step parameter. This is because the programming objects we use on the back end are zero-based arrays, and it's best not to try to be too slick with the user input and cause weird things to happen.
A lot of agent jobs run long, which is why the default is to kick off the job asynchronously and return data to the console. The data you get back should indicate the job is running, but servers can take a highly inconsistent amount of time to start or finish jobs. If the data you get back doesn't say something like
currentRunStatus=running, it could be that the server didn't finish initializing the job before the script completed, or that it spun up and finished, all before the script was done. Inspect thelastRunDateproperty on each step looking for differences to indicate one step was started and another has not yet, or runsqlserver::get_sqlagent_jobagain to get the jobs’ latest status information.
Convert your PowerShell Script into a Task
If you have your own PowerShell script that you run against SQL Servers, it’s easy to convert that script into a Bolt Task. As a reminder the PowerShell with Bolt blog post has instructions for how to convert any PowerShell script into a task, but especially in the case of writing PowerShell for SQL Server, you can use some of the code we have written in your own scripts.
When implementing these tasks, I found that there was a significant amount of duplicated code between them. It makes sense that there would be since there are a number of things all four of them need to be able to do. They need to be able to find the instances of sql running on a node, they need to select the right instance to connect to, get a connection, etc.
In PowerShell it's common to take those kinds of common tasks, extract them into functions, and then to place the function definitions into a separate file that is then dot-sourced into your script.
To see how we've done this we can look at the task metadata for the file that contains all of the shared functions. The get_sql_logins.json file has a line for importing a file that exists in the SQL Server module.
"files": ["sqlserver/files/shared_task_functions.ps1"]Notice that the file path starts with the name of the module, but does not include the namespace; just the module name. If you take a look at the code in shared_task_functions.ps1, it contains a series of functions, but no code that uses them. This file's only purpose is to define functions for other files to use.
Each of the task files then imports that file's functions using a section of code like this one in get_sql_logins.ps1. Once it's imported the shared functions, it can use them to do things like find all of the sql instances running on a node ensuring you only get the instances you asked it to connect to via the $instance_name parameter.
One thing you might notice about some of the functions is that some of them have strict appended to their name. That's because the functions without strict in the name will match loosely, meaning it will return as many matches as possible, even partial name matches. The strict ones are designed to only return exact name matches to prevent accidental matches. Usually, you will use the looser function type for tasks that only retrieve information, and the strict kind for tasks that could modify information and you want to force the user to be more specific about what they want to modify.
The end result is that if you want to write your own Bolt tasks that can connect to SQL Server and retrieve information, all you have to do is include that file as shown, expose an $instance_name parameter (or whatever you want to call it), and you don't have to re-write that code for yourself!
PowerShell MS SQL Server Module Examples: Next Steps
In this post, we have talked about the new Bolt tasks in the Puppet SQL Server module. You should have a good idea now of what they are capable of and how to use them. I hope you are not only inspired to use them but that you have an idea of some of the things that Bolt can help you with when managing your SQL Servers.
We also talked about some of the internals of how these tasks were implemented, and how using a shared code file could help you in the future if you want to write your own custom tasks that need to do some of the same things that these tasks are doing already.
Need Help? Puppet Experts Are Here!
Learn More
- Learn more about Puppet and PowerShell DSC integrations
