Solved

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

Posted on 2009-04-05
7
159 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

20 Experts available now in Live!

Get 1:1 Help Now