Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DTS Hashing

By Alex Kersha,

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.

  1. Create a DTS package or just pick any existing one in Enterprise Manager
  2. RIGHT-CLICK on the package and choose SCHEDULE PACKAGE
  3. When the schedule property sheet pops up, just click ok
  4. Browse to the SQL jobs and make sure you hit REFRESH to show most recent additions
  5. 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

Total article views: 5782 | Views in the last 30 days: 0
 
Related Articles
FORUM

Linked Server Security

Linked Server Security Question

FORUM

Running a DTS or SSIS package from a windows command line

DTS package, SSIS package, command line

FORUM

SQL Server syntax error

SQL Server syntax error

FORUM

SQL Server Agent Job - DTSRun Syntax Error

Invalid command options

FORUM

SSIS Package Security

package encryption

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones