SQL Server Performance

Network not found, File access from Networ Problem

Discussion in 'SQL Server DTS-Related Questions' started by jaswinder1974, Aug 11, 2004.

  1. jaswinder1974 New Member

    Guys,<br /><br />I thought I new how to run DTS packages on server that requires files from the network, but I have been constantly running into this problem.<br /><br />Basically, I am trying to import a file into a sql server table from a network shared space. I am using UNC for referencing the file. If I run this package from my PC or from the server, it works OK. But when I schedule this package to run at a certain time, it fails to execute with error:<br /><br /><i>Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider<br />Step Error Description:Error opening datafile: The network name cannot be found.<br /><br />Step Error code: 80004005<br />Step Error Help File<img src='/community/emoticons/emotion-2.gif' alt=':D' />TSFFile.hlp<br />Step Error Help Context ID:0</i><br /><br />Yes, I am running SQLServerAgent service under my NT Login account, that have access to the share on the network, but SQLSetver is still running under 'Local System Account'. Do you think I need to run SQL Server under my account as well, or is it something else causing this problem?<br /><br />Your response will be much appreciated.<br /><br />waiting...<br />jas....<br /><br />Jaswinder Singh<br />A K ITech Limited<br />London, UK
  2. satya Moderator

    The SQLAgent & MSSQLSErver must be running under domain login account with administrative privileges.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. jaswinder1974 New Member

    Thanks for the quick response Satya. I was very delighted to see your quick reply.

    But my problem is still not solved. unfortunatly I am trying to read from files located on a Novell Netware share drive. I have tested my package for a NT share, it works fine there, but not on the Novell Netware share drive, the one I need.

    waiting for your reply.
    jas....

    Jaswinder Singh
    A K ITech Limited
    London, UK
  4. Twan New Member

    Hi ya,

    SQL Agent and MSSQL do not want to be given admin rights, it is way too privileged. However... In theory as long as the job you're trying to run is owned by a sysadmin user, and MSSQL runs under a domain account with rights on that Novell Share, and when you log into the server with that same account you can access the Novell Share, then it ought to work... (only use enterprise manager to change the server's account)

    perhaps use something like ntfilmon from sysinternals to see what may be going on?

    you could also try running a ping and/or dir step within a job to try to narrow the problem down? Configure the job to put the output into a file so you can see it.

    Cheers
    Twan
  5. jaswinder1974 New Member

    Twan,

    Probably I didn't make myself very clear.

    It works OK as long the files are on a NT share, problem is only with being files on a Novell Share. And I could run it successfully if I run the DTS package manually, problem is only when it is scheduled to run Or I run the job manually.

    Could you please shed some light on 'ntfilemon from sysinternals'?

    Thanks

    Jaswinder Singh
    A K ITech Limited
    London, UK
  6. satya Moderator

  7. Argyle New Member

  8. jaswinder1974 New Member

    Guys,

    Its nothing to do with SQLAgent or scheduled job.

    The problem is - if I run the dts package manually it works fine, but if run it programatically (using extended stored procs) it does not. SQLServer service is running under a domain account which has got permissions on that Novell share.

    any ideas????? I am feeling stuck, there must be some sort of magic button that I have to press, because it works manually.

    Looks like this is a big challenge for SQL Gurus/DBAs!!!!!!!!!!!!!!!!

    Jaswinder Singh
    A K ITech Limited
    London, UK
  9. satya Moderator

    Confirm the privileges for account used in MSSQLSErver & MSSQLAgent services.
    Try to ping SQL Server from Novell share in order to see any network issue exists.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Argyle New Member

    If you login with the sql account, connect with windows authentication to EM, can you still execute it manually?
  11. jaswinder1974 New Member

    Argyle,

    Thanks for your immense interest. I will try that.

    I will be

    1. Registering SQL Server on my PC using my Windows Credentials
    2. Connect to SQL Server using Query Analyser using sa/sql logins.
    3. Try executing the Package using extended stored procs.

    Is that what you mean?


    Jaswinder Singh
    A K ITech Limited
    London, UK
  12. Argyle New Member

    No.

    1.
    Logon to the server with the sql service account (CTRL-ALT-DEL logon).
    2.
    Open query analyzer and connect with Windows authentication.
    3.
    You are now connected with the sql service account (for example MYDOMAINsqlservice). Run the SP.
    4.
    Check for errors

    Also running the DTS package manually from your own PC is quite different than running it from the server.
  13. jaswinder1974 New Member

    No, It did not work, got same error message 'Network name can not be found'.

    It is failing because I am importing from a Novell Share drive. It works fine if I import from a NT Share drive.



    Jaswinder Singh
    A K ITech Limited
    London, UK
  14. satya Moderator

    Check the following :

    -Registry key - HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesLanmanServerParameters
    -Ensure AutoShare on Novell is enabled

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. Argyle New Member

    You said it worked when executed from your PC. Could it be that the server can not find the novell server by name but your PC can (and both the server and your PC can find the NT server with the other share)?

    Can the server resolve the novell server name? Can you ping it by name and/or IP?
  16. jaswinder1974 New Member

    Satya,

    I know it looks like a silly question but where do I need to check to check this register key, On SQL Server box or on Novell machine.

    (I couldn't see it on SQL Server box)

    Jaswinder Singh
    A K ITech Limited
    London, UK
  17. satya Moderator

  18. jaswinder1974 New Member

    Argyle,

    It does work on the server as well, but only when I log on to the server and execute the package manually (right click Package name and select execute.)



    Jaswinder Singh
    A K ITech Limited
    London, UK
  19. Argyle New Member

    Could you post what the statment looks like in the SP and what your path looks like. I find it strange that it says "The network name cannot be found" when one user is logged in but not for another.
  20. jaswinder1974 New Member

    Argyle,

    here is the line


    EXEC master.dbo.xp_cmdshell 'dtsrun /S sldn057 /U sa /P password/N jasDTSUser'

    The above line, it does not matter if I execute it on the client or server, it never works.

    As you might have noticed I am trying to run on the server.

    Jaswinder Singh
    A K ITech Limited
    London, UK
  21. jaswinder1974 New Member

    here is the path:

    \vs1-60ve-cr21CR_GROUP11GROUPSBankOneIRDMigrationDevelopmentJasjasTestFilesjasUser.txt

    Jaswinder Singh
    A K ITech Limited
    London, UK
  22. Argyle New Member

    Does it work if you run it from a command prompt and not through sql server and xp_cmdshell like this:
    dtsrun /S sldn057 /U sa /P password /N jasDTSUser

    If it works does it work when logged in on the server as administrator and when logged in as the sql server account?

    Also do some tests using the /E switch instead of /U and /P to use windows authentication if the above doesn't work.
  23. jaswinder1974 New Member

    No, It does not work from command prompt on the server. I tried it with /E switch. I was logged in using the same domain account that I SQL Server Service is using to run under.

    Jaswinder Singh
    A K ITech Limited
    London, UK
  24. jaswinder1974 New Member

    Sorry Guys,

    I made a mistake here. Actually It never works on the SQL Server box. As DTS package is trying to import a file from a Novell Server. Novell Server needs to authenticate the user. The domain account that SQL Server is running under, has permissions to see the Novell Server. Now How do I authenticate on Novell Server programmatically before I can access the file????

    Jaswinder Singh
    A K ITech Limited
    London, UK
  25. Argyle New Member

Share This Page