locked
SQL Server 2008 security for linked server? RRS feed

  • Question

  • Hi, I have SQL Server 2008 and create linked server to connect Oracle database. How can I use "Windows Authentication"  to connect Oracle database? (Further this linked server use by SSRS data source to run OPENQUERY to get Oracle table data).  Is any Kerberos authentication require to setup?

    Any idea pls. Thanks.

    Saturday, March 12, 2011 2:25 AM

All replies

  • http://support.microsoft.com/kb/280106

    Take a look at ERROR message 6


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 13, 2011 8:58 AM
  • That support document doesn't help much about how to pass "Windows Authentication" to Oracle linked server. Any idea?
    Sunday, March 13, 2011 9:21 AM
  • I have doubts that Oracle supports WA as SQL Server does support


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 13, 2011 9:43 AM
  •  

    Hi KM IT,

     

    Per the Books Online:

     

    If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication. Therefore, you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server.

     

    If you are in a domain environment where users are connecting by using their domain logins, selecting "Be made using the login’s current security context" on Security page is often the best choice.

     

    For more detailed information, please refer the below link:

    Security for Linked Servers (http://msdn.microsoft.com/en-us/library/ms175537.aspx)

    Using Kerberos with SQL Server (http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx)

     

    If there are any questions, please feel free to let me know.

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Monday, March 14, 2011 3:05 AM