DTS Access denied on the server

I have 2 SQL servers (2 machines), A test server and a prod server. Everything runs well on both. The only thing is I cannot access the dts packages on the prod server from the prod server. If I am physically at the prod server machine, and I try to access a dts with the enterprise manager, I get an access denied error. I cannot create a new one, run an existing one, or run a job that runs a DTS (which is what I ultimately want to do). I can register my test server on the prod server, but still cannot access the test server DTS packages. From my client machine and the test server, I can however register the prod server and access it's DST's. It is as if only my prod server cannot access any DTS package from any server.

My prod server happens to be a BDC, and is logged as Administrator in NT. The domain administrator has SA rights in my DB. I cannot access it with the SA account either. Everything else seems to be working but this.

HELP please,
William
LVL 2
oui_liAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
oui_liConnect With a Mentor Author Commented:
Just for everyone's info, I believe I have solved my problem. I kept my BD on the test server until I could free up an other server (jungled around servers and removed a proxy server). I used this spare server and installed it as a stand alone server (w2000 too). Now everything works fine. I think it might have been my BDC thing. Thanks for all the help...

Will
0
 
mironCommented:
You really need sa account, or other sysadmin account
with equale privilages to be able to run job or dts. I have a feeling that the SQL Server is run under domain admin and therefore will  run tasks as a domain admin since both articles are genuine windows processes and live under SQL GUI hood for reasons of ergonomy.

-- begin SQL
execute sp_addsrvrrolememer <account_name>, 'sysadmin'
-- end SQL
0
 
mironCommented:
after running sp_addsrvrrolemember on an account I was able to run applications needed sa account privileges, but during install, when sensitive registries needed write permissions I had to revert to sa. A different account worth a try, there is 75% potential for a positive result.

Thanks.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
krispolsCommented:
Is your SQL client version is 2k and your prod is 7.0? In this case just know that a 2k package couldn't be read or modify by a 7 version.
0
 
oui_liAuthor Commented:
miron,
the windows is logged under the domain administrator account. The sqlserver is running under an sqlserver winnt account, which has domain administrator rights as well as sysadmin rights in sql server.

If I create a new dts, I cannot save it in the server. I get access denied, even If I tell it to use the sa account!

krispols, client (enterprise manager...) is 2k and server (sql server) is 2k. Winnt is 4.0.
0
 
krispolsCommented:
What's happen if you try to create a package on the server itself. Have you got also an access denied?
0
 
mironCommented:
Sorry, I was not following the question. Considering that you can do the work from test server - remote machine you don't need to be bothered by this feature. If your test server is geographycally far, I would suggest getting another machine, hook it up to the network, locate it next to the prod server physically, add it to the domain, and install SQL Server client tools only. Make sure it is not a BDC.
0
 
oui_liAuthor Commented:
Miron, do you think it is the fact that the server is a BDC that is the problem? The test server and the prod server are in the same room. Keeping an extra machine is not an option. I need to be able to run jobs that launch DTS. Jobs are run on the server.

Thanks,
Will
0
 
mironCommented:
OK, if machines are in the same room, than running jobs from test servers seems to me a working ( good ) idea. I think you know how to do it from EM, and EM can run a job from any remote server, with exeption for server name "."  ( DOT ) or <local>, those should be replaced with name of SQL Server where job should run. As for the question on BDC, BDC will make no difference on its own. Something else is happening. You can test it by using securtity credentials of LOCAL System account to run SQL Server Agent on BDC and trying to run the same job / dts under sa account. That would cut out off of test domain accounts.
0
 
mironCommented:
OK, if machines are in the same room, than running jobs from test servers seems to me a working ( good ) idea. I think you know how to do it from EM, and EM can run a job from any remote server, with exeption for server name "."  ( DOT ) or <local>, those should be replaced with name of SQL Server where job should run. As for the question on BDC, BDC will make no difference on its own. Something else is happening. You can test it by using securtity credentials of LOCAL System account to run SQL Server Agent on BDC and trying to run the same job / dts under sa account. That would cut out off of test domain accounts.
0
 
oui_liAuthor Commented:
I think I have managed to parse thought microsoft articles and find a clue to my answer. Article Q129792, talks about login failures for CmdExec on domain controler. It mentions that to solve this problem, you rename the machine to the same name as the domain, or reinstall as a server in the domain. This seems to make sense to me. (also because the name of the machine and the domain are not the same).

Thanks for all the answers...
will
0
 
mironCommented:
oui_li

I just opened the KB article Q129792  at this URL

