Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-05
7
Medium Priority
?
173 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:wally_davis
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24073462
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
 

Author Comment

by:wally_davis
ID: 24073677
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
 

Author Comment

by:wally_davis
ID: 24073732
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24073888
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
 

Author Comment

by:wally_davis
ID: 24080542
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 24082364
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
 

Author Closing Comment

by:wally_davis
ID: 31566849
Copied the files (Exe and data.txt files) over locally to SQL Box, changed UNC to local D:\.....worked great! Thanks nmcdermaid!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

569 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