The Security Problem
I've often come across implementations where security is lauded and woe be the DBA that doesn't take it seriously.
Then you start digging around and find unneeded ports open, user and group access to objects that shouldn't exist
etc. Perhaps a less obvious infraction of a good security policy is the use of DTS packages from within job steps.
In general, the basic syntax for executing a DTS package from the command line looks like this:
dtsrun /N[name of package] /S[name of server] /U[user name] /P[password] .. There are a few other options.
You can find them in Books OnLine or type dtsrun /? to get the options.
So, here's the problem: if you include this in your SQL job step, you've just told everyone what your username AND password are for accessing the server.
An alternative method of running the DTS package (and there are several) from a job step is to use a hashed version of the syntax. Getting this done is fairly straight forward.
It'll cost you a few mouse clicks and about 30 seconds of your time.
The Hashed DTS Package
I've tried to break this down step by step using SQL Server 2000 and Windows 2000 Adv server as the platform.
- Create a DTS package or just pick any existing one in Enterprise Manager
- RIGHT-CLICK on the package and choose SCHEDULE PACKAGE
- When the schedule property sheet pops up, just click ok
- Browse to the SQL jobs and make sure you hit REFRESH to show most recent additions
- You should find your newly created DTS package in the list as a scheduled job
You can run the job as is and the package will execute. More importantly though, if you take a look at the job step directly you'll notice that the command line syntax
looks something like this:
DTSRun /~Z0x6A72924508642712F9ED5B6C3CDFF7214EEC2DCD730813AB0E1ED9C9D71635DB66DA2C2433D6D0EF02879FE60918C33FE86E9E91A ...
This is your hashed execution syntax. The easiest way to incorporate this into any existing SQL job is to just copy out the command and paste it into a new job step. Make sure you choose OPERATING SYSTEM COMMAND as the step type.
That's it, you're all set!
Conclusions
SQL Server security should be one of the top most priorities on every DBA's mind. It's easy to overlook some basic things though so do some digging and try to find the holes in your implementations. There are professional
groups out there available to help with this task but hopefully with this article I've given you one more way to help bulletproof your server before it's taken advantage of.
Alex Kersha
Database Administrator
PeopleBusinessNetwork
