Impersonation is passing kerberos (windows/intregrated/SSIS) creditials from application to application on machine. Accounts need to be trusted for impersonation. Most linked servers are not on the same box, so you have to use delegation. Delegation only works on Windows 2000, Windows 2003. Windows 2003 can do constrained delegation, but constrained delegation only works when there are no old windows OS’s on the network.
Linked servers are good. Linked servers with the SA account impersonating all users attempting to use the link is bad. Using a linked server with a SQL user account with read only rights is better security, but too limited for copying tables between servers. Using SQL security in general is a bad idea, at least on SQL 2000.
The better idea is to use SQL Delegation.
1) The SQL service account must be trusted for delegation (and confusingly, not explicitly untrusted for delegation). The enterprise administrator for the domain (not the subdomain administrator) must set an account to be trusted for delegation.
2) The computer *does not need* to be trusted for delegation. The SQL2000 BOL state otherwise. SQL2005 documentation agrees the computer does not need to be trusted for delegation.
3) You need to download setSpn.exe, which is not available by default. You will need the full subdomain and domain name
setspn -A MSSQLSvc/MachineName.Subdomain.Domain.com:1433 sql4) You have to have TCP/IP running. If you are using a funky port ,you need to modify the above.
5) When you set up your link, in enterprise manager, check the ‘impersonate user’ option.
After I set this up, I discovered that the delegation didn’t work on any of the Windows 2000 boxes, but it did work on Windows 2003. This is even after we tried various random tricks, such as variations on the setspn.exe syntax, restarting servers, variations on the machine and service account name in the setspn command, granting the machine ‘trusted for delegation’ Still, at the end of it all, only Windows 2003 was able to do delegation. Me thinks that Windows 2000 delegation is broken. At a certain level of difficulty, it is not a difficult feature for power users, it is merely a broken feature.