locked
Error on execution of command line tool that uses Excel RRS feed

  • Question

  • Hi Forum,

    I have a command line tool (.NET) that I want to execute from within a trigger.

    Inside this tool I open an Excel file. When I instantiate Excel I get the following COM error:

    The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID
    {00024500-0000-0000-C000-000000000046}
    and APPID
    Unavailable
    to the user NT SERVICE\MSSQLSERVER SID (S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003) from address LocalHost (Using LRPC).

    I changed the security  settings in component services (added the service account for the SQL server) and changed the permissions on all registry keys that contained the above GUID. Nothing seems to help.

    Can someone point me in the correct direction?

    TIA
    Alex

    Wednesday, October 16, 2013 12:45 PM

Answers

  • I have a command line tool (.NET) that I want to execute from within a trigger ... inside this tool I open an Excel file.

    Hello Alex,

    Just been curios: You really want to do that within a trigger? You know the transaction will be open the whole time until all is finished and the process will be blocked? If I think about how long it takes only to start Excel ...

    For such tasks I am using SQL Service Broker + External Activator; works like a charm, no such security issue, highly scalable and even an update on a few thousand rows is done within some milliseconds; the trigger sends only a message to the queue.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Naomi N Wednesday, October 16, 2013 4:40 PM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:40 AM
    Wednesday, October 16, 2013 2:30 PM
  • I would seriously rethink this entire process.  You do not want a trigger waiting on an external process to run before completing.

    I would suggest you use the trigger to "log" the rows which you want to operate on, and then have a job which runs every 1 minute to run the .Net program against the rows which are logged.

    • Proposed as answer by Naomi N Wednesday, October 16, 2013 4:40 PM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:40 AM
    Wednesday, October 16, 2013 4:37 PM

All replies

  • when you use SQL Server the user that actually do the job is NT SERVICE\MSSQLSERVER and not the user that you are login to the SQL Server. the NT SERVICE\MSSQLSERVER is the user that axecute the SQL sevrice.

    You can execute actions with impersonations(with a specific username and password). in your C# code You can use Process.Start with ProcessStartInfo. This lets you provide the credentials under which the process should run (The Excel process)


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Wednesday, October 16, 2013 1:07 PM
    Wednesday, October 16, 2013 1:00 PM
  • Thanks for the answer.

    Is it not possible to enable the MSSQLSERVER Service to do the job?

    Wednesday, October 16, 2013 1:59 PM
  • I have a command line tool (.NET) that I want to execute from within a trigger ... inside this tool I open an Excel file.

    Hello Alex,

    Just been curios: You really want to do that within a trigger? You know the transaction will be open the whole time until all is finished and the process will be blocked? If I think about how long it takes only to start Excel ...

    For such tasks I am using SQL Service Broker + External Activator; works like a charm, no such security issue, highly scalable and even an update on a few thousand rows is done within some milliseconds; the trigger sends only a message to the queue.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Naomi N Wednesday, October 16, 2013 4:40 PM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:40 AM
    Wednesday, October 16, 2013 2:30 PM
  • Theoretically yes,

    You can give permissions to the user SERVICE\MSSQLSERVER (operating/Files system permissions). you make this user get administrator permissions. THIS IS VERY BAD IDEA AND YOU SHOULD NOT DO IT!!! There is a reason why we use different users for different actions. 

    * make sure you check out Olaf's comment. This is absolutely true. You should make sure that you open separate thread using C# Asynchronous method if you don't have to wait for conformation from the action back to trigger, and if this is fit your architecture. this will make sure that the SQL Server do not wait for the task to finish and enable the SQL Server to close the transaction. Other way is to use external service that you write in C# like WCF that will run the action on the excel in the background.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Wednesday, October 16, 2013 4:51 PM
    Wednesday, October 16, 2013 4:28 PM
  • Thanks for that input, but there will be very little insert in that table (maybe 20 per week). Will it make sense to implement a Queue for that?

    What is an external activator?

    Alex

    Wednesday, October 16, 2013 4:36 PM
  • I would seriously rethink this entire process.  You do not want a trigger waiting on an external process to run before completing.

    I would suggest you use the trigger to "log" the rows which you want to operate on, and then have a job which runs every 1 minute to run the .Net program against the rows which are logged.

    • Proposed as answer by Naomi N Wednesday, October 16, 2013 4:40 PM
    • Marked as answer by Allen Li - MSFT Thursday, October 24, 2013 7:40 AM
    Wednesday, October 16, 2013 4:37 PM
  • What is an external activator?


    See Get Started With Using External Activator; it's a little Windows Service watching the notification queue; as soon as a message is received it start your application, the programming model is pretty easy, you get the parameters like queue name etc via command parameter.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 16, 2013 5:10 PM
  • "I changed the security  settings in component services (added the service account for the SQL server) and changed the permissions on all registry keys that contained the above GUID. Nothing seems to help. "

    "Is it not possible to enable the MSSQLSERVER Service to do the job?"

    THAT'S LIKE A CHEESE FOR A MOUSE (do you know what I mean?). C'mon...be carefull. I hope you are not managing sensitive data...

    Wednesday, October 16, 2013 11:19 PM