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


Executing EXE from Sql Stored Procedure

Posted on 2003-03-05
Medium Priority
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.
Question by:Jeffbub
LVL 70

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.


Author Comment

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.

Author Comment

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.

Accepted Solution

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.

Expert Comment

ID: 9276129
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 
Post your closing recommendations!  No comment means you don't care.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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