Surface Area Configuration Manager

Question

question

Slick84 asked May 25, '11

Surface Area Configuration Manager

Hi, I'm using SQL Server 2008 for a reporting system and wanted to enable the xp_cmdshell through the Surface Area Configuration manager but was unable to find it. I have enabled it through sp_configure but was wondering if the "Surface Area Configuration Manager" no longer exists on SQL Server 2008 and onwards? Additionally, even though I have enabled xp_cmdshell (which I understand is a security risk), I still get an error that its not enabled when I run a DTSX package which basically grabs a file from an FTP using xp_cmdshell. Any advice would be helpful. Thanks, S

sql-server-2008 ssis administration xp_cmdshell

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · May 25, 2011 at 11:25 AM

hey @Slick84 - you got to 1k karma points :) well done!!

0 ·

Slick84 commented · May 25, 2011 at 12:54 PM

Helll yeeeeeeah! Thanks ;-)

0 ·

Answer 1 · 2011-05-25T08:30:15Z

Tim answered May 25, '11

It is now listed under the facets under Server security. Enabling it via T-SQL is still a great route to go. ![alt text][1] [1]: /upfiles/facet.png

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · May 25, 2011 at 08:40 AM

Aah that makes sense. Any advice for the question in the end?

0 ·

Tim commented · May 25, 2011 at 10:19 AM

Ah, I see you did run reconfigure. When you say it doesn't run in SSIS is that in debug mode or when executing within a SQL Job?

0 ·

Slick84 commented · May 25, 2011 at 10:22 AM

executing it within a sql job. it also fails in debug mode

0 ·

Tim commented · May 25, 2011 at 10:47 AM

This is wild. How are you executing it in SSIS? As a SQL Task and calling xp_cmdshell 'c:\\file.vbs' ?

0 ·

Slick84 commented · May 25, 2011 at 12:59 PM

I'm running the package by connecting to the SSIS through SSMS. I deployed it under the MSDB folder in SSIS.

0 ·

Show more comments

Answer 2 · 2011-05-25T09:52:39Z

DaniSQL answered May 25, '11

Did you run 'RECONFIGURE' after you enable xp cmdshell? Below is what i usually run to enable any of the advanced options in my servers: EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp.cmdshell', 1 GO RECONFIGURE GO EXEC sp_configure 'show advanced options', 0 go RECONFIGURE GO

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · May 25, 2011 at 10:12 AM

Dani, I did that. I just executed my xp_cmdshell 'c:\file.vbs' in SSMS and it worked fine. The issue seems to be only coming through if I put this in a SSIS package... any idea's? My first thought is security but the account that im running in SSMS is mine and im also executing the package from within SSIS using my credentials.

0 ·

Pavel Pawlowski commented · May 25, 2011 at 11:43 AM

Did you check after the `RECONFIGURE` by executing simply `EXEC sp_configure` that the SQL Server is running the new configuration values? If the server is still running old configuration, you can try `RECONFIGURE WITH OVERRIDE` to force the new configuration value.

0 ·

Slick84 commented · May 25, 2011 at 12:55 PM

I did, the configuration shows the correct value as in "1".

0 ·

Answer 3 · 2011-05-25T11:39:47Z

Pavel Pawlowski answered May 25, '11

Why to use xp_cmdshell in SSIS package for loading a file from FTP? I suggest you use a **`FTP Task`** to transfer files from/to FTP site. And even you do not want to use the FTP Task, you can use the **`Execute Process Task`** to launch command line ftp client to grab the files.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski commented · May 25, 2011 at 01:39 PM

Therefore I also mentioned the Execute Process Task. You can launch a the VB script using the Execute Process Task.

1 ·

Slick84 commented · May 25, 2011 at 12:57 PM

You know, I have a VB script. I dont know programming so my problem is I can't use script task to write this little VB script. It's actually not a FTP really.. but its basically a website sort of like reporting services where I download the file from. Internal of course. The script task tool doesnt allow VB script execution from it and I didnt know of any other option. I had used the xp_cmdshell before and it's very easy to implement so just went with that. Right now I've basically setup a SQL job which executes the xp_cmdshell 'vb file' and its working perfectly. I'm not pursuing the SSIS path right now due to time constraints. :-)

0 ·

question