SQL Server configures delegation to implement double-hop

Document directory

• Step 1:
• Step 2:
• Step 3:
• Step 4
• Step 5
• Step 6

References

Principles and experiments of SQL Kerberos

Install SQL Server native client and sqlcmd separately

Preface

In the previous blog on the principles and experiment of SQL Kerberos, we talked about the two Windows authentication modes NTLM and Kerberos. What are their differences and functional differences.

NTLM is a simple method that can complete tasks in most cases. But it can only be completedSingle hop authentication(There is only one client and one SQL Server server ). In some casesThe Kerberos technology is required for authentication of double hop or even multiple hops.. For example, if a user accesses a webpage with a database connection script using IE browser, the connection configuration is set up using its own Windows account. At this time, IIS needs to first authenticate the security context of the IE browser. After passing the authentication, pass it to the SQL Server service. the SQL Server service authenticates again to confirm that it has the right to access the SQL Server database. This identity transfer completed on the IIS service is called a dual-hop point or a delegation (delegate) (the client entrusts IIS to perform identity transfer ). Another common case is to create a linked server on SQL Server, select the target connection server (be made using the login's current security context) using the security context of the current user (see Figure 5-24 ). In this case, the local SQL Server performs a double hop to pass the security context of the client to the remote SQL Server server.

Body

This article mainly explains how to configure delegation. The prerequisite for delegation is that Kerberos authentication has been configured. For details, refer to the principles and experiments of SQL Kerberos in the previous blog.

The experimental environment is also the three machines mentioned above, which areDC-> SANZ-W7 (do transition node)-> WUW-W7. Where the SANZ-W7 starts the SQL Server service with the local system, and the WUW-W7 starts the SQL Server service with the domain account wuwang. The reason for this is because we will see later when using the WUW-W7 for the transition node, in the DC for the delegation query will report an error, but the use of SANZ-W7 for the transition node is successful, the specific error message is as follows:

Login Failed for user 'nt Authority \ anonymous logon '.

The following describes how to configure the delegation and the Kerberos configuration is omitted.

Step 1:

First, find the DC, SANZ-W7, and WUW-W7 in the computer in the DC and configure it to "trust this computer for delegation to any service (Kerberos only)", as shown in:

Step 2:

Disable the "account is sensitive and cannot be delegated" attribute of the domain account administrator who queries the delegation on the DC, that is, disable this attribute, as shown in:

Step 3:

Return to the DC and verify that the SPNs are already in use. My domain name is MSFT. Execute the following command in the command line:

ldifde -d "CN=Users,DC=toppest,DC=com" -l servicePrincipalName -F output.txt

Shows the execution result:

Then we can find this output.txt file under c: \ Users \ administrator. Then confirm that there are two SQL Server SPNs under the SQL server startup account wuwang, and no other accounts have registered the same SPNs.

dn: CN=Wu Wang,CN=Users,DC=msft,DC=comchangetype: addservicePrincipalName: MSSQLSvc/WUW-W7.msft.com:1433servicePrincipalName: MSSQLSvc/WUW-W7.msft.com

I did find the following information in the exported output.txt file, but did not find information about the sanzhang domain account. Only the following information is shown:

dn: CN=San Zhang,CN=Users,DC=msft,DC=comchangetype: add

This may be the difference between using a domain account to start the service and using local system to start SQL Server. It is also because there is no sanzhang's SPNs Registration Information in output.txt, so there is a previously mentioned failure to use WUW-W7 as a transition node. Because dcis in the process of delegation, the last node is the domain account registration spn, and the node where the SPNs can be found on output.txt.

Step 4

Find the wuwang and sanzhang accounts in the DC, and confirm that the "trust this user for delegation to any service (Kerberos only)" option is selected on the "delegation" tab )", I found that only the wuwang account has the delegation option, as shown in:

Step 5

The SQL code for creating a connection server linked server on the transition node SANZ-W7 to connect to the WUW-W7 server is as follows:

-- Delete the server exec sp_dropserver wangwu -- create the connection server exec sp_addmediaserver @ Server = 'wangw', -- the accessed server alias @ srvproduct = '', @ provider = 'sqloledb ', @ datasrc = 'wuw-w7' -- server to be accessed -- Test query select * From wangwu. insidetsql2008.hr. employeesselect * From wangwu. insidetsql2008.sales. orders

Step 6

We want to perform delegation, which is equivalent to DC accessing the SANZ-W7 through the WUW-W7, so we have to add the DC user administrator in the WUW-W7 to the windows-validated login, and grant the query Test Database insidetsql2008 permission.

If your DC does not have SQL server installed, you only need to install SQL Server native client and sqlcmd to connect to the database. For details, refer to the blog: SQL Server native client and sqlcmd separately.

Use sqlcmd in DC to connect the transition node SANZ-W7. The connection code is as follows:

sqlcmd -S SANZ-W7 -E

At this point we go to view the SANZ-W7's Security Log (event view-> Windows Log-> Security), we will find an access from DC \ administrator, and use Kerberos authentication, as shown in:

Next we connect to the WUW-W7 database by linking the server, as shown in the Code:

After running the above command will get the query results, each of us to view the security log of the WUW-W7, will find an additional Kerberos Authentication login from the Administrator, the same as the security log above.

To sum up, we have completed the complete delegation configuration.