How do I secure a SQL Server extended stored procedure?

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
Extended stored procedures in SQL Server 2000 were the only option to extend the capabilities of the relational engine such as access to the file system or to send and receive emails directly in SQL Server.  Securing these objects is not straight forward so few organizations have done so leaving SQL Server 2000 in an insecure state.

Solution

Step 1 - Extended Stored Procedures Backgrounder

Extended stored procedures are objects that are stored in the master database of the SQL Server 2000 instance.  These objects can be accessed via T-SQL commands that under the covers call *.dll's.  Some of the most used extended stored procedures are xp_cmdshell (file system commands) and xp_sendmail (email directly from SQL Server), but it is possible to build custom extended stored procedures to meet your business needs. 

Step 2 - Security Requirements

Gather the security requirements for your environment as they pertain to using extended stored procedures in your applications or to complete operational needs.  Then determine who should have access to which extended stored procedures and why. 

Keep in mind that by default particular extended stored procedures are only accessible by the sysadmin fixed server role or the db_owner fixed database role.

Step 3 - Analyze

Next, review the execute permissions on the extended stored procedures which can be accomplished by the following steps:

1. Open Enterprise Manager
2. Once Enterprise Manager loads, navigate to SQL Server root | Databases | Master | Extended Stored Procedures
3. Review the list of extended stored procedures and locate the extended stored procedure of choice
4. Right click on the extended stored procedure and select the 'Properties' option
5. Once the 'Properties' window loads, click the 'Permissions' button on the top right of the interface
6. Review and document the extended stored procedure permissions granted to the database users as shown below

Step 4 - Modify the Security

Rather than granting rights directly to users to execute the extended stored procedures, the recommendation would be to build stored procedures that call the extended stored procedures with the needed logic.  Then the users can be granted execute permissions on the user defined stored procedure.

To modify the users with rights to the extended stored procedures, follow the steps from step 3 above and remove the 'EXEC' permissions.

A final option, sometimes considered drastic, is to drop the extended stored procedures.  Although, from a security perspective this may be appropriate for some environments. 

Step 5 - SQL Server 2005

Consider upgrading to SQL Server 2005 and using the CLR (Common Language Runtime) which has the ability to build assemblies in SQL Server 2005 which have the functionality to extend the relational engine's capabilities.  The assemblies can then have execute and deny permissions granted to users similar to other relational objects. 

Another SQL Server 2005 advancement is the ability to use the Surface Area Configuration tool to deny access to particular functionality such as the xp_cmdshell extended stored procedure.  With SQL Server 2005, Microsoft has taken a step toward not allowing functionality until it has been explicitly granted.

Next Steps

• Review the extended stored procedure security in your environment.
• Determine if the extended stored procedure security is tight enough or if it requires improvements.
• Document the security configuration and then determine if a plan needs to be built to improve the SQL Server security.
• Check out the recent security tips from MSSQLTips.com.

Related Tips: More | Become a paid author

Last Update: 9/14/2006

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" SQL Monitor – For database professionals who need results on Day One. Try it online. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST