Solved

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

Posted on 2009-04-05
7
160 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 61
How to check data in sql table 11 47
Error when saving to sql table a '/' 5 28
How to simplify my SQL statement? 14 53
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 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