x

removal of XP's

Hi everyone. What effect does the removal of extended stored proceedures have ? Part of a security review been asked if we can remove some XP's. Examples xp_instance_regaddmultistring , xp_instance_regdeletekey , xp_regaddmultistring. As an example. In total there are about 30.

If the application does not use them , are they needed everytime SQL restarts etc.

Need to understand what issues removal will have ?

many thanks
more ▼

asked May 04, 2011 at 01:14 AM in Default

mickyd gravatar image

mickyd
162 11 11 11

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

What are the actual reasons for the suggestion that they are removed? If there is another way to mitigate the risk that this audit claims then I would go for that first - as @Kev Riley says, changing the security would be the first place to look - actually removing the procedures would be well down the list. You could alter them so the have no effect before actually dropping them from your server.

Keep in mind that having the ability to execute these procedures from within SQL Server doesnt mean you can necessarily affect the registry, that still requires the necessary permissions in the server OS.

For the permissions changes check out http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=56104
more ▼

answered May 04, 2011 at 01:37 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

Thanks for reply. been advised by pen testers they present a security issue. Even though only owned by sys and no associated permissions present.
May 04, 2011 at 02:27 AM mickyd
This could get political within your work but I'd contest that a little. So long as the permissions are right then there is no appreciable risk that I am aware of. Can they give you an example, explain further? Can they reference any white papers or best practice that supports their point of view?
May 04, 2011 at 02:36 AM Fatherjack ♦♦
Agree with @fatherjack - there are lots of 'practice and patterns' type stuff available from Microsoft about securing SQL Server. I would prefer to follow their recommendations about their product, rather than a 3rd party without validated references.
May 04, 2011 at 04:07 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Removal of some of the extended stored procedures, especially those that relate to the registry can result in a service pack or cumulative update install breaking. Not exactly a good thing, especially when you call Microsoft, you'll find you've rendered your SQL Server into an unsupported state.

Also, the removal of the extended stored procedures is not an effective control (tell your auditors and security personnel this). The reason it's not is that removing the extended stored procedures doesn't remove the vulnerability. The .DLL is still present and in most cases can't be removed because that .DLL provides other extended stored procedures you can't remove. So long as the .DLL remains, anyone with sysadmin membership or CONTROL SERVER permissions (SQL Server 2005 and up) can re-add those extended stored procedures at any time. Also, most of those stored procedures have no permissions on them. Since SQL Server is a DENY by default, that means unless you bypass security checks, you can't execute them. The only ones able to bypass those security checks are members of the sysadmin role and those with CONTROL SERVER permissions. So the only ones capable of executing the stored procedures are the ones capable of putting them back into place. So you don't effectively do anything by removing the extended stored procedures.
more ▼

answered May 04, 2011 at 06:24 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

Brilliant explanation, thanks @K. Brian Kelley.
May 04, 2011 at 06:26 AM Fatherjack ♦♦
Indeed great help , thanks
May 04, 2011 at 06:43 AM mickyd
(comments are locked)
10|1200 characters needed characters left

Would it not be better to remove/control permissions to use the XPs rather than removing the XPs themselves.

You could get into a whole heap of issues if you removed them, as you say, they could be used by the system itself, and I'm guessing Microsoft wouldn't be too helpful in providing support if you ran into bugs as a result.
more ▼

answered May 04, 2011 at 01:20 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.1k 47 49 76

(comments are locked)
10|1200 characters needed characters left

I work with DoD standards for securing SQL Server installations and databases. Although they may not be the experts in security they can be used as a good example to follow. You can review them here: http://iase.disa.mil/stigs/app_security/database/sql.html

Within this checklist the only concern with the "xp_" external procedures is access to them. Overall they do not have to be removed to meet DoD standards, simply restricting access to them is sufficient and acceptable.

However I would restrict access to those that can use them to a very small number of individuals. Then also ensure that those that do have access have a strong/complex password.
more ▼

answered May 04, 2011 at 06:13 AM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 20 21 29

Thanks for link , excellent stuff
May 04, 2011 at 06:43 AM mickyd
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x3

asked: May 04, 2011 at 01:14 AM

Seen: 1364 times

Last Updated: May 04, 2011 at 02:01 AM