Solved

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

Posted on 2009-04-05
7
158 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
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 30

Expert Comment

by:nmcdermaid
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

9 Experts available now in Live!

Get 1:1 Help Now