none
How to run .dtx job that query from another database and dumps it on a network drive? RRS feed

  • Question

  • If I execute the .dtx package it executes fine; however, when I try to start a job it failed. The job is setup to "Run as" a Proxy user that has DataRead access on the other database (db12\reports). The error is something like this:

    05/23/2019 13:39:43,Proxyuser - myApp,Error,,DB11,Proxyuser - myApp,,,The job failed.  The Job was invoked by User myDomain\MyUserName.  The last step to run was step 1 ( threat table CSV dump).,00:00:00,0,0,,,,0
    05/23/2019 13:39:43,Proxyuser - myApp,Error,1,DB11,Proxyuser - myApp, threat table CSV dump,,Executed as user: myDomain\myApp. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.6259.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved. 

    Do I need to link the server (db12/reports) to this DB11 for the job to run?

    <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica} </style>
    Thursday, May 23, 2019 7:28 PM

Answers

  • Finally what made it work is, I have to add the myDomain\MyUserName to the Login with sysadmin role on the DB11 server.

    • Marked as answer by Charlie2 Friday, June 28, 2019 2:17 PM
    Friday, June 28, 2019 2:17 PM

All replies

  • Hi Charlie2,

    Thanks for posting here.

    Per the error message above, I couldn't capture any key information from it. Could you please provide more detailed information of error?  And what operation are you doing?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 24, 2019 5:46 AM
    Moderator
  • The .dts package is being run on db11 as a scheduled job. The package goes out to db12 and query some data then dump it to a .csv file on another network drive. DB12 is already listed/added to the Linked Servers. The user or account that runs the job is has data reader access on the database in db12 and also read/write/modify access on the network drive/folder. However, this job keeps failing with the error above. When I tried to execute the actual package on Integration Services Catalogs, it works fine. The issue is the job. I'm not sure where else to look for the issue why it keeps failing. 

    Error:

    Environment reference Id: NULL.  Description: Login failed for user 'myDomain\myApp'.
    • Edited by Charlie2 Tuesday, May 28, 2019 12:43 PM
    Tuesday, May 28, 2019 12:35 PM
  • Hi Charlie2,

    Per the error message above, have you granted SQL Server Agent Fixed Database Roles permission to that user?

    SQL Server Agent Fixed Database Roles

    Grant permissions to users to create jobs in Sql Server

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 30, 2019 9:55 AM
    Moderator
  • I do not see the SQLAgentUserRole as an option in the "Database role membership for".
    Thursday, May 30, 2019 3:09 PM
  • I do not see the SQLAgentUserRole as an option in the "Database role membership for".

    In SSMS, turn to System Databases-> msdb->security-> Roles -> Database Roles -> SQLAgentUserRole, right click properties, then  you would view the members of this role.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 31, 2019 7:29 AM
    Moderator
  • I clicked the Properties, General and in the "Members of this role" section, I clicked on the "Add.." button to add the myDomain\MyUserName user. I clicked the "Browse..." button and in the "Browse for Objects" window, I do not see the myDomain\MyUserName as an available matching objects. So, what do I do next?
    Friday, May 31, 2019 4:34 PM
  • I clicked the Properties, General and in the "Members of this role" section, I clicked on the "Add.." button to add the myDomain\MyUserName user. I clicked the "Browse..." button and in the "Browse for Objects" window, I do not see the myDomain\MyUserName as an available matching objects. So, what do I do next?

    Is the server in the same domain "myDomain"? Can the login myDomain\MyUserName  log into the server?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 4, 2019 9:48 AM
    Moderator
  • Is the server in the same domain "myDomain"? Can the login myDomain\MyUserName  log into the server?

    Yes, it's in the same domain and I can login with myDomain\MyUserName to db12.
    Wednesday, June 5, 2019 12:18 PM
  • Is the server in the same domain "myDomain"? Can the login myDomain\MyUserName  log into the server?

    Yes, it's in the same domain and I can login with myDomain\MyUserName to db12.

    Why not follow the second link to start your task, which is submitted in my previous reply.

    SQL Server Agent Fixed Database Roles

    Grant permissions to users to create jobs in Sql Server

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 6, 2019 6:48 AM
    Moderator
  • Finally what made it work is, I have to add the myDomain\MyUserName to the Login with sysadmin role on the DB11 server.

    • Marked as answer by Charlie2 Friday, June 28, 2019 2:17 PM
    Friday, June 28, 2019 2:17 PM