Access is Denied when running an SP to pass a variable to a Console application

I have a SP that when executed (eventually by another process), it gets a list of workstations passed to it (i.e. the variable @Workstations) which in turn will get passed to a Console.exe application to process those workstations and ping them.
However, when I run it, I get "Access is Denied". My console app was getting a text file passed to it and I changed the code to accept a variable containing workstations that are comma delimited. I passed in the workstations into my V2 2008 Debug/Command line arguments property and it worked great. Below is my SP. I've also ensured that xp_cmdshell is enabled on the SQL Server.
Thank you in advance for your help experts.
Wally
USE [DMS]
GO
/****** Object:  StoredProcedure [dbo].[RunPingUtil]    Script Date: 04/05/2009 13:08:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE 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 + '';
--SET @CommandLine = 'c:\Test\ProofOfConcept.exe "' + @Workstations + '"';
 
EXEC xp_cmdshell @CommandLine;
 
END

Open in new window

wally_davisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nmcdermaidConnect With a Mentor Commented:
You need to log into the actual server and run that command line and see what happens. I assume that the EXE requires libraries that are not installed on the server.
Also if they are local then you are better off with a command line like this:
d:\ProofOfConcept\ProofOfConcept.exe ' + @Workstations + ''
Otherwise it may get confused by the D$ share.
 

 
0
 
nmcdermaidCommented:
Was the path to the file containing the list of workstations a local or remote path? xp_cmdshell runs under the login account of the SQL Server service (when it is called by a sysadm). By default this service logon account is Local\System. By default this particular account does not have access to non local files.
To put it another way, are you creating the workstation list file on the actual SQL Server?
0
 
wally_davisAuthor Commented:
The path to the file currently is on my local PC but I'm running the SP from another SQL Server, not a local Server on my Laptop. So, the file list will eventually be sent to this SP to store it in the @Workstation variable.
We do have a local account created on SQL that has all of the admin priveleges. Is there a way I could use that account or if any changes can be made, what, if any, can I make with respect to getting these file processed?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
wally_davisAuthor Commented:
FYI, Keep in mind right now that I'm actually adding/testing the list of workstations from right within the Stored Procedure. So, again, the workstation names will be passed to this SP.
0
 
nmcdermaidCommented:
So can you clarify: the eventual working system will not have a file path passed to it, it will have the actual string of workstations passed to it?
So the next issue could be one or both of the following:
1. You don't have access to the xp_cmdshell stored procedure
2. The SQL Server doesn't have access to the \\B001C231FC4CC\C$\Test\ProofOfConcept.exe file.
To sort issue 2, go into the services applet, then the SQL Server service, and change its logon to the privileged account that you have.
Also keep in mind that if ProofOfConcept.exe needs any special libraries etc., they aren't going to be installed on the SQL Server (unless you do it yourself)
Also keep in mind that there are various security issue around using xp_cmdshell, which is why it is locked down by defaault.
What is proofofconcept doing? is it just pinging workstations and reporting on them? you can do that directly from xp_cmdshell anyway, without having an EXE
0
 
wally_davisAuthor Commented:
nmcdermaid, I changed up the code and the SP a bit. I've put the ProofOfConcept.exe and "data.txt" files on the SQL Server where the SP "RunPingUtil" gets launched. So, I have everything on that server I need. I even added an output to the SP so I could see the output of the executable and path to the filename as it should be processed. (Please see SP below).
The output I get is --> \\Crprchsqlapp\d$\ProofOfConcept\ProofOfConcept.exe "\\Crprchsqlapp\d$\ProofOfConcept\data.txt".
(I even shortened it that the output showed as  "\\Crprchsqlapp\d$\ProofOfConcept\ProofOfConcept.exe )data.txt". I've tested the passing in of this file from the Cmd prompt on my laptop, i.e. c:\test\ProofOfConcept c:\data, and it worked great. Any other ideas on why the SP is not passing the text file and launching the executable?

USE [DMS]
GO
/* RunPingUtil - takes in a string of text or text file
 * and passes to EndpointStatus.exe as arg and pings wkstns
 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER Procedure [dbo].[RunPingUtil]
@myoutput varchar(4000)  output
AS
BEGIN
	SET NOCOUNT ON;
 
DECLARE @Workstations varchar(max)
SET @Workstations = '\\Crprchmsqdve\d$\ProofOfConcept\data.txt'
DECLARE @CommandLine varchar(255)
SET @CommandLine = '\\Crprchmsqdve\d$\ProofOfConcept\ProofOfConcept.exe ' + @Workstations + ''
 
EXEC xp_cmdshell @CommandLine
select @myoutput=@CommandLine
END

Open in new window

0
 
wally_davisAuthor Commented:
Copied the files (Exe and data.txt files) over locally to SQL Box, changed UNC to local D:\.....worked great! Thanks nmcdermaid!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.