Permissions issue only when fully qualifying exe path. Why not always?

Question

When I try to execute the SQL Server 2012 BCP.exe utility to dump the contents of a table to a file using a fully qualified path to the exe,

D:\SQL2012\110\Tools\Binn\bcp.exe DBNAME.DBO.TABLENAME OUT %FileServerProject%\IMPLEMENTATION\DAT\Pre_Run_BaseTables\CDB_ACCT_CURR.DAT -S%SqlServer% -T -N >> %LogFolder%\Log.log

...I get an ACCESS DENIED error.

However, when I remove the fully qualified path to the exe and run,

bcp.exe DBNAME.DBO.TABLENAME OUT %FileServerProject%\IMPLEMENTATION\DAT\Pre_Run_BaseTables\CDB_ACCT_CURR.DAT -S%SqlServer% -T -N >> %LogFolder%\Log.log

It works fine.

If the path isn't qualified, my understanding is that Windows will search each folder specified in the PATH environmental variable looking for the specified exe, and execute first one found. So, I executed the following command from the console to see my PATH variable.

ECHO %PATH%

Cleaning up the miscellaneous unrelated paths from the output, I saw the following SQL-related folder returned in this order:

D:\SQL2012\110\DTS\Binn\;
D:\SQL2012 (86)\110\Tools\Binn\;
D:\SQL2012\110\Tools\Binn\;

The latter Tools\Binn path was the only folder that contained the bcp.exe utility.

My question is this:

Since the same EXE was executed whether I explicitly qualified the path or left Windows to find it by searching the path variable, why did I get an ACCESS DENIED error when I ran using the fully qualified path and not when I did not qualify the path?

Note that in both cases I was running under an ID that had read and execute rights to the TOOLS\BINN folder. In the case where I was using the fully qualified path, if I added the account to the Local group, it would work but this was not a viable solution. Also, the ID had LogOn As Batch rights to the server.

Update:

I now have no doubt that when I execute using the unqualified bcp.exe path that I am in fact running the only copy of bcp.exe on the server. For starters, I used Search Everything to extensively search every drive on the server. I found three occurrences. I then renamed the 2 that I did not want to accidentally reference.

I then reran the job using the unqualified bcp.exe path and using Task Manager's process tab, I found bcp.exe running under the service account. I then right-clicked on the file name and selected the context menu "Open File Location", and it took me to the only location of the bcp.exe file that was not renamed -- the file that I was intentionally trying to target with the fully qualified name.

D:\SQL2012\110\Tools\Binn

Since the bcp.exe was not qualified, it ran successfully.

Update 2 So far, 42 people have looked at this. I'd be curious if people are looking at this saying "that's impossible, that the facts of this case must not be exactly as I've stated."

Almost there: I simplified the batch file down to the bare minimum to reproduce the problem. You will notice that I changed our true path names, but I kept the gist of it.

Here's the "Before" code:

 ----------------------------------------------------------------
 Output of messages for workload object TESTDUMP/GHG9999I.11/MAIN
 Start date Fri Sep 25 13:33:36 2015
 ----------------------------------------------------------------

 C:\Users\MyServiceAccount>WHERE bcp.exe 
 INFO: Could not find files for the given pattern(s).

 C:\Users\MyServiceAccount> D:\SQL2012\110\Tools\Binn\bcp.exe  MyDB.DBO.MyTable OUT \\MyFileServer\IMData\MyDB_SOURCE\IMPLEMENTATION\DAT\Pre_Run_BaseTables\MyTable.DAT -S MyDbServer\int -T -N  1>>\\MyFileServer\IMData\MyDB_SOURCE\Logs\MyTable_BCP_out.log 
 Access is denied.

 C:\Users\MYSERVICEACCOUNT>ECHO RESULT=1 
 RESULT=1

Here's the "After,", which worked

 ----------------------------------------------------------------
 Output of messages for workload object TESTDUMP/GHG9999I.10/MAIN
 Start date Fri Sep 25 13:33:00 2015
 ----------------------------------------------------------------

 C:\Users\MyServiceAccount>WHERE bcp.exe 
 INFO: Could not find files for the given pattern(s).

 C:\Users\MyServiceAccount>"D:\SQL2012\110\Tools\Binn\bcp.exe" MyDB.DBO.MyTable OUT \\MyFileServer\IMData\MyDB_SOURCE\IMPLEMENTATION\DAT\Pre_Run_BaseTables\MyTable.DAT -S MyDbServer\int -T -N  1>>\\MyFileServer\IMData\MyDB_SOURCE\Logs\MyTable_BCP_out.log 

 C:\Users\MYSERVICEACCOUNT>ECHO RESULT=0 
 RESULT=0

