Chapter 1 Securing Your Server and Network (12): Securing Linked Servers

Original source: http://blog.csdn.net/dba_huangzj/article/details/38438363, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Previous article: http://blog.csdn.net/dba_huangzj/article/details/38398813

Objective:

A linked server (Linked server) enables two different instances of SQL Server to exchange visits with even other types of RDBMS. By linking to a server, you can implement distributed queries that resemble an application that pre-saves connection strings to the target server.

A linked server can refer to a remote SQL Server, or any other data source that supports OLE DB provider, which is already installed on the same machine as SQL Server.

There are some considerations to consider when creating a linked server. Described in this article

Realize:

1. In SQL Server Management Studio, open the Server Objects node, right-click on "Linked Server" and click "New Linked Server":

2. Select the name and data provider. If the destination server is at tail-end SQL Server, enter the instance name of the target server and select SQL Server as the server type:

Original source:http://blog.csdn.net/dba_huangzj/article/details/38438363

3. On the "Security" page, map the required login account, if the local and remote logins have the same account name and password, you can tick "impersonation" so that SQL Server does not need to store the password into the configuration:

4. Choose how to handle accounts that are not defined in the map list:

Specifies that the connection is not established with a security context for logins that are not defined in the list.

no connection (not be made)	Specifies that connections are not established for logins that are not defined in the list.
No connection established using security context (be made without using a security context)
be made using the login ' s current  secu rity context)	If you are using Windows Authentication to connect to the local server, use Windows credentials to connect to the remote server. If you are using SQL server Authentication to connect to the local server, you will need to use a login name and password when connecting to the remote server. In this case, a login with the exact same name and password must exist on the remote server.
Use this security context to establish (be made using the "this" security context)	Telnet must be a SQL server authentication login on the remote server.

Original source:http://blog.csdn.net/dba_huangzj/article/details/38438363

Principle:

The linked server stores the connection string in an instance of SQL Server, and you can define how to access the linked server. From a security standpoint, it is a good idea to restrict access in the mapping list and select "Do not recommend connections."

Original source:http://blog.csdn.net/dba_huangzj/article/details/38438363

More:

If you use a client to connect to SQL Server and run the query through a linked server, or through a mock list, you must configure:

1. The Windows account must have permission to access the linked server, in Active Directory, and the "accounts is sensitive and cannot be delegated" option cannot be selected.
2. Each server must already have an SPN registered in the domain environment, and the account of the SQL Server service must be "Trusted for delegation" in the Active Directory