August 4, 2011 at 3:08 pm
I've setup a bunch of linked servers in the past few years but had an Oracle admin bring up a question that I didn't know for sure.
"How does SQL Server control the security of linked servers at the user level? It seems that all sql server linked servers are essentially public objects. If you have access to any db in the sql server instance, you have access to the linked server data."
I have to say that I'm not quite sure and thought I'd look for a little help in understanding the dynamics of who can use linked servers and if there is a way to lock them down if the linked servers were setup with a static user/password (such as for an Oracle server).
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 4, 2011 at 3:46 pm
yes, there is security control over the linked servers at both from where you are accessing and the remote server
If you right click on the properties of the linkedserver you already have setup , then there is tab called security
where you can add multiple or single logins or use a remote login id and password.
All the logins in this securty tab should be having access on the remote server too.
August 9, 2011 at 9:01 am
So you can put an Oracle login in the "Remote User" field? When I try it gives me an "invalid user/pass" error, even though it works if I just put it down in the "Be made using this security context" box.
The way we have always set them up in the past is to just put the Oracle user/pass in the "Be made using this security context" area. However, this means that anyone that has access to the local SQL server can then use the linked server no problem. I was hoping that I could use the remote server logins instead to limit the linked server access to only specific logins.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 9, 2011 at 9:17 am
You can't put an Oracle login on the local side because that isn't authenticated or recognized in SQL Server. You should be able to specify that certain logins to SQL Server are mapped to other logins used in the linked server. There is a default mapping, which makes the linked server a public object, but if you say that Joe in SQL Server is mapped to JoePlumber in Oracle, than AFAIK, only Joe can access Oracle.
August 9, 2011 at 9:54 am
Steve Jones - SSC Editor (8/9/2011)
There is a default mapping, which makes the linked server a public object, but if you say that Joe in SQL Server is mapped to JoePlumber in Oracle, than AFAIK, only Joe can access Oracle.
I'm sorry, I got lost a little bit on that part. If you have time to expand a bit on how to do that I would greatly appreciate it.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 9, 2011 at 11:40 am
Steve Jones - SSC Editor (8/9/2011)
You can't put an Oracle login on the local side
Might be I'm understanding wrong but
You can have the Oracle Login on local side in a linked server as
checking the box. Be made in the security context (Remote Login with Password)
This login is no need to be under sql server logins.
I have this way setup in my environment which access the Oracle server.
If you want to control the security of linked server check this link
August 9, 2011 at 11:47 am
@SQLFRNDZ (8/9/2011)
Steve Jones - SSC Editor (8/9/2011)
You can't put an Oracle login on the local sideMight be I'm understanding wrong but
You can have the Oracle Login on local side in a linked server as
That's not a local login. That's a remote login. The local logins are those used to gain access to the SQL Server instance. You configure the linked server to use this remote login, and map it to a local login.
August 9, 2011 at 11:49 am
SQLJocky (8/9/2011)
Steve Jones - SSC Editor (8/9/2011)
There is a default mapping, which makes the linked server a public object, but if you say that Joe in SQL Server is mapped to JoePlumber in Oracle, than AFAIK, only Joe can access Oracle.I'm sorry, I got lost a little bit on that part. If you have time to expand a bit on how to do that I would greatly appreciate it.
Go to the linked server properties, the security tab. There is a radio button below that says for logins not in the list, what do you do? If you check, "not be made", then unless you have specifically mapped a local SQL Server login to a remote login, the linked server cannot be used.
August 10, 2011 at 7:24 am
Go to the linked server properties, the security tab. There is a radio button below that says for logins not in the list, what do you do? If you check, "not be made", then unless you have specifically mapped a local SQL Server login to a remote login, the linked server cannot be used.
So just to be clear...I would create a local SQL Server Login, use it at the top and add the Oracle user/pass to the right and that should give that one user access to the Oracle connection. Then if I use "Not be made" then it restricts all others. I did try that and was getting an invalid user/pass message from Oracle and just figured I was doing something wrong. I'll work with our Oracle guy since it seems I was doing it correctly. 😀 Thanks for the help!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
August 10, 2011 at 9:53 am
That should be correct.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply