Security around linked severs

[bluto99]

I am creating a linked server from SQL Server 2005 to Oracle. I would like to lock down the link to a set of developers assigned to an global windows group. I konw there is a method to map individual users to a login on the Oracle database but I cannot find a way of allow a group to have access.

Does anyone know of a way to do this?

 

[mrdenny]

Unfornitually there is no way to map a domain group across a linked server. It's got to be done login by login.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bluto99]

That is a shame, would be really handy to be able to do this as it is in keeping with our security structure.

Is there a security option to take out the ability to use linked servers?

 

[mrdenny]

Sadly there isn't.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

MrDenny,

Can you expand on your comment here:

Unfornitually there is no way to map a domain group across a linked server. It's got to be done login by login.

I just set up a linked server today. I always set it up to use an existing account on the target server to connect.

My understanding is that there can only be ONE connection per linked server? ie) the connection I use to set up the linked server.

Thanks

 

[mrdenny]

If I have a login on my SQL Server called "MYDOMAIN\Domain Admins" and this login maps to a Windows Group also called "MYDOMAIN\Domain Admins" there is no way for me to setup a mapping through the linked server for this login. For each member of the "MYDOMAIN\Domain Admins" domain group who needs access to the linked server I have to add there domain account as a SQL account (in my case MYDOMAIN\mrdenny) and use that account to map through the linked server.

The ops question isn't about connections, but how to secure the linked server so that unauthorized users aren't running queries against the Oracle server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com