locked
How secure is linked server? RRS feed

  • Question

  • I've setup a linked server between my SQL Server 2005 instance and a SQL Server database on my hosting provider's server. I was wondering how secure this connection is. For example, is all data between my server and the online server encrypted?
    Monday, April 20, 2009 11:51 AM

Answers

  • Hi

    For example:
    http://technet.microsoft.com/en-us/library/ms189067.aspx

    Although this just refers you to your Windows documentation (it doesn't explain how to set this up). The advantage of this method being that SQL doesn't require any extra configuration once the IPSec conenction is established.

    Ewan
    • Marked as answer by mrplace Thursday, April 23, 2009 2:08 PM
    Thursday, April 23, 2009 1:33 PM

All replies

  • Hi,

    Linked server login mappings can be added by using sp_addlinkedsrvlogin and removed by using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server.

    For example, a mapping for a linked server, S1, has been set up for a local login, U1, to remote login, U2, by using a remote password of 8r4li034j7$. When local login U1 executes a distributed query that accesses a table stored in linked server S1, U2 and 8r4li034j7$ are passed as the user ID and password when SQL Server connects to the linked server S1.

    The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.

    I hope this information is helpful for you


    Rajesh Jonnalagadda http://www.ggktech.com
    Tuesday, April 21, 2009 6:12 AM
  • Thank you for answer. My question is: is it easily possible for another person to fetch the data as it is being transferred from one server to the other? 
    Tuesday, April 21, 2009 9:26 AM
  • Hi,

    I dont think it is possible for someone to read the data as it is transferred through your linked server provided you follow the best SECURED option for your environment and application from these
    1. not be made.
    2. Be made without using a security context.
    3. Be made using the login's current security context.
    4. Be made using this security context.


    Thanks, Leks
    Tuesday, April 21, 2009 9:32 PM
  • Hello

    The datastream between SQL Servers is not encrypted so a network sniffer would be able to read the data if intercepted.

    To protect against this, you would need to implement an OS-level solution, like IPSec, which would secure the link between your servers.

    HTH

    Ewan
    Thursday, April 23, 2009 12:14 PM
  • Thank you for your reply,

    Do you have a link to a (MS) article that supports your statement?
    Thursday, April 23, 2009 12:28 PM
  • Hi

    For example:
    http://technet.microsoft.com/en-us/library/ms189067.aspx

    Although this just refers you to your Windows documentation (it doesn't explain how to set this up). The advantage of this method being that SQL doesn't require any extra configuration once the IPSec conenction is established.

    Ewan
    • Marked as answer by mrplace Thursday, April 23, 2009 2:08 PM
    Thursday, April 23, 2009 1:33 PM
  • Thank you very much
    Thursday, April 23, 2009 2:07 PM