June 3, 2008 at 4:43 am
Hi I've got a strange problem here and I need to find a resolution, so hopefully someone here will be able to advise.
We've got two SQL servers, one running SQL2000 and the other running SQL2005. Both servers are running Windows Authentication. Our main business application is running on the SQL2000 box and any in-house applications we create are running from the SQL2005 server as it is more powerful, and SQLSMS is much nicer to develop with.
If I remote desktop (as administrator) to the SQL2005 server and run a script like the following
SELECT s2005.fielda, s2000.fieldb
FROM databaseA.tableA s2005
JOIN databaseB.tableB s2000
ON s2005.id = s2000.id
the script will work.
If I run the same script in SQLSMS on my client XP machine it gives the following errors
OLE DB provider "SQLNCLI" for linked server "sql2000" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone advise?
June 3, 2008 at 5:24 am
You need to configure Kerberos authentication. NTLM authentication will only let you pass an authentication token across two logins. So, your token on your local machine is passed to ServerA then when your token is passed from ServerA to ServerB it has jumped too far and is invalid. The security model was change in Windows NT4 to prevent these additional hops because of worm attacks on networks.
Kerberos authentication uses a ticketing model that will allow you to authorize services (like SQL Server) to validate authentication. This is the newer MS authentication model and it is more secure and faster than the old one. Talk to your network administrator. If they know what they are doing they can help you configure this, it should only take a few minutes.
June 3, 2008 at 7:18 am
Cheers for that, I am the network admin, so I'll have to add it as a job for another day, but I've looked at the MS documentation on it, and at the moment it would require reconfiguration I do not have time for, as it's only really effecting me at the moment, I'll just live with it.
June 3, 2008 at 7:24 am
Not just Kerberos authentication, but Kerberos delegation. The first SQL Server must be configured to be able to delegate to the second one.
K. Brian Kelley
@kbriankelley
June 3, 2008 at 7:28 am
Assuming you are using Active Directory and Server 2003 for your domain controllers, it is already turned on. You should just have to configure your SPN's.
Use the setspn command line utility to configure an SPN for your MSSQL service account on each of your servers. Then, go to the Delegation tab on the user accounts in AD (which will appear for the accounts once they have SPN's configured) and click the trust for any service checkbox. Do the same for the two servers.
June 3, 2008 at 7:43 am
Michael Earl (6/3/2008)
Assuming you are using Active Directory and Server 2003 for your domain controllers, it is already turned on. You should just have to configure your SPN's.Use the setspn command line utility to configure an SPN for your MSSQL service account on each of your servers. Then, go to the Delegation tab on the user accounts in AD (which will appear for the accounts once they have SPN's configured) and click the trust for any service checkbox. Do the same for the two servers.
You don't want to do the trust for any service. That effectively puts you in an unconstrained delegation setup. This was the only setup possible in Server 2000 AD. It is also strongly recommended against by Microsoft and by security experts because of the potential for abuse. You want to set up the constrained delegation properly.
Windows 2000 does not support constrained delegation. Because unconstrained delegation will diminish security, avoid using delegation in Windows 2000 environments.
and
To configure the account to use unconstrained delegation, select Trust this computer for delegation to any service (Kerberos only). This option is not recommended.
This is from: Troubleshooting Kerberos Delegation
K. Brian Kelley
@kbriankelley
June 3, 2008 at 8:35 am
For Kerberos and SPN to work, I'd need to change the SQL server from Local System to a domain account wouldn't I?
June 3, 2008 at 8:37 am
timothy.merridew (6/3/2008)
For Kerberos and SPN to work, I'd need to change the SQL server from Local System to a domain account wouldn't I?
Yes!
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply