Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
sql 2005 enterprise 64 bit (AMD) not running .Net app that uses COM Interop with 32 bit dll

Odpovědět sql 2005 enterprise 64 bit (AMD) not running .Net app that uses COM Interop with 32 bit dll

  • Thursday, April 26, 2012 2:49 AM
     
     
    Sign In to Vote
    0

    This started in the VB forum and it was suggested that I repost on SQLServer since the app runs fine except under the SQL agent...

    I have a  VB.Net 2010 (.net 4.0 framework) console app that uses COM interop to automate a 32 bit (non-MS) application.  It has been compiled for X86 target only.   To achieve some level of stability, the com automation process is run inside an appdomain.  I need to be able to run mulitple instances of the app on the same server, and would prefer to use SQL server 2005 Jobs as the way to launch multiple instances of the app.  For reasons that probably aren't too relevant here, I need to ensure that only one console app is trying to instantiate the appdoamin at any given time, and I have chosen to create a specific file in the temp directory with exclusive access until the appdomain has been created. 

    The automation works fine with multiple instances as long as they are launched interactively.  Watching task manager, I observe each COM process appear with an asterisk next to the executable name.  As the applications complete,  the related automation processes shut down and disppear from the processes list.  Logging and task manager provides evidence that all processes are using completing the work successfully.

    When I schedule the jobs in MS SQL Server 2005 (64 bit),  The automation executable shows the sql server agent account (a domain account with high privs), but doesn't have an asterisk next to it.. The error captured whenever more than one process is  started is:"

    System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID {2DFED5C5-79B3-4D6C-A902-DD8E7B97B605} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)). at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType) at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType) at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props

    , Boolean bNewObj) at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache) at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Obje (truncated)....'                                     . 

    I realize that this is not providing a lot to go on, but any guidance on how to get more than one instance happily running as 32 bit processes when launched as SQL server jobs would be very much appreciated.

    I have tried a few different ways to invoke using cmd.exe, cscript, and Wscript in the syswow64 directory when creating the SQL job.  In all cases the exe comes up  with an asterisk in the task manager process list, but the exe related to the 32 bit COM component does not.  Everything works fine doing the same calls interactively with my domain account.  very confusing.

     

All Replies

  • Tuesday, May 01, 2012 8:20 PM
    Moderator
     
     
    Sign In to Vote
    0
    in the job execution options you can find execute in 32 bit or run as 32 bit, just check it and try again

    http://www.rad.pasfu.com

     
  • Tuesday, May 01, 2012 8:45 PM
     
     
    Sign In to Vote
    0
    At one point I tried to create an SSIS package and know there is a 32 bit option there, but I observed that the interop code (running in a separate appdomain) was still showing up without an asterisk in the task manager. I still couldn't run multiple instances so I have reverted back to a scheduled job based on OS command. SSIS seemed like more overhead without tangible benefits.  I don't see any options for 32 bit within the menus for excution options in the job properties of SQL server agent jobs.  Since the main console app is 32 bit only, it runs as it should, but not the interop code .  If there is such an option for SQL Agent Jobs can you give more details how to find it?
     
  • Wednesday, May 02, 2012 12:06 AM
    Moderator
     
     
    Sign In to Vote
    0

    In SQL 2005, there isn't an option in the Job Step Properties of Agent to tell SSIS to run in 32 bit mode.  Instead, you have to use an OS Job Step type and refer to the 32-bit version of DTExec to run a 32-bit SSIS job (in the Program Files (x86) folder).  (In 2008+, there is a nice little "32-bit mode" checkbox for SSIS Job Step types.)

    Todd McDermid's Blog Talk to me now on


     
  • Wednesday, May 02, 2012 12:56 AM
     
     
    Sign In to Vote
    0

    I have tried this again just now... have created an SSIS package running the .net console app passing a runtime parameter with config information.  I'm using the os command step launching with... "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe"  /FILE "E:\Apps\myfile.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E...

    I believe that the (X86) directory should have the 32 bit DTExec in it.  I see the main exe referenced in the SSIS package is launched as 32 bit (it is a 32 bit /X86 only app), and the interop process shows up in task manager with no asterisk and seemingly never fully loads the interop in the appdomain.  every five minutes or so, something burps and my code retries without logging much details, and certainly not doing any tangible work it is designed to. 

    The very same SSIS package runs fine if I launch it interactively from my ID.  It also works fine if I run the code from the OS Scheduler, but my admins don't like jobs like this in the OS scheduled tasks.

    Again, my preference is to not use an SSIS package, but simply call the exe passing a config file name as a command line.  I've tried using C:\WINDOWS\syswow64\cmd.exe  /c E:\Apps\run.bat  where run.bat has the command in it.  which seems like it should launch the 32 bit version of the command processor from the SQL agent, but again. that fails.  I've alsoe tried passing the exe and config file name to C:\WINDOWS\syswow64\cmd.exe.

    I've never experienced this much trouble with sql agent jobs.

     

     
  • Wednesday, May 02, 2012 3:49 PM
    Moderator
     
     
    Sign In to Vote
    0

    Given what you've reported so far, I don't think the issue is with SSIS.  SSIS launches the package in 32-bit mode, which you can confirm in Task Manager.  The process that the package launches executes in 32-bit mode, which you've also confirmed.

    Perhaps the 64-bitness of interop process is an indicator of a problem, but not the real problem itself.

    Perhaps the real problem is user accounts or registry access, as your interactive execution runs as expected.  Here's a way to test that somewhat, or at least narrow it down.  You need to create a proxy to run that job step.  You can watch this video, or take a look at the related help topics around creating and modifying proxies in SQL.  Create a proxy (for testing) with your own account credentials.

    If running the package with the proxy of your account works - then the issue is the SQL Agent account doesn't have the permissions necessary for some part of either the launching or execution of the interop process.  If the execution still fails, it indicates the problem is with a need for an interactive login and/or access to the user hive in the registry (which doesn't get loaded in unattended modes).

    Todd McDermid's Blog Talk to me now on

     
  • Wednesday, May 02, 2012 5:39 PM
     
     
    Sign In to Vote
    0

    I agree that the evidence in task mamager could very well be a symptom and not the cause.  Also,  SSIS is really only a bit player in this topic and I would prefer to NOT use it if all it does is run a windows exe. 

    As a test, based on your suggestion,  I created a new credential that is my domain account, and then a proxy that was associated with that credential.   I applied that proxy to the job step that runs the DTExec command above and it has run to completion successfully.  Task manager shows the exe and the related com item as 32-bit, with my account associated (instead of the SQL agent domain account) and I'm seeing expected CPU activity for both while another process is running which is all positive.

    I don't know much about how the com component works, but am pretty sure that it makes use of the HKEYCurrentUser Hive, so that could be an issue if it isn't being populated without a proxy. 

    So, that gets me much closer to where I need to be.  As a final solution however, with frequent password change requirements, the server admins probably won't sign on for setting up credentials and proxies for the SQL agent domain accounts.  Even if it could be scripted, sounds like a bad idea.

    Are there next steps you can suggest?   

     
  • Wednesday, May 02, 2012 6:28 PM
    Moderator
     
     
    Sign In to Vote
    0

    OK - since it ran with your credentials, that gives us a decent place to start.  It doesn't say whether or not the user hive is needed yet, that's the next test.

    You'll need to ask your server admins to set up a domain account for a service.  That's not an uncommon request.  This service account should be locked down as much as possible - as it's not a user account - but given sufficient rights to accomplish what your EXE needs to do (access to folders, etc).  This service account's password should never expire.  Set up a credential and proxy for that account, and run the job step using that proxy.

    Todd McDermid's Blog Talk to me now on

     
  • Wednesday, May 02, 2012 8:12 PM
     
     
    Sign In to Vote
    0

    The main reasons for scheduling via SQL Scheduled jobs are:

    • This is a platform understood by our infrastructure group
    • It's easy to maintain/ change the SQL Agent password once/server for however many jobs are scheduled
    • It has a wealth of reporting/restart capabilities and status is readily visible

    The main reason that OS Scheduled tasks are discouraged is that maintaining passwords is more difficult (every task of an unknown list).  We don't use proxies today, so that adds unfamiarity and complexity when passwords for domain accounts need to be changed. This process will run on dozens of servers worldwide. For now, I'll continue down this path to see where it goes, but I have doubts that it will be a viable solution unless swapping domain accounts for credentials can be scripted and run reliably.

    I'm thinking it will be difficult to go to our security group (not server admins) and ask for a service domain account that never expires.   The required security has to be fairly broad to access many databases (not just local files and registry) via integrated security.  But for the sake of continuing the discussion, let's assume that can happen...   Aside from the fact that the proposed new account wouldn't have rights to my personal network shares, it's not clear what new information would be gleaned from setting up the account.  Is this so that I can figure out the  minimum subset permissions required to run with a proxy/credential?

     
  • Wednesday, May 02, 2012 9:14 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    What you'd learn is whether the job you're trying to run needs a user's registry hive loaded.  If it does, that could present problems, because that usually only happens on interactive logins.  I do seem to recall that Windows Scheduled Tasks have a setting to load the registry... but I can't recall for sure.

    I'm not sure where the "complexity" of maintaining service accounts comes in.  In all the ways I can think of, having a service account set up to "own" the job adds good complexity:

    • Each "system" would have its own service account (or more, if the "system" can/should be subdivided into different security contexts).  This means you can lock out a particular service at any time without affecting other services, and without knowing what jobs/systems/tasks you have to touch.  When you retire a "system", you can immediately retire it everywhere, and not have zombie jobs running because you're not sure what they're involved with.
    • Each service account can be configured to have the bare minimum rights required to do a job.  Instead of opening up the SQL Agent service account to be able to read/write to a network share because one job needs access, you set up its service account with that access.  This means that no other job run by that Agent can get in that share - only the job that needs to can.
    • Each service account can be set up to login to whatever servers/services it and only it needs (said that before), the advantage being that you can lock down rights specifically for that service (said that too), but you can also track access specifically to an account.  Running SQL Profiler (for example, but not limited to just that) will now show you which services are touching which databases, and how.  If you have the generic Agent account owning those actions, you don't know what's touching what.

    I'm definitely no expert on proxies or why they're good - but I get this info from the experts, and I they all say that's the way to go.  If you want some backup, just let me know and I'll reach out to get some.

    Todd McDermid's Blog Talk to me now on

    • Marked As Answer by JT of MN Thursday, May 03, 2012 1:58 AM
    •  
     
  • Thursday, May 03, 2012 1:58 AM
     
     
    Sign In to Vote
    0

     I can see where setting up an account for each function could be a really good way to partition security.  No matter how strong the case is for doing what you're proposing, it's not the way our (large-ish) corporation does things today.  Special accounts seem to be assigned to departments (not systems) in pairs so that mandatory password changes can be done on the account not currently in use, then then accounts are swapped on hundreds of servers using a script.  That is fairly straightforward to script SQL Agent account swaps across many servers.  Scripting a simlar change of SQL credentials for scores of jobs running on different servers might also be doable, but sounds more complex.  More investigation would be needed.  A lot of developers can set up their own jobs, and I don't see anything that would prevent someone else from choosing credentials set up for my system to run a package requiring similar rights.  Our domain, system, and SQL admins typically don't have the time to customize access to the level you're thinking.  I have started the conversation, just don't have a lot of confidence that anything will change fast enough to work for this project.   So if credentials are a requirement for my code to run under the SQL agent, that's a problem.

    I've also asked the supplier of the interop code if they rely on the current user registry hive.  They have been quick to respond in the past, so I would expect an answer this week.

    If the main goal is to figure out whether different methods of launching scheduled items result in the current user hive being available, I should be able to write a very simple app to test that.  The app could easily be configured to optionally launch the com session.  I'll probably try that this weekend. That could nail down what combinations for SQL could work.  It might also reveal if a windows service or different scheduler can get around this problem with fewer side-effects.

    I'm going to mark this as answered, and will provide more details if I find anything noteworthy.  Thanks for the help!

     
  • Thursday, May 03, 2012 3:14 PM
    Moderator
     
     
    Sign In to Vote
    0

    No problem.  I'm a bit surprised that your org operates that way - but these things can be organic and evolve over time (or not!).  The situation you describe seems very OK for a small shop, but for larger places... these things would concern me (and I'm no expert who knows all the holes):

    You have MULTIPLE people in each department fully aware of credentials that give access to MULTIPLE resources.  Not a safe situation.  Should anyone have malicious intent... "department down!"

    You can still do account swapping using scripts - credentials and proxies are all manageable via T-SQL scripting.  It's actually very simple, and would only require that you change the credential part.  (The credential holds the domain account, the proxy points to the credential.)

    In fact, I'm really not sure why these scripts that change accounts do that... why not just have them change passwords on the account in use?  (Probably too complicated for you to explain, or "nobody knows, that's just how we do it." :) )

    You can make credentials for SQL accounts, but I'm talking about Windows accounts.

    But to finish off - credentials shouldn't be a problem - just use the departmental account, right?  It's only one more line to a script to change accounts in the credential, regardless of the number of jobs on the server.

    Good luck!

    Todd McDermid's Blog Talk to me now on

     
  • Thursday, May 03, 2012 5:50 PM
     
     
    Sign In to Vote
    0

    I didn't design the secuirty model (which is mostly evolutionary), and I think we all realize the potential issues. 

    Two accounts are used because a password change exercised across many servers can take long enough that some processes may not get a new password before tasks on other servers  have locked out the account for too many failed logins.  Yes, that COULD reinforce the argument for using credentials, but I think the problem would still exist if only a single account (per department) was used insteadof pairs. 

    The problem for the "one more line of script" per dept account/credential is mostly organizational.  Different groups (central infrastructure and many departments/projects) would need to coordinate in a way they don't today. It's a reasonable idea, and I will pursue it.