January 16, 2004 at 5:13 am
I have been battling this problem for over a month now and can't seem to get a resolution. PLEASE HELP!!!
I'm running SQLServer 2000 on my Windows 2k Server. I have a client with a database on the server. He is running Enterprise Manager to remotely connect to the server db and is logging on with the same logon as his db.dbo.
On his machine, we are executing a DTS package which imports tables from his local FoxPro data files and dumps them into tables in his SQL db. (tables are deleted/created/data imported)
The package works fine when executed directly on his local machine in EM.
The problem is when we schedule the package to run daily. When attempting to execute the scheduled job in SQL Agent, it fails every time.
(FYI, DEDICATED is the server name)
When executing the job, the error is:
Executed as user: DEDICATED\Administrator. DTSRun: Loading... 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. Process Exit Code 1. The step failed.
I checked the services.
SQLSERVER was running under .\administrator
SQLSERVERAGENT was running under LocalSystem
I updated so they both run under ".\administrator"
Under the SQL Server Group/ LOCAL properties security tab, I have "SQL Server and Windows" authentication checked. Below that under "Startup Service Account", I have "This account" checked and account is ".\administrator" (would not allow me to use just administrator)
Under SQL Server Agent properties, Service Startup Account is also ".\administrator"
Under it's Connection tab "Use SQL Server Authentication" is checked and the ID is "administrator"
January 16, 2004 at 6:59 am
What privilege the ".\administrator" has in SQL Server? SQL Server Agent is running under local system account that can't access the FoxPro data which is in client machine. You have to run SQL Server Agent with a domain user account who has permissions to access the FoxPro database. The domain user account has also need to be granted proper privilege to access the SQL Server database.
January 16, 2004 at 7:10 am
The DEDICATED/administrator has sysadmin permission on the server. I realize that the job cannot be executed on the server because I would not have access to the client's local FoxPro db.
On the client's machine, the administrator is "marc". The Enterprise Manager login is "alan" which is the same login as the dbo on the db we are accessing. It doesn't make sense that I would have to create a "marc" login on the database.
The user "alan" has the following privileges on the database:
public and db_owner
What else do I need?
January 16, 2004 at 7:16 am
It seems the SQL Server Agent account has to have permissions to access foxpro database like "alan".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply