Problem with DTS

Hi there.
I have the following environment:
Windows 2000 Server SP4, SQL Server 2000 Sp3.
There are some DTS which have been creating some time ago. They are
scheduled are have been runngin until now. Now the jobs fail. Can't say
what has been changed. Haven't been responsible for those systems and
those who are say they didnt change anything (same story as always ;)
...)
The DTS have been created when connecting with an Windows Domain
Account. When I connect with this Windows Account I can open and run
those DTS. But when login as local Admin directly on the server and open
Enterprise Manager with the local Admin User and try to open the DTS I
get an error:
Error Source : Microsfot OLE DB Provider for SQL Server
Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
not exist or access is denied.
The DTS is a Local Package (I have some Meta Data Service Packages ...
same there).
The simlest DTS has two SQL Server connection using a SQL User. First a
Delete on the first connection is made, then some data is copied from
connection2 to connection1.
As I said, when I login with Domain User Account I can open it, edit it,
run it .. no problems. But with the local Admin of the SQL Server ...
error.
SQL Server and SQL Agent are running as local System Accounts.
Why am I getting this error? I think it cant be the Connections in the
DTS, because there SQL Users are used which exists and work.
The Local Administartor is also a SQL System Admin, as are the Domain
Admins.
Any hints? Anyone knows where I could investigate further?
mfg
Marc Eggenberger
Someone removed the BUILTIN\Administrators login from SQL Server, as they
should have, and have replaced it with the [NT AUTHORITY\SYSTEM] group;
however, they should have followed proper Change Control policies. I'm
surprised you haven't been having more problems.
At the VERY least, you should be running the MSSQL and SQLAGENT service
under local accounts, NOT SYSTEM. And, if you can, run the services under a
Domain Account.
So you know, SYSTEM = [NT AUTHORITY]\SYSTEM and is not a Local User but a
GLOBAL AD GROUP. The members of this group are all the <Machine Name>$
server accounts that are created whenever you add a server as a Domain Member.
Next, if you use a Domain Account for the services, you can grant explicit
privleges to those accounts on other server network shares.
As you know, DTS runs in the process space and under the security context of
the user that executes the DTSRun command. When the package runs as a job,
it uses the server's memory space and the SYSTEM account because that is what
the SQLAgent is running as and where it is running.
You can not log in as SYSTEM and SYSTEM and Local Administrator are two
different security contexts. Another good reason to have an explicitly
defined account to run the SQL Server services under: you can log in to the
server as that user and "see" what the services would see when they log in.
Sincerely,
Anthony Thomas
"Marc Eggenberger" wrote:

> Hi there.
> I have the following environment:
> Windows 2000 Server SP4, SQL Server 2000 Sp3.
> There are some DTS which have been creating some time ago. They are
> scheduled are have been runngin until now. Now the jobs fail. Can't say
> what has been changed. Haven't been responsible for those systems and
> those who are say they didnt change anything (same story as always ;)
> ...)
> The DTS have been created when connecting with an Windows Domain
> Account. When I connect with this Windows Account I can open and run
> those DTS. But when login as local Admin directly on the server and open
> Enterprise Manager with the local Admin User and try to open the DTS I
> get an error:
> Error Source : Microsfot OLE DB Provider for SQL Server
> Error Description: [DBNETLIB]ConnectionOpen(Connect()).]SQL Server does
> not exist or access is denied.
> The DTS is a Local Package (I have some Meta Data Service Packages ...
> same there).
> The simlest DTS has two SQL Server connection using a SQL User. First a
> Delete on the first connection is made, then some data is copied from
> connection2 to connection1.
> As I said, when I login with Domain User Account I can open it, edit it,
> run it .. no problems. But with the local Admin of the SQL Server ...
> error.
> SQL Server and SQL Agent are running as local System Accounts.
> Why am I getting this error? I think it cant be the Connections in the
> DTS, because there SQL Users are used which exists and work.
> The Local Administartor is also a SQL System Admin, as are the Domain
> Admins.
> Any hints? Anyone knows where I could investigate further?
>
> --
> mfg
> Marc Eggenberger
>