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 |
|
dogli
Starting Member
32 Posts |
 Posted - 2002-09-25 : 15:51:32
|
| Hi ,I have a login ID and password to connect to remote Oracle Server. I need to set up a linked server on local SQL server box with these login informaiton. But there are serval other developers have the same access right(sa) with me to access the SQL Server. How can I prevent those developers from access my linked Oracle server? The Oracle server contains very very important company information which can not be seen by any other people. Thanks for any idea and suggestions |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 16:15:20
|
| Well, the big problem is the fact that multiple users are logging in under sa. For one thing, that's bad. Every system administrator should have their own, separate login. They can be granted the same rights as sa, but they should not share their logins or password. That way if someone runs rampant, you can cut them off, or at least trace their activity. If everyone logs in as sa you'll have no way of knowing.BTW, make sure you are NOT using the sa login for any applications. This is a huge NO-NO. Create specific logins for any outside applications and use them instead. Do not grant any more permissions than are absolutely required.If you do a forum search:http://www.sqlteam.com/forums/search.aspFor the term "best practices" (subject only) you'll get several links, one or more of them about security. Some of the other links will give you some more information on security practices (there's a relatively recent one about T-SQL practices)In any case, unless you create separate logins for those who should not access the Oracle linked server, you won't be able to work around this problem effectively. Once you do, however, you will be able to restrict logins from connecting to Oracle.If you're not the system administrator of the SQL Server, you need to have a chat with him/her about the security issues and take the steps indicated. Don't forget, security is a pain in the ass; a convenient security model is an insecure model. |
 |
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 16:55:36
|
Robvolk, you are always there to be helful! thanks.For my situation, I am not the SQL Server administrator. However, even the SQL administrator are not allowed to see these linked tables and views. Also, I just double checked the other developer's login inforation, all of them are using Windows basede login. So,what should I do to prevent the administrator to see my linked server? I think there's no way... Thand YOU! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 17:07:11
|
| If they're using Windows based logins, then you're in luck. After you create the linked server, go to its Properties page and then the Security tab (from Enterprise Manager). There is a window that allows you to set local (SQL Server) logins and map them to remote (Oracle) logins. Add your Windows/SQL Server login to that list, and indicate the remote Oracle login and password.Once that's done, look below at the login settings for "For logins NOT defined in the list above, connections will..." Set that to "Not be made". That will prevent anyone besides you from accessing the linked Oracle server.Also look in Books Online for more information about linked server security, especially the sp_ system procedures associated with linked servers, they'll have more information on it. |
|
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 17:26:32
|
| Sorry,Robvolk, I got another trouble. I did it according to just what you said. But after changed "local login" from "sa" to my login account, say,dogli, I will get error message:"Error 7416. Access to the remote server is denied because no-login mapping exists".Do you know the problem of this? If you don't know, that's fine. Anyway, I need to buy beer today.:) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 19:02:40
|
| If you registered SQL Server in Enterprise Manager under "sa", or otherwise created the linked server under "sa", you need to redo it under your login. That should fix it. |
|
|
|
|
|
|
|
|
how to implement Linked server Security