?
Solved

Executing EXE from Sql Stored Procedure

Posted on 2003-03-05
5
Medium Priority
?
2,900 Views
Last Modified: 2012-05-04
I have written an EXE using Visual Basic that updates some things in a database. What I am eventually wanting it to have the EXE run on a set schedule. So I was wanting to have a job scheduled on the Sql server, using sp_add_job, etc.

Right now I am just trying to test the syntax to execute this EXE. What I have been trying is

EXEC master..xp_cmdshell 'C:\temp\adodbTst.exe'

When I run this executable from the dos prompt myself, it works just fine. But when I run it through Sql it doesn't ever finish, it just hangs there and never completes.

I have been issuing this command by using the Sql Server Query Analyzer, which I wasn't sure if this is the best way to issue T-Sql statements or not. When I first start the Query Analyzer I connect to the database using my Windows logon information, so I would think it would be using my windows permissions when I run the xp_cmdshell command, is that right?

Any ideas on why this is not working? Is there a way to debug the execution to see what is making it not complete? Any help would be great, thanks.
0
Comment
Question by:Jeffbub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8074312
Here are a couple of possible things to consider:

*) from BOL:
"
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.
==> If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. <== On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
"

*) The EXE must not be interactive; it cannot receive a user response to any prompt while running under xp_cmdshell.


0
 

Author Comment

by:Jeffbub
ID: 8075028
I was looking into the sp_job commands as well and noticed that sp_add_jobstep had an option to specify the database_user which the procedure would run on. I set this to my "domain\logonName", but still ran into the same problem. I would have guessed that this would have executed the command just as if I had typed it at the command line, but maybe not.

I don't believe my program is interactive in anyway either. In that there are no message boxes, or input boxes, and the program exits by itself when completed.
0
 

Author Comment

by:Jeffbub
ID: 8075208
I guess maybe I should add something else to explain what I am trying to do. The program that I did write needs to access the System event log on one computer (which requires administrative access on that computer) and it also needs access to the database (on a separate computer). What I have tried so far was to add my logon name to the SQL list of database users. Then I made a DSN that uses Windows authentication instead of Sql authenticatoin. My reasoning is that I was thiking this might be more secure, then using a connection string that had the `sa' password in clear-text in my visual basic program. Then if the executable is run under my logon name, it will have access to both computers with the necessary access.

I'm not sure if this is the best, or most secure way to do this, (probably not). Maybe there is a better way to achieve this that will solve my above problem as well.
0
 
LVL 3

Accepted Solution

by:
Frostbyte_Zero earned 400 total points
ID: 8075246
I have successfully done this. If you wrote the EXE to use NT Authentication, the job will run using the user credentials of whomever invokes the EXE. If you execute it, it will use YOUR credentials.

However, if you let it run under SQL Agent. The EXE will use the credentials of the SQL Agent service. I assume you are running on NT/2000. In Control Panel on the PC running SQL Server, click on Services (it may be under Administartive Tools in Win2K). Look for SQLServerAgent. Note who it is set to LogOnAs. That is the user that SQL Agent will use to execute DOS commands. Make sure that user has the proper rights to your SQL Server.

Also, in the connection string on the EXE you can set the APPLICATION parameter. This would allow you to identify the process and see what it is doing in CurrentActivity under SQL Enterprise Manager under Management/Current Activity/Process Info when it runs.

Good luck.
0
 

Expert Comment

by:CleanupPing
ID: 9276129
Jeffbub:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

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