Security Concern: Alternative to extended stored procedures on Microsoft SQL Server

While securing a Microsoft SQL Server instance, there are many issues you should look after. According to the official documentation, it can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. On environments where SQL Server is used on a more “Swiss Army Knife” fashion, there is often difficulty on disabling or, at least, restricting access to the general extended stored procedures, like xp_cmdshell. These stored procedures provide an interface from an instance of SQL Server to external programs for various maintenance activities. But they also represent a security liability. The alternative suggested is if possible to use SQL CLR Integration to perform these tasks. It can be done and it works, but maybe it isn’t a desired task by the regular DBA to learn to program a .NET Framework language.
With this in mind, as far as SQL Server Agent jobs are concerned, I’d suggest creating a Powershell job step:

Bear in mind this requires a specific kind of error handling. By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors but the command produces an error, the SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to set your $ErrorActionPreference = "Stop". You can check that out here.
For the processing task which are a hybrid of data manipulation and other external system related tasks I’d suggest the development of Powershell scripts that use SQL Server Management Objects (SMO) which is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
Sure, you might say that this suggestion doesn’t help from learning to code, but this is an inevitable goal every SQL Server DBA must have as the future might have projects where the projects might have a server core platform. What then?
So why not kill two rabbits with one stone and learn your Powershell and SMO, it quite seems  the way to go. For starters, this might help.

By coincidence, as I'm writing this post, this editorial from SQLServer Central came out. What are the odds of that?

Happy coding!


Photo Credit: baboon™ via Compfight cc