Ozzie.eu

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.

On the same subject as this post, this editorial from SQLServer Central came out.

Happy coding!

Absent fellow DBAs, be jealous :-)

Get a free Office 365 developer account

Microsoft is giving away a one year FREE subscription for an Office 365 Development
instance. Sign up and you’ll receive the link via email to setup
your subscription.

The Office 365 Developer Program is a vibrant and growing community of
developers, veteran and newbie, who are developing solutions on Office
365. 

You can go to the sign up form from this page.