Solved

DTS Access denied on the server

Posted on 2001-09-05
16
495 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:oui_li
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 9

Expert Comment

by:miron
ID: 6459443
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
 
LVL 9

Expert Comment

by:miron
ID: 6459464
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
 
LVL 3

Expert Comment

by:krispols
ID: 6459789
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
 
LVL 2

Author Comment

by:oui_li
ID: 6471985
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
 
LVL 3

Expert Comment

by:krispols
ID: 6473408
What's happen if you try to create a package on the server itself. Have you got also an access denied?
0
 
LVL 9

Expert Comment

by:miron
ID: 6484425
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
 
LVL 2

Author Comment

by:oui_li
ID: 6487472
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
 
LVL 9

Expert Comment

by:miron
ID: 6487571
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 9

Expert Comment

by:miron
ID: 6487573
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
 
LVL 2

Author Comment

by:oui_li
ID: 6488393
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
 
LVL 9

Expert Comment

by:miron
ID: 6488440
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
 
LVL 9

Expert Comment

by:miron
ID: 6488448
There is no mentionig of cmd_exec or / and domain controller in this article what so ever.
0
 
LVL 2

Author Comment

by:oui_li
ID: 6494661
0
 
LVL 9

Expert Comment

by:miron
ID: 6495549
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
 
LVL 2

Accepted Solution

by:
oui_li earned 0 total points
ID: 6664081
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
 

Expert Comment

by:CleanupPing
ID: 9281650
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now