Solved

SP errors out when attempting to pass in var to a Console app that receives as an argument

Posted on 2009-04-03
12
625 Views
Last Modified: 2012-05-06
I have the following SP below that I'm attempting to use to call a Console app and pass a variable that contains a list of workstations. The console app works like this --> "ProofOfConcept -f c:data.txt". So, currently it takes a Filename, that gets passed into it, stored in a variable in the Console app, stores workstations in array to loop through, Ping and store Status's in DB. Someone else on our team will pass all the workstations to a variable in the SP called RunPingUtil. Please see code below. I've also copied error output that may help. I've been working on this one all day and could really use another set of eyes. :)
Thanks Experts,
Wally
------------------------------------ SP RunPingUtil -----------------------------------------------

USE [DMS]

GO

/****** Object:  StoredProcedure [dbo].[RunPingUtil]    Script Date: 04/03/2009 09:34:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER Procedure [dbo].[RunPingUtil]

AS

BEGIN

	SET NOCOUNT ON;

 

DECLARE @Workstations varchar(max)

SET @Workstations = 'B001617259BA7,B001E371E21B2,B001422C95811'

 

DECLARE @CommandLine sysname;

SET @CommandLine = '\\B001C231FC4CC\c$\Test\ProofOfConcept.exe "' + @Workstations + '"';

 

EXEC xp_cmdshell @CommandLine;

 

END

---------------------------- ERRORS When executing SP -------------------------------------

NULL

Unhandled Exception: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

   at System.Security.CodeAccessPermission.Demand()

   at System.Environment.GetCommandLineArgs()

   at ProofOfConcept.Program.Main()

The action that failed was:

Demand

The type of the first permission that failed was:

System.Security.Permissions.EnvironmentPermission

The first permission that failed was:

<IPermission class="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Read="Path"/>

NULL

The demand was for:

<IPermission class="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Read="Path"/>

NULL

The granted set of the failing assembly was:

<PermissionSet class="System.Security.PermissionSet"

version="1">

<IPermission class="System.Security.Permissions.FileDialogPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Access="Open"/>

<IPermission class="System.Security.Permissions.IsolatedStorageFilePermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Allowed="ApplicationIsolationByUser"

UserQuota="512000"/>

<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Flags="Execution"/>

<IPermission class="System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Window="SafeTopLevelWindows"

Clipboard="OwnClipboard"/>

<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Url="file://B001C231FC4CC/c$/Test/ProofOfConcept.exe"/>

<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

version="1"

Zone="Internet"/>

<IPermission class="System.Drawing.Printing.PrintingPermission, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

version="1"

Level="SafePrinting"/>

</PermissionSet>

NULL

The assembly or AppDomain that failed was:

ProofOfConcept, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null

The method that caused the failure was:

Void Main()

The Zone of the assembly that failed was:

Internet

The Url of the assembly that failed was:

file://B001C231FC4CC/c$/Test/ProofOfConcept.exe

NULL

Open in new window

0
Comment
Question by:wally_davis
  • 6
  • 3
  • 3
12 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24065150
It doesn't seem to be  a problem with the stored procedure. Please check this link:
http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/1505d7e6-59dc-4997-b707-9e9736a1f978/
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 24065800
Why are you using sysname as type for the @CommandLine variable in the sp? Use varchar(max) instead.
0
 

Author Comment

by:wally_davis
ID: 24073263
1. I couldn't change the @CommandLine to use the varchar(max) variable. I used the sysname because that appeared to be the recommendation when passing in a Path to an executable plus the variable being passed to it to get a proper execution. I did try it but received the following error: "Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'". So I left the type at sysname.
2. I made some changes to my code to accept an input of a variable versus a text file. I then attempted to run the SP again and this time I got the error " Output = Access is Denied" and Null. Return value was 0". So I'm one step closer.
3. As a last resort, I enabled "ClickOnce Security Settings" to run the Console app as a full trust application but that didn't help either.
Anyone else have any suggestions on why I can't pass the contents of the variable and why I'm  getting "Access is Denied".
0
 

Author Comment

by:wally_davis
ID: 24073283
FYI, I tried varchar(255) in place of "sysname" and I got the same error (Access is Denied). for whatever reason, I can't pass the @CommandLine to the xp_cmdshell with type varchar(max) and varchar(255) is not going to be enough for the amount of workstation names I want to pass in to be pinged. Somewhere between the neighborhood of 20,000 to 40,000 machines will need to be processed.
0
 

Author Comment

by:wally_davis
ID: 24073421
I've also verified that the xp_cmdshell is enabled on the SQL Server in question when I execute the xp_cmdshell within the SP.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 24077664
<<"Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'". So I left the type at sysname.>>

I don't understand, the error states clearly "Procedure expects parameter 'command_string' of type 'varchar'" but you go on with "So I left the type at sysname" !!!

Change the type to varchar(max) or insetad of (max) you can use a speciffic value like (8000). In SQL 2000 the maximum size for varchar was 8000 in 2005 you should be able to use bigger that than or (max) without problems:

DECLARE @CommandLine varchar(max);
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Zberteoc
ID: 24077678
On the other hand you have to understand that the comand line in dos/windows itself cannot be of any length as it is limmited to 255 most likely.  Adding a parameter,@Workstations, of bigger length won't help.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24077702
It is not a problem of parameter type. either varchar or sysname will work. (I've tested and double tested this in my environment). It is a problem with this program "ProofOfConcept.exe". I'm just wondering if you were able to take a look at the link I've mentioned in my previous comment.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24077776
Also, do you have xp_cmdshell enabled?. By default this sp is disabled in SQL 2005. Run the attached script and then try you code.
 


-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Open in new window

0
 

Author Comment

by:wally_davis
ID: 24079684
I've checked everything you guys have provided and tested it several times over but to no avail.
ProofOfConcept.exe works great. When I add a list of workstations within the Properties/Debug/Command line arguments textbox, the code works. I've also passed in a text file to this EXE and it works. However, I do see where when I run the SP after a couple changes per your suggestion, i.e. varchar(255) , I get "Output = NULL'.
Ralmada, the link you sent me was for changes to an ASP.Net application. I have a console application. My question is, where within VS 2008 to I make the neccessary changes to the code to see if those changes via that link might help?
0
 

Author Comment

by:wally_davis
ID: 24079691
FYI, yes, xp_cmdshell is enabled. I used the Surface Config Features util to verify.
0
 

Accepted Solution

by:
wally_davis earned 0 total points
ID: 24082527
Gentleman, I finally got it figured out.
1. Added ProofOfConcept.exe file to local SQL Server for local processing.
2. Added the data.txt file from which it would get passed to the EXE file
3. I had to use local Drive path vs UNC Path
and that pretty much did the trick. I do appreciate your help guys.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now