DTSRUN privileges?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Brian Peasland

    #1

    DTSRUN privileges?

    I am trying to get a DTS package to be run from the command line with
    the dtsrun utility. The DTS package is stored in the database. The user
    I supply is a user in the database. I get an error stating "SQL Server
    does not exist or access denied." It looks to me like the SQL Server
    instance does exist because it tries to start the package. I get
    "DTSRun: Executing". If I put in a server that is non-existent, I do not
    get that message. I also know that my username and password are correct.

    Here is output from my attempt to run dtsrun for my DTS pkg (server,
    user, password change to protect my db security):

    C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
    DTSRun: Loading...
    DTSRun: Executing...
    DTSRun OnStart: DTSStep_DTSExec uteSQLTask_1
    DTSRun OnError: DTSStep_DTSExec uteSQLTask_1, Error = -2147467259 (80004005)
    Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
    does not exist or access denied.
    Error source: Microsoft OLE DB Provider for SQL Server
    Help file:
    Help context: 0
    Error Detail Records:
    Error: -2147467259 (80004005); Provider Error: 17 (11)
    Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
    does not exist or access denied.
    Error source: Microsoft OLE DB Provider for SQL Server
    Help file:
    Help context: 0
    DTSRun OnFinish: DTSStep_DTSExec uteSQLTask_1
    DTSRun: Package execution complete.


    I suspect that my user I am connecting to the database with does not
    have privileges to execute the DTS package. I cannot determine, from
    BOL, what privs I need to grant to this user to let them execute this
    package. Any ideas?


    TIA,
    Brian




    --
    =============== =============== =============== =============== =======

    Brian Peasland
    oracle_dba@nosp am.peasland.net


    Remove the "nospam." from the email address to email me.


    "I can give it to you cheap, quick, and good.
    Now pick two out of the three" - Unknown
  • DickChristoph

    #2
    Re: DTSRUN privileges?

    Hi Brian.

    The fact that it says:[color=blue]
    > DTSRun: Loading...
    > DTSRun: Executing...[/color]

    indicates your user can run DTSRUN. What is more likely is that user cannot
    connect to the servers and databases named in the package.

    Try going into Query analyzer and connect to the server as that user and see
    if you can connect to the database and select from some of its tables.

    --
    -Dick Christoph

    "Brian Peasland" <oracle_dba@nos pam.peasland.ne t> wrote in message
    news:IyqxzH.BFw @igsrsparc2.er. usgs.gov...[color=blue]
    >I am trying to get a DTS package to be run from the command line with the
    >dtsrun utility. The DTS package is stored in the database. The user I
    >supply is a user in the database. I get an error stating "SQL Server does
    >not exist or access denied." It looks to me like the SQL Server instance
    >does exist because it tries to start the package. I get "DTSRun:
    >Executing". If I put in a server that is non-existent, I do not get that
    >message. I also know that my username and password are correct.
    >
    > Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
    > password change to protect my db security):
    >
    > C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
    > DTSRun: Loading...
    > DTSRun: Executing...
    > DTSRun OnStart: DTSStep_DTSExec uteSQLTask_1
    > DTSRun OnError: DTSStep_DTSExec uteSQLTask_1, Error = -2147467259
    > (80004005)
    > Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
    > not exist or access denied.
    > Error source: Microsoft OLE DB Provider for SQL Server
    > Help file:
    > Help context: 0
    > Error Detail Records:
    > Error: -2147467259 (80004005); Provider Error: 17 (11)
    > Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
    > not exist or access denied.
    > Error source: Microsoft OLE DB Provider for SQL Server
    > Help file:
    > Help context: 0
    > DTSRun OnFinish: DTSStep_DTSExec uteSQLTask_1
    > DTSRun: Package execution complete.
    >
    >
    > I suspect that my user I am connecting to the database with does not have
    > privileges to execute the DTS package. I cannot determine, from BOL, what
    > privs I need to grant to this user to let them execute this package. Any
    > ideas?
    >
    >
    > TIA,
    > Brian
    >
    >
    >
    >
    > --
    > =============== =============== =============== =============== =======
    >
    > Brian Peasland
    > oracle_dba@nosp am.peasland.net
    > http://www.peasland.net
    >
    > Remove the "nospam." from the email address to email me.
    >
    >
    > "I can give it to you cheap, quick, and good.
    > Now pick two out of the three" - Unknown[/color]


    Comment

    • Dan Guzman

      #3
      Re: DTSRUN privileges?

      To add to Dick's response, be aware that the DTS package runs on same
      machine as DTSRUN. You'll get these errors if you run the package on a
      client machine and have specified SQL Server name 'local' in the DTS
      connection.

      --
      Hope this helps.

      Dan Guzman
      SQL Server MVP

      "Brian Peasland" <oracle_dba@nos pam.peasland.ne t> wrote in message
      news:IyqxzH.BFw @igsrsparc2.er. usgs.gov...[color=blue]
      >I am trying to get a DTS package to be run from the command line with the
      >dtsrun utility. The DTS package is stored in the database. The user I
      >supply is a user in the database. I get an error stating "SQL Server does
      >not exist or access denied." It looks to me like the SQL Server instance
      >does exist because it tries to start the package. I get "DTSRun:
      >Executing". If I put in a server that is non-existent, I do not get that
      >message. I also know that my username and password are correct.
      >
      > Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
      > password change to protect my db security):
      >
      > C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
      > DTSRun: Loading...
      > DTSRun: Executing...
      > DTSRun OnStart: DTSStep_DTSExec uteSQLTask_1
      > DTSRun OnError: DTSStep_DTSExec uteSQLTask_1, Error = -2147467259
      > (80004005)
      > Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
      > not exist or access denied.
      > Error source: Microsoft OLE DB Provider for SQL Server
      > Help file:
      > Help context: 0
      > Error Detail Records:
      > Error: -2147467259 (80004005); Provider Error: 17 (11)
      > Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
      > not exist or access denied.
      > Error source: Microsoft OLE DB Provider for SQL Server
      > Help file:
      > Help context: 0
      > DTSRun OnFinish: DTSStep_DTSExec uteSQLTask_1
      > DTSRun: Package execution complete.
      >
      >
      > I suspect that my user I am connecting to the database with does not have
      > privileges to execute the DTS package. I cannot determine, from BOL, what
      > privs I need to grant to this user to let them execute this package. Any
      > ideas?
      >
      >
      > TIA,
      > Brian
      >
      >
      >
      >
      > --
      > =============== =============== =============== =============== =======
      >
      > Brian Peasland
      > oracle_dba@nosp am.peasland.net
      > http://www.peasland.net
      >
      > Remove the "nospam." from the email address to email me.
      >
      >
      > "I can give it to you cheap, quick, and good.
      > Now pick two out of the three" - Unknown[/color]


      Comment

      • Brian Peasland

        #4
        Re: DTSRUN privileges?

        DickChristoph wrote:[color=blue]
        > Hi Brian.
        >
        > The fact that it says:[color=green]
        >> DTSRun: Loading...
        >> DTSRun: Executing...[/color]
        >
        > indicates your user can run DTSRUN. What is more likely is that user cannot
        > connect to the servers and databases named in the package.
        >
        > Try going into Query analyzer and connect to the server as that user and see
        > if you can connect to the database and select from some of its tables.
        >[/color]

        The user can connect to the database and query tables just fine with QA.
        This is not a Windows user, but rather one that is authenticated in the
        database.


        Thanks,
        Brian

        --
        =============== =============== =============== =============== =======

        Brian Peasland
        oracle_dba@nosp am.peasland.net


        Remove the "nospam." from the email address to email me.


        "I can give it to you cheap, quick, and good.
        Now pick two out of the three" - Unknown

        Comment

        • Brian Peasland

          #5
          Re: DTSRUN privileges?

          Dan Guzman wrote:[color=blue]
          > To add to Dick's response, be aware that the DTS package runs on same
          > machine as DTSRUN. You'll get these errors if you run the package on a
          > client machine and have specified SQL Server name 'local' in the DTS
          > connection.
          >[/color]

          I do plan on running the DTSRUN utility from a client machine, but I
          have stated the SQL Server's name in the /S switch. I did not use
          'local' or '.' for the server name since I know the package is not local
          to the client.

          Thanks,
          Brian

          --
          =============== =============== =============== =============== =======

          Brian Peasland
          oracle_dba@nosp am.peasland.net


          Remove the "nospam." from the email address to email me.


          "I can give it to you cheap, quick, and good.
          Now pick two out of the three" - Unknown

          Comment

          • DickChristoph

            #6
            Re: DTSRUN privileges?

            Hi Brian

            Can you open the package and look at the connections. Dan was suggesting
            that one of these connections might be "(local)". also I would add to my
            previous comment, the username specified for these connections might lack
            the privileges to connect to the database.

            Also if to open the package and execute it from the designer you can see
            which step is crashing.

            --
            -Dick Christoph
            "Brian Peasland" <oracle_dba@nos pam.peasland.ne t> wrote in message
            news:Iyrtp9.25D @igsrsparc2.er. usgs.gov...[color=blue]
            > Dan Guzman wrote:[color=green]
            >> To add to Dick's response, be aware that the DTS package runs on same
            >> machine as DTSRUN. You'll get these errors if you run the package on a
            >> client machine and have specified SQL Server name 'local' in the DTS
            >> connection.
            >>[/color]
            >
            > I do plan on running the DTSRUN utility from a client machine, but I have
            > stated the SQL Server's name in the /S switch. I did not use 'local' or
            > '.' for the server name since I know the package is not local to the
            > client.
            >
            > Thanks,
            > Brian
            >
            > --
            > =============== =============== =============== =============== =======
            >
            > Brian Peasland
            > oracle_dba@nosp am.peasland.net
            > http://www.peasland.net
            >
            > Remove the "nospam." from the email address to email me.
            >
            >
            > "I can give it to you cheap, quick, and good.
            > Now pick two out of the three" - Unknown[/color]


            Comment

            Working...