Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp

 

 

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.

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 close.

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 xp_sqlagent_proxy_account.

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
go

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

-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go

-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go

grant exec on xp_cmdshell to LimitedUser
go

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'
go
(Results)
--------------------------------------
abcd
null
(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'
go

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 from the SQL Server site or the MSDN downloads.

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.

sql server agent properties

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.

sql agent proxy account login

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 steps 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
go

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 Vol 1 Issue #13 of my T‑SQL UDF of the Week newsletter. By the time you read this it should be posted on the Archive page. 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.


RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule