Re: Distributed Query - Linked Servers?

Hello Mark,

Thank you for understanding my post.

1. In the case of SQL style accounts, it doesn't seem very secure to me, because the password will be transmitted over the network.

2. In the case of Windows style accounts, we know it can work with delegation. BUT, it should also be able to work by setting up a credential and proxy account on Server1. In this case it could work with NTLM authentication. The chain would look like this.

User_Account->NTLM->Server1->Proxy_Account->NTLM->Server2

The user account authenticates to Server1, the Proxy account authenticates to Server2. In SSIS we have this, but in linked server we do not?

I must explain that I solved the original problem by using delegation, but using delegation is not always a good solution. It requires domain administrator action to set up, and there are implications during domain and forest migrations.

I don't need further help with this, but I am happy to continue discussion or run more tests.

Thanks.

Mark Han[MSFT] wrote:
HI Gerry,

Thank you for the reply.

Sorry for the inconvenience, since you current concern is not to execute the SSIS package. in order to better asssit you in the post, could you please tell me your concern based on the current situaion?

Based on the current information, I would like to explain my understanding on the original issue.
1 you can able to get it working with SQL style accounts without delegation. it is normal because that when we use sql account to connect to the linked in double hop scenario, we use sql authentication. To use SQL authentication, we just need to verify that the 2 sql server instances are under sql server and windows authentication mode and the sql account you use has the permission on the 2 sql server instances.

2 I was able to get it working using Windows style accounts after I set up SPNs and enabled delegation. it is because that when we use windows account to connect to the linked server(doulbe hop scenario), we have to use kerberos authentication. and to use kerberos authenticatin, we must setup SPN for the 2 sql server instances and enable the delegation what ever the setting of the linked server. if the delegation setting is not enable, the kerberos authentication will fail. That will cauese the window accout fail to connect to the linked sql server.

according to your description, "by entering credentials of an account that could authenticate from Server1 to Server2". it is the configuration of the linked server.(not for kerberos authentication). So the original connection will fail. once we complete the setting of delegation for kerberos(the setting is on DC), we are able to connect to the linked sql server by windows account.

Besides, generally, it is suggested to enable delegation, it is much more security.

I look forward to heairng from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================



--
Gerry Hickman (London UK)
.

Relevant Pages