DTS and/or job autorization, anyone?
-
Hi there,
I wonder if anyone could point me to a resource where DTS autorization & security is covered in-depth. I have this situation and I can't reason why things are working and why NOT:
I have a DTSpackage on SQL1, which copies a table from SQL2 to SQL3. When I run this this package from my terminal as SA, it works fine. When I run the package from the server SQL1 as SA it works fine. Now beware!
When I schedule the package to a job on SQL1 and I start the job, the job fails with an 'server does not exists or access denied' message.
This message is generated in the copy_task of the package. This could mean, that the MSSQLServer on SQL2 has no autorization to connect to SQL3, or that SQLServerAgent on SQL1 is not autorized for SQL3. However, when I schedule a job on SQL1 with the statement 'select * from SQL2.northwind.dbo.categories' the job runs fine; dito for SQL3. And when I execute the package directly, it works ok. I have not tried this yet for SQL2, but by now my head is aching
, have to take a break.I think I have a ghood understanding on SQL Server security, but now I am doubting myself. Do I miss something here? Or is autorization in a mix of DTS packages and jobs really this nauseating?
Please, any hints apreciated.
Greetz,
Hans Brouwer -
does the sqlserver-service account or the sqlagent-service-account have network authorities to make the connection ?
When you run it using EM, it also uses the rights you - as a windows user - have.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Holdit,holdit,
Are you telling me, that when I start a job via EM on my workstation it runs under MY securityaccount, and not under the SQLServerAgent account of the machine involved?! If this is so,I'm really flabbergasted!
About the network-authorities: I don't know. Both services run under the Administrator accounts. But as I explained: another job addressing the remote server ran ok.
tnx for responding,
Greetz,
Hans Brouwer -
No, when you open the dts-package and run it, it uses your client-stuff.
I didn't notify in your previous post that you ran it OK with a sqlagent-job.
If another job runs fine, and this job is not, check the job-owner (or put it to sa).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
"However, when I schedule a job on SQL1 with the statement 'select * from SQL2.northwind.dbo.categories' the job runs fine; dito for SQL3. And when I execute the package directly, it works ok. "
In this case SQL2 is a linked server. Authority for linked servers are defined at linked server level where as DTS has its own connection objects.
Unless you only have defined a connection object for e.g. the local server and have defined linked servers for the others, access may be possible using the linked server 'select * from SQL2.northwind.dbo.categories', depending on the linked server security settings.
Try logging into the local sqlserver the same way as sqlagent does and then try to run the linked server query. (if your sqlagent runs sql-authenticated, also connect using sqlagents credentials)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply