Developing Matt

My Technical Journal

Write to a text file without xp_cmdshell

leave a comment »

There are dangers of xp_cmdshell.  If turned on you can execute some pretty nasty commands.  Combine that with a poorly written dynamic sql procedure and you have a hackers delight.  It’s a window to the windows of the machine’s soul.

Many places don’t turn it on, and attempts to use it will give you:

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

I don’t blame them.  Unfortunately it takes out an opportunity to combine it with bcp and write to a file. 

The way you can bypass this is by using a job, adding a step that has a cmdExec step that executes bcp to write to a file. 

bcp "select field1, field2 from server.database.dbo.table"  queryout "c:\test.txt" -T –c

bcp bol

I’m still trying to understand ole automation, which isn’t turned on either.

Advertisements

Written by matt

January 14, 2012 at 5:57 pm

Posted in Sql Server, T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: