February 18, 2014 at 11:52 am
Hey all,
We recently migrated from a 3 instanced two node cluster to a single virtual machine. In order to make this happen without having to change application connection strings, I had the bright idea of using DNS aliasing. In order to use DNS aliasing though, I had to have each instance running on port 1433, meaning they all needed dedicated IP's. At least that has been my understanding per this article.
So, that's the route I went. Set each instance to their own IP:
- Instance1: 10.0.0.1
- Instance2: 10.0.0.2
- Instance3: 10.0.0.3
I then set each to run on 1433.
Last but not least, setup DNS aliasing:
- Alias1: 10.0.0.1
- Alias2: 10.0.0.2
- Alias3: 10.0.0.3
I can now connect to Alias1\Instance1, Alias2\Instance2, Alias3\Instance3 successfully!
Unfortunately though, I have a couple issues occurring with this setup.
1) The DNS alias works; however If you connect to the new host name + instance + port, it does connect; however it changes which instance you land on each time. For example, If I connect to: NewHost\Instance1,1433, I sometimes end up on Instance1, sometimes on 2, sometimes on 3. Whatever controls this (I don't think the browser service?) simply isn't consistently sending me to the proper instance.
Also, when connecting to IP\Instance, it connects me to the right instance.
- 10.0.0.1\Instance1 = NewHost\Instance1
2) Authorizations are occurring via NTLM and not Kerberos, which is causing Sev 20 errors "SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure." Thankfully the vast majority of the application uses SQL auth and this isn't breaking the application.
Now, #1 really isn't a big deal being that the world knows of these machines as their old names - which are the DNS aliases; however I'm more so curious to understand why this is happening and if there is a fix.
For #2, post setup, only Instance1 had an SPN and it was based on instance, not port - MSSQLSvc/NewHost.uhhs.com:Instance1. My service account has the ability to modify SPN's, so that is not our issue. I manually created an SPN for the others; however this changed nothing. Connections are still NTLM and still getting the errors. Obviously I can't make a MSSQLSvc/NewHost.uhhs.com:1433 being that all three instances are on that port.
Any thoughts here? I've been chatting with Microsoft and unfortunately thus far, they've not been of much help.
Thanks!
February 18, 2014 at 2:12 pm
Well, I have been using DNS aliases for a while. It has helped me through a number of hardware upgrades. I highly recommend it.
To get through your NTLM issue, you can do one of two things. You can manually set up the SPNs in Active Directory (see the SETSPN command), or you can make all the DNS names CNAMEs, rather than ANAMEs. The downside of the CNAME is that if someone pings your DNS alias, they get a response marked with the real machine name. This is probably only a problem, if you have users who are likely to ignore your mandates, and go directly to the machine name, rather than the DNS alias.
With the DNS aliases, I have set up servers on non-default ports with nearly no problems (at least no problems directly blamed on the DNS alias setup).
February 18, 2014 at 2:13 pm
Sorry. Double post issue.
February 18, 2014 at 2:35 pm
Matt Crowley (2/18/2014)
Well, I have been using DNS aliases for a while. It has helped me through a number of hardware upgrades. I highly recommend it.To get through your NTLM issue, you can do one of two things. You can manually set up the SPNs in Active Directory (see the SETSPN command), or you can make all the DNS names CNAMEs, rather than ANAMEs. The downside of the CNAME is that if someone pings your DNS alias, they get a response marked with the real machine name. This is probably only a problem, if you have users who are likely to ignore your mandates, and go directly to the machine name, rather than the DNS alias.
With the DNS aliases, I have set up servers on non-default ports with nearly no problems (at least no problems directly blamed on the DNS alias setup).
Hey Matt,
Thanks for the response. As stated, I do indeed have an SPN setup for the instance (based on host name); however I can't create an SPN for the port, being that all three instances run on 1433. Per a recommendation of a friend, I went ahead and set it back to a dynamic port, registered an SPN for that port, and while it does work ... still no Kerberos.
I then went ahead and created manual SPN's for the DNS Alias\PORT & DNS Alias\Instance ... still no go on the Kerberos!
February 18, 2014 at 2:50 pm
Instead of a dynamic port, you should probably set a static port. I usually start at 1435, and work my way up from there, but you can just as easily take a port above 30000.
February 18, 2014 at 2:52 pm
Well, happy to report, that I have fixed it. It was indeed the lack of an SPN for the virtual ... even though I said earlier I put it in, looks like it was just a timing issue.
That and the reason I omitted this is because this is our first time using aliasing via ANAME vs. CNAME, which doesn't need the SPN.
Oh the fun!
February 18, 2014 at 2:59 pm
That is the maddening thing about Kerberos problems. No change you make takes effect immediately, due to the caching that happens all over the place. Whenever you make a change, you need to run things like KLIST.exe to clear all of your kerberos tickets, and get new ones.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply