SQL Server Reference Guide

From time to time you may need to run a command from SQL Server on the operating system. There are various ways to do this, and most all of them are insecure. The cmdexec process allows you to have access to the command line in the operating system, but there are some fairly significant risks with using this feature. In some cases, security best practices restrict you from using it at all.

If you do need to run a command on the operating system, one of the more secure and most flexible tools you have is PowerShell. Starting in SQL Server 2008, the platform now has the ability to work with the PowerShell system in Windows. In fact, SQL Server 2008 even includes a special PowerShell provider.

PowerShell is a scripting and command environment based on the .NET framework from Microsoft, which means you get access to everything on the system using batch-level coding through more sophisticated programming constructs. It's fairly simple to learn the basics, and allows you to move quickly to more advanced concepts. In short, you can use it to do anything you would have done with COMMAND.COM or CMD.EXE, and much more. If you're new to PowerShell, I have a series of articles starting here that start with the basics and move on to more sophisticated concepts.

Normally if you're sitting at the console of a SQL Server, you don't need to call operating system commands from within SQL Server - you can simply open a command window and type your commands there. It's more often the case that you need to automate a process in SQL Server that also needs to perform some action on the operating system. For instance, in one shop I worked at the process called for taking a backup to a local disk, and then copying that file to another location on the network so that the standard backup procedures at that location would pick up the file for offsite storage. That meant that the DBA had to code up the logic to not only do the backup within SQL Server, but also the file transfer command within the operating system. to be sure, there are a lot of other ways to handle this - they could have used the SQLCMD command in a batch file to fire off the backups and so on, but the DBA wanted to do everything inside SQL Server, and be able to maintain history and be alerted through the standard SQL Server mechanisms.

SQL Server comes with an automation system called the SQL Agent. This subsystem can run "Jobs" which are collections of "Steps" on a server, maintain a history of those results, send e-mails (if properly configured) and even has basic flow logic. It runs within SQL Server, and is integrated both graphically and in commands. See this article if you're not familiar with SQL Agent: http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=240

Added in SQL Server 2008, you're able to run PowerShell as a SQL Agent Step. This means in a single SQL Agent Job you can run Transact-SQL, PowerShell and other commands one after another. This suited the needs of the client perfectly, so this is the process we used for them. In this tutorial, I'll show you a simple PowerShell step within the SQL Agent system, and show you the steps you need to follow to set it up. You're welcomed to follow along, but do this on a testing system only until you're familiar with how it works - don't go straight to production with these steps. 

PowerShell has a great deal of security built right in, but even so you need to be aware of what it can and cannot do on your system. If you do not own the security for the Microsoft Windows operating system where you will run this process, make sure you coordinate with the person or team that does. Read the article I mentioned earlier on PowerShell to set the scripting permissions according to what you need to do.

Like any set of automated steps, it's important to try the individual commands before you put them into the automation. I constantly see people troubleshoot a script - only to find that they haven't checked each individual step to begin with. So before you create your automation, make sure each line runs one after another at a PowerShell prompt first.

I'm going to run two commands in this example - one which finds the free space on devices and lists that in order, and the other which performs a simple directory listing. I'll pipe both of these to a file on my c:\temp directory on the hard drive. The reason I picked these mostly uninteresting commands is to demonstrate the Windows Management Instrumentation (WMI) calls that can show or do most anything on a Windows system (and thus need a certain level of security), a standard DOS command, and access to a file location that SQL Server doesn't always have. Even this trivial example should be useful enough for you to expand to something you need to do.

Try these out on a PowerShell prompt on your test system:

  Get-WMIObject Win32_LogicalDisk | sort-object -Property freespace  -desc | select-object DeviceID > c:\temp\dir.txt

And

  DIR >> C:\TEMP\DIR.TXT

(Of course, this assumes you have a c:\temp on your system. If not, point that to whatever you do have.)

The first thing you need to ensure is that you have a Windows user that is capable of running these commands. If you're in charge of the security on the Windows system that has SQL Server installed, then ensure that the user you want to use for this process has only enough rights in Windows that it needs to do the work you want - if you aren't, then coordinate that with the systems administrator. Write that information down.

Even though SQL Agent has a full logging system built in (and you should use it) make sure that the process includes logging at the operating system level. Some processes make calls to yet other processes, and SQL Agent might not pick these up. This is another reason PowerShell is so useful in this situation - it has a built-in connection you can use to the Windows Event Logs.

With all of that set up, here is a simple process you can follow to automate operating system tasks with SQL Agent and PowerShell:

1. Create Credentials in SQL Server
2. Create a Proxy Account to use the Credentials
3. Set up the Job and Steps
4. Schedule the Job or run it manually

Let's take a look at each of these steps in an example.

Create the Credential

When you create a Job in the SQL Agent system, it runs under the account you selected for the Windows Service Account you picked when you installed or last configured your system. You can read more about that here.

This Windows account might have all of the permissions and rights you need to accomplish your task. But if you're following security practices, then the SQL Agent Service Account should not be an administrator on the Windows System, nor should it have rights that span lots of operations. It should be as minimal as possible. I tend to make accounts on the Windows Side that have the particular permissions I need to accomplish the task.

To avoid having to create those accounts in SQL Server, and to be able to use these accounts within SQL Server as I go, I use Credentials. These are simply links to accounts in Windows, without having to set that Windows user up in your SQL Server. You can even use Certificates instead of a user account, so that you don't have to use a Windows Account here — but that's another article.

To set up a Credential, you can use either the graphical tools or Transact-SQL Commands. For the graphical tools, open SQL Server Management Studio (SSMS) and navigate to the Security item in the Object Explorer. Expand that, and right-click the Credentials item. Select New Credential... from the menu that appears.

Figure 1 (Click to Enlarge)

Give the Credential a name you want to use — keep this short but descriptive - and fill in the Windows Domain (or machine name), User Name, and password for that user.

Figure 2 (Click to Enlarge)

You can also do this as a Transact-SQL Command:

USE [master]
GO
CREATE CREDENTIAL [ElevatedUser] WITH IDENTITY = N'UNIVAC\SQLAdmin', SECRET = N'StrongPasswordHere'
GO

Now you have a Credential tied out to that Windows Account. You can use this not only in SQL Agent, but other places as well.

Create the Proxy Account

The Credential you created isn't linked automatically to the SQL Agent system. In fact, you don't want it to be - because you need more control than that. SQL Agent by default uses the Service Account I mentioned a moment ago. You can control the account for not just the entire SQL Agent Job, but each individual Step in the Job. That gives you an amazing amount of flexibility. To do that, you need to create a "Proxy" (stand in) account for the type of Steps SQL Agent can run.

Once again in SSMS, navigate to the SQL Server Agent item, then expand the Proxies item and right-click the PowerShell item. From the menu that appears, select New Proxy...

Figure 3 (Click to Enlarge)

From there, fill out the name you want for the Proxy. I normally name mine with a descriptive label that tells the highest level of right the Proxy has. There is a description field here, but I like the name to have that info as well.

After you fill out a name, type or select the name of the Credential that this Proxy will map to. Then select the type of Steps you want for this Proxy — I've checked PowerShell in this example. Although it can lead to more steps, I recommend that you follow this process for each kind of step rather than using the same account for multiple places.

Figure 4 (Click to Enlarge)

Then select the Principals tab and select that the SQL Agent account should be able to use this Proxy.

Figure 5 (Click to Enlarge)

If you want to use T-SQL to create this Proxy, here's what I used:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'PowerShellWMIProxy',@credential_name=N'ElevatedUser', 
		@enabled=1, 
		@description=N'Can run WMI queries in Windows'
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'PowerShellWMIProxy', @subsystem_id=12
GO
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'PowerShellWMIProxy', @login_name=N'NT SERVICE\SQLSERVERAGENT'
GO

Now all that's left is to create the Job and the Steps.

Create the SQL Agent Job

Back in SSMS, navigate to the SQL Server Agent item and then right-click the Jobs item. Then select New Job... from the menu that appears.

Figure 6 (Click to Enlarge)

Name the job, and categorize it if you like. Optionally, you can add a description.

Figure 7 (Click to Enlarge)

Now click the Steps text on the left and change to that panel. Click the New... button at the bottom.

Figure 8 (Click to Enlarge)

Give the step a name, and then change it to PowerShell for the type. Notice that you can now change the Run as: box to the Proxy account name you created earlier.

Finally, add the commands from before (or any that you have tested and want to try) in order:

Get-WMIObject Win32_LogicalDisk | sort-object -Property freespace -desc | select-object DeviceID > c:\temp\dir.txt
DIR >> C:\TEMP\DIR.TXT

Schedule the SQL Agent Job or Run it Manually

It's at this point that you can set up a schedule for the Job. I'm not going to show those panels, or set up a schedule here — I'll run this job manually in a moment.

If you do want to set up a schedule, you can check my article on the SQL Agent to see how — it's no different for this process.

With everything set up, I'll show you the code if you would rather use T-SQL than the graphical tools:

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'TestJob', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'Test Job for PowerShell', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'UNIVAC\SQLAdmin', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'TestJob', @server_name = N'UNIVAC'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestJob', @step_name=N'TestStep', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'Get-WMIObject Win32_LogicalDisk | sort-object -Property freespace -desc | select-object DeviceID > c:\temp\dir.txt
DIR >> C:\TEMP\DIR.TXT', 
		@database_name=N'master', 
		@flags=0, 
		@proxy_name=N'PowerShellWMIProxy'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'TestJob', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'Test Job for PowerShell', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'UNIVAC\SQLAdmin', 
		@notify_email_operator_name=N'', 
		@notify_netsend_operator_name=N'', 
		@notify_page_operator_name=N''
GO

And now to run the process, it's simply a matter of issuing the command to run a SQL Agent job - which you can do from T-SQL or even ironically enough in PowerShell. Here's the T-SQL code to run that SQL Agent Job:

USE msdb ;
GO

EXEC dbo.sp_start_job N'TestJob' ;
GO

Now you can check the results of your Job execution. The simplest way is to check the output you created, but you can also right-click the SQL Agent Job and select History from the menu that appears. You should always do this, and make sure that you don't just schedule the job and forget it. Always test to ensure that you got the result you were expecting.