Delegation Issue - NAV 2009 SP1 RTC

matttrax

I think this is because we are using a semi-advanced setup and because I'm still getting used to the whole 3-tier thing. I am getting the typical "Login failed when connecting to SQL Server XXXXX" error. I'm pretty sure this is a delegation issue and I have been through all of the posts, blogs, and run through the setup guide on MSDN a couple times. Here's a rundown of our setup:

Clustered SQL Server (My apologies if I don't explain this correctly as our DBA set it up).
SPSQL01 is the cluster and each of the servers is SPSQL01-1 and SPSQL01-2. For some reason those have been "renamed" (don't know the term, sorry). So you can log into each of those two servers with those names, or SPXXSQL01 and SPXXSQL02. There are two instances of SQL running, SQL01 and SQL02. To connect to the database with the classic client the server name would be SPXXSQL01\SQL01.

Application Tier with four instances of the NAV Server service (although just trying to get one to work). It is running under a login called NAVServiceTier for now. The following SPNs have been setup for that login:

SQL01/SPXXSQL01.corp.local:1433
InstanceNAV01/SPXXNAV01.corp.local:7046

The login has been granted full control on the correct folder on the server. Delegation has been enabled to any service. The user and schema has been created in SQL. Everything that was in the MSDN guide has been done as far as I can tell.

Wanted to give you all a chance before I called up Microsoft. Let me know if you need more information. I appreciate the help.

clayfox

Glad to hear I am not the only one trying this.

I went through all the same things and just finally got it all working yesterday.

Here is my thread from the other day and the solution.

One trick is that the SPN documentation needs to be updated and the domain accounts have to be setup right.

viewtopic.php?f=32&t=37900

Let me know how you do.

tedcjohnston

The SPN for SQL needs to be for the login that the SQL service runs under, not the login for the NST.

Search for a posting by Scott Frappier on SPNs and the NST. He explains it quite well (after fixing my delegation issue).

matttrax

So very, very annoyed.

I got it to work. The RTC loaded great on three tiers and in a clustered environment. I got really excited, closed it and got up and did my happy dance. \:D/ Came back and tried to reopen, back to the Anonymous login delegation issue. I made the angry face :x

Could someone tell me the exact SPNs I'm supposed to have? Our service tier and sql service are running on different servers, different accounts, standard ports. I'm pretty sure it's some sort of conflict between SPNs. I've done everything as the fully qualified domain name. The fact that it worked for a minute and then stopped without changing anything has me really puzzled.

clayfox

I had not gone back to mine but mine is intermittent as well.

I think it has to do with that the load balance name "NAV", which I have an SPN for, is actually being authenticated by the member servers "NAV1" or "NAV2". Using a member server name works everytime, but the LB name only works sometimes and may only after the member server acting as primrary has successfully authenticated.

alexpeck

Could you provide a summary of which accounts are enabled to delegate to which services in Active Directory?

I took a look at this page: http://msdn.microsoft.com/en-us/library/ms189585(SQL.90).aspx

It seems that you need to have a SQL SPN with no port registered on the SQL Service account (e.g. setspn -a MSSQLSvc/machine.domain.company.com domain\user), and the NAV service must be set to delegate to this in Active Directory. This is because the cluster instances use different ports.

As for the system functioning intermittently, this could be for one of two reasons:

1. If you change SPNs or active directory configuration without flushing the tickets and restarting the services you retain the old configuraiton. If the delegation stopped working after the client's Kerberos ticket expired or after one of the services was restarted, this is probably what happened.

2. You configured the SPN with a fixed port for one instance, then SQL started serving you from the other instance. I'm not familiar with SQL Server clustering, but I thought the cluster is always active-passive, and you only fail over onto the passive instance if the active fails. Did you initiate a fail over?

Alex

matttrax

alexpeck wrote:

Could you provide a summary of which accounts are enabled to delegate to which services in Active Directory?

Here are the SPNs we have setup on the accounts:

NAVServiceTier
SQL01/XXXSQL01.corp.local
XXXNAV01/spXXXnav01.corp.local:7046

SQL
SQL01/XXXSQL01.corp.local
XXXNAV01/spXXXnav01.corp.local:7046
MSSQLSvc/XXXSQL02.corp.local:51982 (present before I added anything)
MSSQLSvc/XXXSQL02.corp.local:SQL02 (present before I added anything)
MSSQLSvc/XXXSQL01.corp.local:51642 (present before I added anything)
MSSQLSvc/XXXSQL01.corp.local:SQL01 (present before I added anything)

Both logins are set to trust to any service (trying to simplify).

Again, spXXXsql01 is the cluster which contains two servers in an active-active setup. These are spXXXsql01-1 and spXXXsql02-2. These have been "renamed" so that they can be logged into as XXXSQL02 and XXXSQL02 respectively. (If someone knows what that is called please let me know). There are two instances, SQL01 and SQL02. Being that I don't much understand all of this I can only say that the SQL01 service is started, but the SQL02 service is not.

XXXNAV01 is the middle tier. It has four NAV Servers installed, but I am only working with one right now. That service is also started and points to the correct database.

If there is any further information I can provide please let me know. I really want to get this working

alexpeck

Firstly, I think you might have a duplicate SPN which will certainly cause authentication to fail. The SPN SQL01/XXXSQL01.corp.local is listed under both NAVServiceTier and SQL service accounts (if I interpreted your list correctly).

You listed SPNs for the physical nodes under SQL. I haven't personally tried this (so this is a suggestion rather than a definitive answer), but I think you need to specify an SPN for the virtual SQL instance - in your first post I think you said it is SPSQL01. So you should also have an SPN like:

MSSQLSvc/SPSQL01.corp.local

If you can, try to enable Kerberos event logging on all the machines (including the domain controller). This is described here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;262177. The error messages are a bit cryptic, but might at least provide a clue. This docuument is a good reference for interpreting the errors: http://www.microsoft.com/downloads/details.aspx?FamilyID=7dfeb015-6043-47db-8238-dc7af89c93f1&displaylang=en

I'm curious what you mean by renaming the machines. Did you change the computer name in the properties dialog or provide additional DNS entries for the additional names? The latter could certainly have an impact.

Alex

matttrax

alexpeck wrote:

I'm curious what you mean by renaming the machines.

I wish I knew what I was saying too I wasn't involved with the setup of the cluster and know next to nothing about SQL Architecture for one server, let alone a cluster with multiple instances.

alexpeck wrote:

If you can, try to enable Kerberos event logging on all the machines (including the domain controller)

Sadly our hardware is also managed through an outside company. Yay for managed services. So trying to spend the least amount of money.

I will keep trying things and let you know what I find. In the meantime I am open to as much help as possible. This stuff is way outside my usual writing code all day. Thank you so much for everything so far.

matttrax

I'm back, but sadly it is still not working. The following SPNs worked for a while on Monday, but then stopped working. We are still working with Microsoft to try and figure this one out.

SPNs for the NAV service account:
NAVService01/NAVServer:7046
NAVService01/NAVServer.corp.local:7046

SPNs for the SQL service account:
MSSQLSvc/SQLServer01.corp.local
MSSQLSvc/SQLServer01.corp.local:51642
MSSQLSvc/SQLServer01.corp.local:SQL01
MSSQLSvc/SQLServer02.corp.local
MSSQLSvc/SQLServer02.corp.local:51982
MSSQLSvc/SQLServer02.corp.local:SQL02

As far as they can tell everything is setup correctly and we aren't having any network issues. One thing we did find is that SETSPNs may not take effect immediately when you have multiple Domain Controllers. It takes some time to replicate across all of them.

And as for the "renamed" I was talking about in earlier posts, I think this is more accurate. The servers were aliased under a different name and that alias was also given its own IP.

Back on the phone today, wish me luck As always, I'm open to any ideas you might have.

[Edit] It's decided to work today. Although no changes have been made to SQL, AD, Services, or anything else for more than 24 hours. What could this be?

matttrax

Apparently we're having domain controller issues. If you hit certain ones you get the double hop / anonymous login problem. Others it works fine. Thoughts on why this would be? I am not a networking / server person at all.

alexpeck

That sounds like a replication problem, have you ruled out the problems listed here: http://technet.microsoft.com/en-us/library/cc738415(WS.10).aspx?

Alex