0

Running an executable on the server that connects to the database, extracts a username and password to be used in a call to a web service. This works on other servers, and I can run this from a command line or in a batch file. When I try to run this from xp_cmdshell the executable runs, but the call to the web service fails with the error 403 forbidden. Is there any reason why an external exe should connect different when called from a command line or from a SQL server command. As the executable starts fine and only when it is making a connection there is a problem I am at a loss here.

(the Previous text for this question, edited to try to make it more clear) We have an executable that connects to the database and at some point connects to a web service. This works fine when started from a batch file when we start this from within SQL the executable runs, but the connection to the web service returns the following 'The remote server returned an error: (403) Forbidden.' I know that the exe works, there are several functions that are run before the connection to the web service, and they all report back in the log file on the database. Does anyone know of a reason why the exe would react differently when run from SQL? (The same set-up is used on a different server and works fine)

We have tried a fresh install of SQL server and moving the exe file to another location. No difference for either solution. We are running the start of the procedure manually, run the exe from a batch file and complete the procedure manually when this has finished. Calling the batch file does not work either.

the expectation is that the connection to the web service is successful and following functions can use the connection to return results to be stored in the database.

erwino
  • 1
  • 2
  • 2
    It's kind of hard to tell what you're going on about (you start an exe using SQL??!) but this feels like a user account permissions/elevation problem. Your exe that accesses some networked resource works fine when started from an admin account directly, but when a (other process started using lower permissions) starts your EXE, it is no longer able to access the network resource. In simplistic terms if your account launches a.exe then the exe has all the permissions your account does. If your SQLServer service runs as some low privilege account then any exe it starts is also restricted – Caius Jard Aug 20 '19 at 13:21
  • See https://stackoverflow.com/questions/510170/the-difference-between-the-local-system-account-and-the-network-service-acco for example.. – Caius Jard Aug 20 '19 at 13:24
  • I know that the question is a bit vague, but no, the account that runs the exe is fine as the exe itself starts and connects to the database. It's only when the program is trying to connect to the web service with a username and password that the return is error 403. All other functionality within the program is working and returning entries in the database log files. The error handling continues with the next function and ends. (the exe is called from within sql as there are several tables to be updated before the web service calls to extract information) – erwino Aug 20 '19 at 13:43
  • what do you mean, "the exe is called from within sql"... how are you doing that? Also, @CaiusJard is probably right, the account used to run SQL Server probably wouldn't have access to the web service. – Hannah Vernon Aug 20 '19 at 14:13
  • OK, if this is an access rights problem is there a way to find out what account the procedure is running under? The exe is called from within the procedure using xp_cmdshell. (we are aware that there are security issues etc attached to this) – erwino Aug 20 '19 at 14:18
  • You're probably going to have to go into more detail on the web service error.. Saying that it's 403 means it's likely a response form the web server saying unauthorized (authenticate and try again) so it's not a problem accessing network resources per se (making a request to a srver and getting a response even a 403 is evidence that the app has network access). How does your web service authenticate? Can you show exactly, from wireshark, debug trace logs or whatever, EXACTLY what is being sent to th web service in a working scenario vs a failing one? – Caius Jard Aug 20 '19 at 15:18
  • The only feedback we have is from the log inside the program, the program is running as intended from any other server. This server is the only one that is giving us the error and only when run from within SQL. I understand that you are trying to get more information to get to the issue, and thanks for that @caius-jard – erwino Aug 20 '19 at 15:51
  • The puzzle is that you're asserting that the program runs fine when you double click it but doesn't authorize itself with the web service when sqls runs it; that can realistically only come from a config difference, such as "the program is reading a config file from one place when doubleclicked but another place when run by sqls" - something about the runtime environment created by doubleclick is different to that created by sqls, and it's possibly something as lame as working directory, but we need to see more code as to how this program calls the service, where it gets the user/pass etc – Caius Jard Aug 20 '19 at 15:53
  • The username and password, among some other settings, are read in from a table in the database. Still trying to determine if the exe runs under another user when started by a sql command, and if so, what is that user. Are there other firewall rules? is there a certificate that is called differently? – erwino Aug 21 '19 at 08:37

0 Answers0