Running as Job in SQL Server 2005

[dpk136]

How do you set the finished built project up to run as a job in SQL Server. I have it set up, but i keep on getting errors for it. When i run it in Visual Studio, it works...

David Kuhn
------------------

http://www.davidpkuhn.com

 

[mrdenny]

There is something wrong with the Merant driver installed on the server. Try reinstalling the driver on the server. Does the Merant driver have any logs on the server that you can look at to point you to why it's failing?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpk136]

I don't think the merant driver has any logs on here. What if reinstalling it doesn't work?

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

Unfortunately, troubleshooting is all about trying one thing and if it doesn't work, going on to the next thing. If you don't try it, you can't rule it out as a possible problem.

On another note, what connection manager type do you have in SSIS for your Progress database? Can you go into the properties in Design and test the connection? Does it test successfully?




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

If i go into design, the whole thing runs with no problem. It is only when i run it as a job using sql server agent

David Kuhn
------------------

http://www.davidpkuhn.com

 

[mrdenny]

When you run it manually are you running it from the SQL Server or from your workstation?

If you are running it from your workstations console then it's running using the drivers on your workstation not the ones on the server.

Are there any config differences between your workstation and the server?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpk136]

I'm running it on the server. It is the same machine that is holding sql server...

David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

The weird thing is, if i run it in the SSIS development area, it works. If i open SQL Server Management Studio and connect to SSIS. If i import the SSIS package to the MSDB and run it there with all the same things that i'm running my job under, it will work. If i try to run this same Package that is stored in SQL Server, it does not work when run as a job under SQL Server agent. I don't know how it would have anything to do with the Merant driver if it works like this.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[mrdenny]

If you are connecting to the SQL Server and right clicking on the package and clickinge execute then the package is running on your workstation with your workstations settings and drivers.

When you run the job, the package is execute on the SQL Server with the SQL Servers settings and drivers.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpk136]

They are both on the same machine...i am logged into the server and running the stuff from there.

everything is being run on the server and that is where i'm at so i don't think i understand what you mean.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

I think i may have figured it out. The Progress database (the one i'm getting data from) is on another server. The SSIS Service is running as a network account. The SQL Server Agent Service is running as a local account. The local account would not have access to the Server that is holding the Progress database. So i think that if i just change the Log On account for the SQL Server Agent Service over to a network account, that will fix this issue.

What does everyone think???

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

Actually, you might be dead on. Having services that ran as local accounts instead of Domain User accounts killed a lot of functionality in SQL 2000, so it's entirely possible it's doing it here too.

Make sure the account is a regular Domain User account, though, and not a Domain Admin account.

Let us know if that solves the problem or not, too, if you wouldn't mind.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

That will probably be causing the problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpk136]

We changed the SQL Server Agent Service to someone who has network authentication and should have access the other server, the Network admin account. This did not let it work... If I open the command prompt and use the same cmd i am using in SQL Server using the dtexec.exe, it will work...both are the exact same code, it just looks like whoever the dtexec.exe is running as in the command window is allowed to access the other server.

Does anyone know what permissions you would need?

Is there anyway that there could be a work around. Like using windows task manager and running dtexec.exe (not sure how to do it, just a suggestion)..

Please help.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

When you go to the job step properties, what is the job step running as? SQL Agent Service Account or something else?

Also, who owns the job? I don't know if owner permissions makes a difference in SQL 2k5, but it does in SQL 2000.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

Well, we had SQL Server Agent running it and that didn't work, so we set up a Proxy, and used the same as the Owner and the person who created it...also the logon account for the SQL Server computer.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

What permissions in SQL does this person have? Have you tried setting the job owner to SA?

And you did say that you changed the SQL Server login to a domain user account, correct?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

I tried running as and setting the owner as SA, that did not work. The login is a Domain User account.

If the job is running as a Domain Admin account, would there be any problems with this.

The problem is not within SQL Server, it is with connecting to the other server to get the data off of the Progress database.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Catadmin]

The problem with using Domain Admin accounts is that if anyone hacked into the account, you've essentially given them the "keys to the kingdom". Most DBAs try to stay away from the Admin accounts.

It might be something like a specific like the user accounts ability to process tokens or something. See what granular permissions the actual User account has on the domain. You might also want to check with Microsoft at this point to see if it's a domain permissions issue.

Can you log in (not via SSIS, but via some other process) using that User's account? If so, I would say it definitely leaves the problem at the network/domain level.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dpk136]

Thanks,

I'll see what i can find. You have all been helpful, hopefully i find out something from Microsoft.

I'll let you know


David Kuhn
------------------

http://www.davidpkuhn.com