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
632 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 27

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
 
LVL 27

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

726 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