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

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

Without the author's permission. No one should be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability whatsoever.

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

Objective:

Linked server (Linked server) enables access to two different instances of SQL Server, even to other types of RDBMS. By linking to the server, you can implement distributed queries, similar to an application, and store connection strings that connect to the target server.

The linked server can reference the remote SQL Server. Or, optionally, support other data sources for OLE DB provider that are already installed on the same machine as SQL Server.

When you create a linked server. Some considerations need to be taken into account. Described in this article

Realize:

1. In SQL Server Management Studio, open the Server objects node, right-click on "link Server" and tap "new linked server":

2. Select the name and data provider.

Assume the target server footer for 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. Mapping the required login account, assuming that local and remote logins have the same account name and password, can tick "impersonation" so that SQL Server does not need to store password into the configuration:

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

does not establish a connection (not being made)	specifies an incorrect list of logins that are not defined to establish a connection 。
	Specifies a login that is not defined in the list. Connections are not established using the security context.
be made using the login s   security context)	If you are using Windows Authentication to connect to a local server, use Windows credentials to connect to the remote server. If you are using SQL Server authentication to connect to a local server, you will need to use a login and password when connecting to a remote server. In such a case, a login with the exact same name and password must exist in the remote server.
Use this security context to establish (be-made using this-security context)	Telnet must be a SQL server Authentication login in remote Server.

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

Principle:

The link server stores the connection string in an instance of SQL Server. Ability to 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

Many others:

Suppose you use a client to connect to SQL Server and execute a query through linked server. Or it can be executed through a mock list. Then the configuration must be:

1. Windows account must have permission to access the link server. In the active folder (active Directory), the "account was sensitive and cannot be delegated" option cannot be selected.
2. Each server must have been registered with the SPN in the domain environment, and the account of the SQL Server service must be "Trusted for delegation" in the active folder

Filed under: http://blog.csdn.net/dba_huangzj/article/details/38489765

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