SQL Server 2000 SP3 and xp_cmdshell Woes

The problems caused by the SQL Sapphire Worm, also know as
the SQL Slammer, have caused many sites to do a quick upgrade to SQL Server
2000 Service Pack 3 (SP3). It includes the fix that prevents infection by the
worm. While moving to the latest service pack is usually a good thing, to do
so without thorough testing risks breaking a working application. That is
exactly what happened to one of my clients over the weekend.

Although not infected, the DBA for this site decided to do a
Service Pack upgrade instead of applying the relevant hotfixes. I understand
his dilemma. The original hotfix (MS02-039) that closed the vulnerability that
SQL Sapphire exploits does not include an installer and that makes many DBAs
and programmers, myself include, reluctant to apply it. That bulletin has been
revised and the new one can be found at: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS02-061.asp.
Additional information about the worm and tools that can be used to address
security can be found at the SQL Server site: http://www.microsoft.com/sql.

Installing a service pack is usually pretty safe. Nevertheless,
each service pack makes enough changes that one or two features of the
applications that I work with break. Regression testing is the price of
progress. Not paying the price and skipping the regression test carries the
risk that your application will not work after the upgrade. In the case of my
client’s system, two features just stopped working.

The features that stopped working depend on using the
xp_cmdshell extended stored procedure to initiate external programs. My
suspicion was that the cause is a change in the way that authorization for
using xp_cmdshell worked after the service pack was applied. I was pretty

By default, xp_cmdshell can only be used by logins in the
sysadmin server role. Usually this is just the DBAs for the site. It is
possible to grant the right to use xp_cmdshell to other users. Some
applications depend on xp_cmdshell to initiate external code from within SQL
Server. It’s often used to kick off a DTS package using the DTSRUN command
line utility.

When a login that’s in the sysadmin role executes
xp_cmdshell, it runs under the windows account that SQL Server is running
under. When you grant the right to run xp_cmdshell to a login that is not in
the sysadmin role, you must set the account that is used to run xp_cmdshell and
any programs that it invokes. This is done with the extended stored procedure

Suppose that you want to grant the right to execute
xp_cmdshell to the SQL login LimitedUser. You’ll need an NT account to execute
the program. Here’s the script:

use master

xp_sqlagent_proxy_account N’SET’
, N'<mydomain>’
, N'<ntuser>’
, N'<ntuser’s password>’

— retrieve the proxy account to check that it’s correct.
xp_sqlagent_proxy_account N’GET’

— grant database access in master
sp_grantdbaccess ‘LimitedUser’

grant exec on xp_cmdshell to LimitedUser

You will have to put in your information for <mydomain>,
<ntuser>, and <ntuser’s password> before you run the script.
To test that it worked, log into SQL Query Analyzer as LimitedUser and execute
the script:

exec master..xp_cmdshell ‘echo abcd’
(End of results)

The script can be executed from any database. The output
should look like the results shown.

To verify that authorization of xp_cmdshell was the problem,
I logged in as the user that was experiencing the problem and tried the previous
script. What I got was:

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 997 from GetProxyAccount on line 604

I checked that the proxy account was set up correctly with
this script:

— retrieve the proxy account to check that it’s correct.
xp_sqlagent_proxy_account N’GET’

It looked correct. It was exactly the way it had been set a
few months before.

When a service pack has been out for a couple of weeks, the
best place to get the latest news on problems and issues that other people have
experienced is on the relevant USENET newsgroups. In this case, I turned to
microsoft.public.sqlserver.programming and searched for xp_cmdshell. That
pointed me in the right direction.

A couple of people had already experienced a similar problem
and I was referred to the Books Online article titled "How to reset
SQLAgent permissions (Enterprise Manager)". By the way, with Service Pack
3 there is a revised set of Books Online that you can retrieve here: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

The BOL instructs you to go into Enterprise Manager and
bring up the property page for SQL Agent. Click on the "Job System"
tab. In the Non-SysAdmin
job step proxy account area
of the page, clear the check box Only users with SysAdmin
privileges can execute CmdExec and ActiveScripting job steps
Figure 1 shows the screen before the check box is reset.

Figure 1 SQL Agent Properties tab Job System

Once you clear the check box, a dialog box pops up to allow
you to set the SQL Agent proxy account. The dialog box is shown in Figure 2.

Figure 2 Setting the SQL Agent proxy account

This gets filled in with the same information that goes into
the xp_sqlagent_proxy_account procedure. In fact, Enterprise manager executes
xp_sqlagent_proxy_account when you press the OK button.

This flag can also be cleared using an undocumented system
stored procedure that is located in msdb. It’s called sp_set_sqlagent_properties
and Enterprise Manager uses it to set the properties on the SQL Agent property
pages. To clear the Only
users with SysAdmin privileges can execute CmdExec and ActiveScripting job
field use this script:

–Corresponds to the Enterprise Manager SQL Agent property page
— Job System tab. Sets the value of “Only users with SysAdmin
— privileges can execute CmdExec and ActiveScripting job steps”
— 1 Turns on the restriction
— 0 Turns off the restriction and allows non sysadmin logins
— to do this and to run xp_cmdshell
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0

Either the Enterprise Manager or the script is sufficient to
make the change.

After clearing the flag and resetting the account,
xp_cmdshell started working for the non-sysadmin users. I then turned my
attention to being sure that all the features that depended on xp_cmdshell
really worked.

To do that I had to find all of the places where xp_cmdshell
was used. I thought of using the system stored procedure sp_depends but it does
not show references to objects outside of the current database. xp_cmdshell
and all extended stored procedures are objects in the master database, so sp_depends
wouldn’t help.

Instead, I decided use a user-defined function (UDF) that I
have, to search syscomments for all references to a character string. The UDF, udf_SQL_SearchDBObjectsTAB,
returns a table of object names, listed by object type, that contain a
particular string. While searching for the string ‘xp_cmdshell’ doesn’t
guarantee that the object (stored procedure or function) executes xp_cmdshell,
it narrows the search down to just a few objects that can easily be checked.

I’ve made udf_SQL_SearchDBObjectsTAB the subject of
next week’s issue of my T‑SQL UDF of
the Week
newsletter. By the time you read this it should be posted on the
Archive page. You can find it at: http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm.
If you like it, sign up for the newsletter, it’s free.

After checking all of the references to xp_cmdshell, the
application was 100 percent back in business and everyone was happy. The
interruption was only to a few features and only for a couple of hours, so it will
be forgotten. Of course, with a little more testing, the interruption could
have been avoided.

The bottom line seems to be that Service Pack 3 changes the
behavior of SQL Server by making the permission for non-sysadmin accounts to
use xp_cmdshell conditional on the value of the Only users with SysAdmin privileges can
execute CmdExec and ActiveScripting job steps
flag. It’s a subtle change
that makes SQL Server a bit more secure.

I hope you find this article useful. Applying Service Pack
3 to a production system has risks and this time there was a small price to
pay. Fixing this problem on a live system got my adrenalin pumping a bit.
Moreover, what I found was that the resources to figure out what was happening
are easily available in the form of newsgroup postings, MSDN knowledge base
articles, and the Books Online.


See All Articles by Columnist
Andrew Novick

Andrew Novick
Andrew Novick
Andrew Novick develops applications as consultant, project manager, trainer, author, and Principal of Novick Software. His firm specializes in implementing solutions using the Microsoft tool set: SQL Server, ASP, ASP.Net, Visual Basic and VB.Net. 2003 marks his 32nd year of computer programming, starting in High School with a PDP-8 and moving on a degree in Computer Science, an MBA, and then programming mainframes, minicomputers and for the last 16 years PCs. In addition to writing articles for Database Journal, Andrew is author of the book SQL Server User Defined functions, which will be published by Wordware in the fall of 2003. He co-authored SQL Server 2000 XML Distilled, published by Curlingstone in October of 2002. He also writes the free T-SQL User-Defined Function of the Week newsletter. When not programming he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife.

Latest Articles