Security for SQL Server linked servers

Source: SQL Server linked server security

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Safety settings (Security Settings)
   1. Achieve the effect: User A can see can be used, B users can not see this linked server;
   2. Realization effect: User A can see can use, B user can see but no permission to use;
   3. Summarize
4. References (References)

two. Background (contexts)

when it is necessary to use the remote server database to interact with the data locally (such as data, etc.), we usually create a database link on the remote server locally, and for the creation of a linked server, you can refer to: SQL Server creates a linked server , but we seldom pay attention to the security of linked servers.

In order to control the security of the linked server, we take the following approach: control only one user can use the linked server, the effect is as shown in Figure1 and Figure2, Testlink users can see and use the linked server: [192.168.1.5], However, the SA user cannot see the linked server. Another security model is shown in Figure14 and FIGURE15.

(Figure1:sa users see the linked server)

(Figure2:testlink users see the linked server)

three. Safety Settings (Security Settings)

For the security of linked servers, you can achieve two kinds of effects:

1) User A can see can be used, B user can not see this link server;

2) User A can see can be used, B user can see but no permission to use;

(a) Achieve effect: User A can see can be used, B users can not see this linked server;

1) First we fill in the address of the remote server, if there is a port to join the port, do not fill the port default is 1433

(Figure3: Creating a linked server)

2) without regard to security issues, I usually use the FIGURE4 approach to set up account numbers and passwords.

(Figure4: Usually the practice of setting the account password)

3) If you want to implement the security of the linked server, then select "Do not establish a connection", then click the "Add" button, so that "local server log on to the remote server login mapping" To add the local a user and Server B a mapping relationship.

(Figure5: Undefined login)

4) Local Login settings sa This user is logged on with the remote server's user sa and password * * * * * * For remote server logon verification as shown in:

(Figure6: Login mapping)

5) through the setup of the above steps, now this link server only SA user can use, in order to see the effect, we create a testlink user, create interface operations such as Figure7, Figure8, in order to real reaction production environment account Rights Management, We also assign a database permission to Testlink, as shown in the Figure9 diagram.

(Figure7: Create Testlink user)

(Figure8: Set Testlink server role)

(Figure9: Set Testlink user identity)

6) using SA and Testlink to log in can be very intuitive to see the effect: SA has [192.168.1.5] the use of the linked server, and Testlink even see the opportunity is not. The effects are shown in Figure1 and Figure2.

(b) Achieve effect: User A can see can be used, B users can see but no permission to use;

1) Suppose we need to create a linked server on the Testlink user, but want SA not permission to use, if directly under the Testlink user to create the linked server will appear the following FIGURE10 error, because there is no permissions, permissions can be modified using FIGURE11 diagram.

(figure10:testlink user creates linked server error)

(FIGURE11: Modifying the role of Testlink)

2) Now we create a [192.168.1.48] linked server, set as shown in Figure12 and Figure13.

(Figure12: Create a new linked server)

(Figure13: Set Testlink login mappings)

3) We use SA and Testlink user Login View server link, Figure13 and Figure14 see the list is the same, that is no difference?

(Figure14:sa users see the linked server)

(Figure15:testlink users see the linked server)

4) First of all, let's see if there is a difference between using a linked server [192.168.1.48] under SA and Testlink users:

(Figure16:sa users using linked servers)

(Figure17:testlink users using linked servers)

(iii) Summary:

1) The user can see that the linked server is determined by the sysadmin role, which should be noted when assigning roles to a user;

2) If two users are under the sysadmin role, even if they see the same list of linked servers, they can also control the permissions of the linked server, as shown in Figure16 and Figure17;

　　3) If the SA user knows 192.168.1.48 's account number and password, he can also add his own user login mappings to the [192.168.1.48] link server, as long as the SA user knows the account and password , so as long as the control 192.168.1.48 account security can be;

4) If you want to use the simulation when the user login mapping, then you set the user must be the same as the Remote link Server user name and password to be able to use;

5) If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

four. References (References)

Security for linked servers

sp_addlinkedserver (Transact-SQL)

Security for SQL Server linked servers