question

Slick84 avatar image
Slick84 asked

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-2008ssisadministrationxp_cmdshell
2 comments
10 |1200 characters needed characters left characters exceeded

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

hey @Slick84 - you got to 1k karma points :) well done!!
0 Likes 0 ·
Helll yeeeeeeah! Thanks ;-)
0 Likes 0 ·
Tim avatar image
Tim answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

Aah that makes sense. Any advice for the question in the end?
0 Likes 0 ·
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 Likes 0 ·
executing it within a sql job. it also fails in debug mode
0 Likes 0 ·
This is wild. How are you executing it in SSIS? As a SQL Task and calling xp_cmdshell 'c:\\file.vbs' ?
0 Likes 0 ·
I'm running the package by connecting to the SSIS through SSMS. I deployed it under the MSDB folder in SSIS.
0 Likes 0 ·
Show more comments
DaniSQL avatar image
DaniSQL answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
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 Likes 0 ·
I did, the configuration shows the correct value as in "1".
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

Therefore I also mentioned the Execute Process Task. You can launch a the VB script using the Execute Process Task.
1 Like 1 ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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