Without noticing, you may experience double-hop problem with your SQL Server and you may be clueless on it. With this, all you see is, some requests made to SQL Server is not getting satisfied and getting permission related to errors. Let's try to understand what is double-hop and how it can be sorted out with SQL Server.
See the below diagram;
The diagram shows that how user connects with the database using the application. He sends a request via the application and application server satisfies his request using the database hosted in the same machine. In this case, application needs to use user's credentials for connecting with SQL Server hosted is Server 01 and it impersonates the user's account for that. It is the first hop and it works fine.
Now the second hop. SQL Server 01 needs some info in SQL Server 02, hence it forwards to the request with user's credentials but, by default, environment does not allow it. SQL Server 01 has no way of forwarding user's credentials to Server 02 and you will get an error.
How to sort this out?
This requires Delegation to be enabled. Delegation allows Server 01 to forward user's credentials to Server 02 and then Server 02 can execute the code as a distributed query.
You need following to be enabled/configured for Delegation.
- User must have permission on both Server 01 and Server 02.
- Either TCP/IP or Named Pipes must be the protocol for accessing SQL Server.
- The Active Directory property called Account is sensitive and cannot be delegated must not be checked for the user's account.
- Both Server 01 and Server 02 require registered Service Principal Number (SPN).
- SQL Server services accounts must be trusted for Delegation.
- SQL Server 02 must be configured as a Linked Server in Server 01.
No comments:
Post a Comment