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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply