Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-21 : 09:13:18
|
| Joe writes "I have ran into a problem with DTS. First off, I created a DTS package on a remote system (my development machine, Win2K) and when I created it, I used the Server's machine name, say it's name is Altoids_1. So, on my dev system, in the DTS package, it uses Altoids_1 and works fine on my machine. I schedule it, and it does not work. The Server contains an error when it tries to run. Then, I develop the DTS package on the Server itself, and here is what happens on it: When you use Altoids_1 as the server you want to connect to (the local machine name) it will pop up the error " Cannot connect to server, access denied or server does not exist Server.Connection(Connect())", but if I use (local), I can connect to it. Well, I can connect to it when I am design mode that is. It will run when in design mode, but if I close the design mode, after I saved it, and try to run it, then it gives me the same error that it gave when I tried to use Altoids_1 in the design mode. Ok, the Hosts file has the ip address (just for example let's use 10.10.10.10).....so it has 10.10.10.10 Altoids_1 in the hosts file. I rebooted the server, and pinged the server itself, using 10.10.10.10, 127.0.0.1, and Altoids_1. It responded like it should. It was also able to ping other computers on the network. But when I try to run a DTS from the Server machine and when I try to use Altoids_1 in the designer(on the Server), it will not work. It gives me the Connection.OpenConnection(Connect()): server does not exist or access denied Error. I know that access is not denied, I am using the same Username and password that I used to get into the Enterprise Manager, so it is not Access Denied. What is causing this?" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-21 : 13:08:35
|
| You're moving down the wrong path. Your problem is likely bad permissions for the user that's running the job ( the sql server agent's credentials ). In general, permissions are usually the problem when a package executes normally when run through EM but fails when run by the SQL Server agent.setBasedIsTheTruepath<O> |
 |
|
|
JoeRS
Starting Member
3 Posts |
Posted - 2002-05-22 : 13:52:50
|
| Ok, I used sa this time to design and run this. It still does not work. I have not changed permissions on for sa, but I do know that the way that it was originally set up was with windows authentication, which is still one of the usernames. Will this cause a problem if I then try to run things as sa? Here is the exact error that it gives me when I schedule this jobProvider 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.again, this is using sa. If I reinstall SQL Server and set it to where it is SQL server authentication, would it solve this? I need to be able to schedule this so that will run every night, and am getting rather purturbed at it not working. Thanks! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-22 : 14:02:33
|
Let me step back and punt.I re-read your first reply and I'm confused about the following:quote:
When you use Altoids_1 as the server you want to connect to (the local machine name) it will pop up the error " Cannot connect to server, access denied or server does not exist Server.Connection(Connect())", but if I use (local), I can connect to it.
Am I right in reading the above that you can't reference the server name when using sql server tools on the server console? If that's the case then you've got larger issues than just DTS, I think.Can you run Query Analyzer from the server console and connect to 'Altoids_1'?setBasedIsTheTruepath<O> |
|
|
|
JoeRS
Starting Member
3 Posts |
Posted - 2002-05-22 : 16:54:11
|
| Yes, the Query Analyzer can connect using both (local) and Altoids_1 |
|
|
|
|
|
|
|
|
(local) vs. Machine Name