Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    Tampa, FL
    Posts
    4

    Question Unanswered: DTS Package for AS/400 to SQL Server

    Set up a DTS to transfer data from an AS/400 to SQL Server
    7 using a file DSN (Client Access ODBC Driver (32 bit)).
    It works great except that every time I run it, it asks me
    to log into the AS/400, even though I specified the ID and
    password in the DSN and DTS.

    Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

    I need to run this unattended, daily without a manual log
    in.

    Any ideas?

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS Package for AS/400 to SQL Server

    Yes, we had this exact same issue. There are several steps you will need to take to resolve it:

    1. Check the version of IBM Client Access that you are running on your SQL server. We installed V5R1 with the latest service pack. The latest service pack is critical because the original version of V5R1 has a bug that limits the size of the connection library to 9 characters (it's supposed to be 10).

    2. Make sure that your SQL Agent is running as a user account (preferably a Domain User). Make sure it is NOT running as the local system account. Verify this by right-clicking on the SQL Server Agent icon under the management folder of your SQL Server (in EM) and then clicking on Properties. The Service Startup Account information should be filled in.

    3. Set up an account on the AS/400 with the EXACT same name as the service account for SQL Agent. The password must also be EXACTLY the same (nb, at least on our AS/400, passwords are limited to 10 characters; the password for your SQL Agent account must conform to the same limitation).

    4. When you set up the ODBC DSN connection to the AS/400 on your SQL Server, be sure to specify to use Windows Authentication (no prompting).

    Doing all of this will ensure:

    1. That your server will connect to the AS/400 without the password prompt
    2. That rebooting your server will not impact the state of the connection.

    Bear in mind that if you change the password for the SQL Agent account, then you will need to change it on the AS/400 as well.

    I hope that this helps.


    Hugh Scott



    Originally posted by lossman
    Set up a DTS to transfer data from an AS/400 to SQL Server
    7 using a file DSN (Client Access ODBC Driver (32 bit)).
    It works great except that every time I run it, it asks me
    to log into the AS/400, even though I specified the ID and
    password in the DSN and DTS.

    Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

    I need to run this unattended, daily without a manual log
    in.

    Any ideas?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •