none
Linked Server Security to Oracle

    Question

  • I know how to setup in Linked Servers in 2000-2012 to ORACLE databases.  I'm really hoping I don't know something that has changed.

    It's always been a pain because you have to either setup either by INDIVIDUAL user mappings or by using a specific security context.

    These two methods bite because:

    Using First method you map 30 developers to 30 local server logins to remote server login mappings, which is a maintenance/security nightmare.

    Or Second use a specific security context which lets anyone with a server login use the Linked Server, also a horrible security issue if they shouldn't be seeing the linked data.

    I believe the historical reason for not allowing windows groups when using the Local server login method mapping was due to default schema generation for Windows groups. 

    So is there a good secure and easily maintainable way to create linked servers now??????

    Thanks,

    Friday, January 03, 2014 9:08 PM

Answers

All replies

  • So I don't know much about Oracle. In fact, I know about nothing at all.

    However, so much I understand that for a logins without mapping to be used for Windows users, Oracle must support Windows authentication. Which maybe they do, but since Oracle is a multi-OS platform it is nothing I would take for granted.

    Even if Oracle has Windows authentication, there are still a few more stars that must align. The OLE DB provider you use to connect to Oracle, must support Windows authentication. And of course, SQL Server must have some logic to know how to determine whether remote data source supports Windows auth and how to use it. And to that the regular problem with double-hop issues etc.

    For SQL logins, I don't think unmapped logins can ever work, because SQL Server only has the password hash and it will not work on Oracle, even if user name and password are the same.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 03, 2014 10:47 PM
  • Oracle doesn't need to support win auth because with linked server you can specify the name/pswd to use when connecting.  My question had to do with the sql/windows side and whether you could specify a group not just a usrname.  Was hoping with the changes in 12 that this would be possible.
    • Marked as answer by chuck02323 Monday, January 06, 2014 4:46 PM
    • Unmarked as answer by chuck02323 Monday, January 06, 2014 4:46 PM
    Monday, January 06, 2014 2:20 PM
  • Ah, so you mean that for the parameter @locallogin in sp_addlinkedsrvlogin you should be able to specify a Windows group, so that you don't have to add every users explicitly?

    No, you cannot do that in SQL 2012. Or SQL 2014. I found this comment in sp_addlinkedsrvlogin:

       -- IF SPECIFIED CHECK LOCAL USER NAME (NO NT GROUP!)

    I can certainly see that this is useful, so I encourage you to submit a suggestion on http://connect.microsoft.com/SqlServer/Feedback

    I found one such item:
    https://connect.microsoft.com/SQLServer/feedback/details/375983/allow-windows-groups-as-linked-server-logins

    It was brutally closed as "by design", without any explanation for the design. That was a couple of years ago, so I think there is reason to give it new consideration.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by chuck02323 Monday, January 06, 2014 4:46 PM
    Monday, January 06, 2014 4:19 PM
  • Thanks,

    I put in a similar request years ago to add the feature and suggested it at several MS events.

    I've never had any luck with SQL or VS feedback.

    Monday, January 06, 2014 4:46 PM
  • It's not that Microsoft has a shortage of suggestions. A small handful of my requests have been honoured, but many have not.

    In any case, I have brought this up with my contacts, mainly to find out if there is anything reasonable about that "design".

    Of course, if you map by group, everyone would use the same credentials on the Oracle side, but I guess that is what happens already.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 06, 2014 6:55 PM