Note that the difference is that the BCP path that worked was enclosed in quotes. I would have thought that this would have been important only if the path contained embedded spaces. I am confused why it mattered in this case.

A secondary new concern is why the WHERE command failed to work when running under MYSERVICDEACCOUNT through a scheduler. The command works when I log in manually under MYLANID, navigate to c:\users\mylanid, and try it.

If someone can explain why the quotes mattered, they get 100 pts and my gratitude.

Update 4:

I located the AgentParm.txt file on a server where the same code works. It was under the Program Files folder:

# Agent settings for nt-x86-64
agentname=MyWorkingServer
log.archive=2
oscomponent.jvm=server

On the server that we have been discussing where I have the issue when the batch file is unquoted, I see the following in the Program Files (x86) folder. All other lines were the same, so I excluded them. I did not see any mentioned of oscomponent.cmdprefix.force.quotes.full.

# Agent settings for nt-x86-32

agentname=MyServer

Do I need the 64 bit version of CA scheduler to run 64 bit exes? If so, will I have an issue running 32 bit exes (like SQL Server dtexec.exe) with the 64 bit CW Scheduler?

Sudoriferous

I suggest using procmon to work out what is going on – Uninterested

Actually, I tried to. I never saw bcp.exe enter the mix. I guess I don't know how to use the tool. I intended to verify that it was the same exe that was being run in both cases. While I ciuld noit verify this, I am close to certain that this is the case. I'll try to eiether figure out how to use it, or searchj the whole server for other occurences of bcp.exe – Sudoriferous

I searched for multiple bcp.exe files. I only found the one – Sudoriferous

don't just rely on where you think the executable might be, you have no idea how many folks make copies and place it in random places. Search the entire C: and D: drive. ie: C:\> dir /S bcp.exe, and D:\> dir /S bcp.exe – Incompatible

You might find this interesting to find which executable gets executed. – Substitute

Regarding procmon -- the process explorer should be sufficient if you increase the timeout to hide terminated processes (can't check right now the exact name of this option) – Substitute

Saying where bcp should show you the path to the instance that will run if you don't specify a path. (Note that the process running under the service account can't be the process you launched on the command line - not unless you're logged in as the service account.) Note also that it is possible for an application to behave differently depending on the command line used to launch it, but it is unusual. – Pinafore

Process Monitor might also be able to help you find which file the access denied error is referring to. – Pinafore

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. Y:\>where bcp.exe D:\SQL2012\110\Tools\Binn\bcp.exe Note that I did not launch bcp.exe from the command prompt. I ran it from a job scheduler and then logged on with a different ID, opened up Task Mgr and saw the bcp.exe running under the Service Account. Taskman confirmed the location of the bcp.exe as being this same path. I believe the file that cannot be accessed is the BCP.exe file itself, but only when fully qualified. I don't know FileMon to verify this, but it seems to be the case. – Sudoriferous

filemon/procmon tells you which file fails an access call. It's a good tool to work out exactly which file is failing – Uninterested

It is probably not the case, but the dll search order might be different – Substitute

I'm one of the viewers and,yes, I am finding it hard to believe, but you're checking all the right things. Here's a guess - one of the variables (e.g. %LogFolder%) is a relative path and when you run bcp.exe with no path, it's appended to the current directory and when run with fully qualified path its appended to a directory where the task doesn't have access. Pure guesswork, but if it turns out correct - I'll write it up as an answer... :) – Irretrievable

The %logfolder% unfortunately is not a relative path. It's a absolute folder location in unc form, if I remember correctly. I'll try to simplify the example to the bare minimum and post the complete example. I like the way you were thinking though, thanks – Sudoriferous

As said before, this is really weird... One thing you could try to be absolutely sure that it is really this copy of bcp running: You could rename the one you want to run and call this changed name with your command... – Cabe

@chadD What are the values of %FileServerProject% and %SqlServer% (don't need exact values if confidential - just representative)? The full error message would help - is it SQL Server does not exist or access denied.? I'm assuming it's that, rather than the log file that has access denies? – Irretrievable

Please see new comments under "Almost there..." – Sudoriferous

@chadD Well diagnosed!! I'm thinking about the quotes.... My guess is now that this has to do with the scheduler. What scheduler are you using? I think it has to be how it's treating the \` character. Maybe it needs to be \\\` if it's not quoted. Although if that was the case, it's strange that it doesn't also affect the paths passed in as arguments to the job. – Irretrievable

Hmm - from the output I'm guessing it's CA Workload Automation scheduler. This is probably quite security concious - I wonder if the tool forces you to quote any path to an executable in order to avoid issues and even potential exploits via unquoted paths if they do have spaces (even thought yours doesn't). I haven't got the tools to test this theory - do you have any instances (e.g. other tasks) where a qualified but unquoted path works? – Irretrievable

Yes, we use CA Workload Automation scheduler. We recently migrated from a Windows 2003 serve with SQL 2008 to a Windows 2008 server with SQL 2012 and we were trying to replicate the environment. We used CA Workstation scheduler on both servers and the unquoted fully qualified path worked on the old server. so, it is not the presence of CA WS scheduler in an of itself causing the issue but some yet identified factor. Normally I would think that a fully qualified path is the way to go as the more cautious route, but I now question this approach. Thanks for your input. – Sudoriferous

I don't yet know of any fully qualified but unquoted paths that work on the new server. I'll try to find one, if it exists. – Sudoriferous

I don't think I can add anything else. I can't repro in cmd or powershell environments with exactly the same directory names. I don't have CA automation or SQL server to test the exact same configurations. My last roll of the dice is that CA is doing something to the path in translation when it is not quoted - possibly the \110 is problematic and being treated as some kind of escape code (which would be a bug). I don't think I can add anything further – Irretrievable

Answer 1

Could it have something to do with what you pointed out? probably inadvertently

32Bit D:\SQL2012 (86)\110\Tools\Binn\ 64Bit D:\SQL2012\110\Tools\Binn\ your hard path is calling 64 bit and your %PATH% variable is finding the 32Bit Version first.

Cockatoo

There is no 32-bit BCP.exe file utiltiy on the server. There is only one such file, found in the 64 bit utility folder. – Sudoriferous

Answer 2

I'm not familiar with CA Workload Automation scheduler, but are you using its JIL syntax when specifying the job?

A search of support docs seems to indicate that a colon (":") is a special character in JIL and needs to be escaped by quotes or backslash.

Rule 5
Valid value settings can include any of the following characters:
■ Uppercase and lowercase letters (A-Z, a-z)
■ Hyphens (-)
■ Underscores (_)
■ Pound signs (#)
■ Numbers (0-9)
■ Colons (:), if the colon is escaped with quotation marks (" ") or a preceding backslash (\)
■ The at character (@)
Note: Object names can only contain the following characters: a-z, A-Z, 0-9, period (.), underscore (_), hyphen (-), and pound (#). Do not include embedded spaces or tabs.

Callimachus

Answer 3

This is not exactly the same situation that you described, but it seems like it might have the same root cause and fix. The documentation here (p45) describes a situation where jobs with qualified paths passed to CA Workload Automation Agent in Windows behave unexpectedly when quoted. This applies to paths both with and without spaces.

In a nutshell - it seems to have been fixed by adding a parameter oscomponent.cmdprefix.force.quotes.full to the agentparm.txt file, which you can set to true or false depending on the behaviour you want.

The situation described doesn't exactly match yours, so I'm not 100% sure this is a fix, but it would be worth toggling that setting to test whether it toggled the behaviour you observe.

Irretrievable

@ChadD very interested to hear whether this explains the issue - it's a really weird symptom and I'd be intrigued to hear the resolution. – Irretrievable

@ChadD I saw the update - I'd be pretty surprised if it's a 32/64 bit issue as the quoted version worked on the 32 bit server as I understand. I guess the fact that it works on one server without the above param and not on the other suggests it's not that. You could try adding the parameter for testing to the server that doesn't work if it's minimal hassle. But other than that - I think I really am out of ideas this time. – Irretrievable

Thanks, I really want to know and will be revisiting this, time permitting. And I will try to make the time, especially if there are additional suggestions to try. Thanks much – Sudoriferous