Unanswered: SQL 2000 DTS: Embed UserName/Password to access file on non-SQL server
I 'completed' a DTS package which uses a file on a non-SQL server. I am about to put this into production, so it runs automatically each evening. but just realized that it won't work unless it signs onto the source file's Windows 2000 server. It works for me when I'm testing it because I am always logged on the the Windows 2000 server.
I have been trying to figure out which DTS task I need to use to establish a connection by embedding my UserName and Password. Any tips woud be apprecitaed.
If I understand the question correctly, you are trying to access a data source (a file; what kind of file? .csv, .txt, .xls, other?) on a non-SQL, Windows server (Windows what? NT, 2000, 2003?). Your DTS package does not work in production because it cannot access (does not have privileges to) the source file.
The solution that I'm pretty certain will work would be to grant read privileges on the file to the service account used for SQL Agent (if the DTS package is executed within a task). This assumes that the SQL Agent account is a Domain User account (recommended).
If the SQL Agent service account is local to the SQL machine, then you will likely need to create a pass-through authentication scenario, by creating an identical account on the source Windows server (same user name, identical password). Then grant this new user access to the file in question.
If the SQL Agent service account is LocalSystem, you may have other issues. I can't remember what the work-around for that is. To see what kind of user you have set up for SQL Agent, right click on SQL Agent and look at the properties.
PS. If it's an MS Access file with MS Access security enabled, there's a whole other step involved, but it is doable.