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
626 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL - sum months, quarter, YTD 9 91
SQl works but add Having  by null or value 11 44
Compare data between two databases 16 95
How to simplify my SQL statement? 14 50
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

919 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

18 Experts available now in Live!

Get 1:1 Help Now