http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q129792

attached below is what I found. It would be really nice if you could post the URL address of the article you found.

Thanks.

-- BEGIN QUOTE

List of Third-Party Object-Oriented Books with ISBN

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual FoxPro for Windows, version 3.0

--------------------------------------------------------------------------------


SUMMARY
The following list of books and magazines cover the subject of Object Orientation Programming. Some of them focus on the areas of Analysis and Design.

NOTE: Many products mentioned in this article are published by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding the accuracy of these publications.

Aside from our own products, Microsoft does not support, endorse, or recommend any of the products listed below. We provide this information only as a convenience for our customers and do not provide warranties of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

The books are arranged alphabetically by title.



MORE INFORMATION

Title:     Code Complete
Author:    Steve McConnell
Publisher: Microsoft Press
ISBN:      1-55615-484-4

Title:     Design Patterns: Elements of Reusable Object-Oriented Software
Author:    Bamma, Helm, Johnson, and Vlissides
Publisher: Addison-Wesley
ISBN:      0-201-63361-2

Title:     Designing Object-Oriented Software
Author:    Rebecca Wirfs-Brock, Brian Wilkerson, and Lauren Wiener
Publisher: Prentice-Hall Inc.
ISBN:      0-13-629825-7

Title:     The C++ Programming Language, 2nd. ed.
Author:    Bjarne Stroustrup
Publiser:  Addison-Wesley
ISBN:      0-201-53992-6

Title:     The Microsoft Guide to C++ Programming
Author:    Kaare Christian
Publisher: Microsoft Press
ISBN:      1556153945

Title:     Object Data Management: Object-Oriented and Extended Relational
           Database Systems
Author:    R.G.Cattell
Publisher: Addison-Wesley
ISBN:      0-201-53092-9

Title:     Object Magazine
Author:    N/A
Publisher: Sigs Publications
ISBN:      N/A

Title:     Object Oriented Analysis and Design with Applications, 2nd Ed.
Author:    Grady Booch
Publisher: Benjamin/Cummings Publishing
ISBN:      0-8053-5340-2

Title:     Object-Orientation: Concepts, Languages, Databases,
           User Interfaces
Author:    Setrag Khoshafian and Razmik Abnous
Publisher: Wiley
ISBN:      0-471-51801-8

Title:     Object-Oriented Databases: Technology, Applications,
           and Products
Author:    Bindu R. Rao
Publisher: McGraw-Hill Inc.
ISBN:      0-07-051279-5

Title:     Object-Oriented Information Systems: Planning and Implementation
Author:    David A. Taylor, Ph.D.
Publisher: Wiley
ISBN:      0-471-54364-0

Title:     Object Oriented Methods
Author:    Ian Graham
Publisher: N/A
ISBN:      0201593718

Title:     Object Oriented Program Design
Author:    Mark Mullin
Publisher: N/A
ISBN:      0201517221

Title:     Object Oriented Programming
Author:    Peter Coad and Jill Nicola
Publisher: N/A
ISBN:      013032616X

Title:     Object-Oriented Software Engineering: A Use Case Driven Approach
Author:    Ivar Jacobson
Publisher: Addison-Wesley
ISBN:      0-201-54435-0

Title:     Object-Oriented System Design: An Integrated Approach
Author:    Edward Yourdon
Publisher: Yourdon Press Computing Systems
ISBN:      0-13-636325-3

Title:     Object Oriented Technology: A Manager's Guide
Author:    David A. Taylor
Publisher: Addison-Wesley
ISBN:      0-201-56358-4

Title:     Object Technology in Application Development
Author:    Daniel Tkach and Richard Puttick
Publisher: Benjamin/Cummings Publishing
ISBN:      0-8053-2572-7

Title:     Practical Applications of Object-Oriented Techniques
           to Relational Databases
Author:    Donald K. Burleson
Publisher: Wiley
ISBN:      0-471-61225-1

Title:     The Tao of Objects
Author:    Gary Entsminger
Publisher: N/A
ISBN:      1-55851-412-0
 

-- END QUOTE
0
 
mironCommented:
There is no mentionig of cmd_exec or / and domain controller in this article what so ever.
0
 
oui_liAuthor Commented:
0
 
mironCommented:
per article "sa" login solves the trouble. I also would verify the BDC workload with SQL Server shut down. In a 40 machine config 90 mhz pentium 386 averaged 0% CPU utilization configured as PDC in Active Directory for Windows 2000.

Thanks.
0
 
CleanupPingCommented:
oui_li:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.