August 23, 2012 at 9:54 am
Hi all,
I am moving databases from a default SQL 2000 instance to a clustered SQL 2008 R2 instance.
My aim is to minimise client connection disruption when this move takes place
In my lab I have set up a DNS alias to point to clustered instance network name.
The clustered SQL instance is configured to listen on port 1433.
I have registered SPNs on service account to the DNS alias.
I can telnet to DNS Alias 1433.
I can connect via odbc to the network when I include the port number but not without the port number.
All the connections to the new instance appear to be NTLM when I query sys.dm_exec_connections so possibly Kerberos is not configured correctly.
SPN is set on the service account, service account is trusted for delegation, SQL network name is trusted for delegation
SQL 2008 R2 instance is on Windows 2008R2
AD controller is on Windows 2003 SP2
I'm not sure what steps to take next to troubleshoot this so any assistance would be greatly appreciated.
Thanks
August 23, 2012 at 5:54 pm
Kerberos doesn't work with dns aliases. You have to use A records, not CNAMES.
August 24, 2012 at 11:26 am
Thanks for the response,
I changed from CNAME to A record and I have the same issue in that I have to specify the port number.
If I remove aliasing from the equation I am still having a problem just connecting to the cluster network name without having to specify the port number.
My understanding is that if I set a clustered instance to run on port 1433 I should be able to connect without using a port number as it's the default port.
I have a clustered instance: NetworkName\Instance1
I change the port in SQL configuration manager to 1433.
Should I be able to connect to just NetworkName from anywhere on the network?
The only place I can connect to just NetworkName is locally on the Cluster Node.
My problem is that from anywhere else I have to connect using NetworkName,1433
Thanks
August 27, 2012 at 3:41 am
From a bit of testing it appears you cannot connect to SQL 2008 R2 using the SQL Server ODBC driver (SQLSRV32.DLL) without specifying the port number.
I can connect successfully using SQL Native Client (SQLNCLI.DLL) and SQL Server Native Cient 10.0 (SQLNCLI10.DLL) without specifying the port.
Looks like all the client connections need to be upgraded to either include the port or use a more current driver (majority of clients are XP machines).
Has anyone had similar experiences and can you advise on the approach taken to resolve?
Thanks
August 28, 2012 at 2:32 pm
Agree on the comment about the driver issue. The SQL Server native client (SNAC) driver doesn't ever need the port as long as it's the default port. But the choice of driver you use to make your connection is always based on the application context; and there are a lot of arguments in favor of using the ADO.Net type of connections rather than ODBC. If you do that, you'll find that ADO drivers also do not need the port specified.
In this case, if you really need Kerberos, you may be stuck having to deploy application changes after the migration. Or another option might be the use of a "failover partner" parameter in your app connection string--assuming you're using ADO and can thus take advantage of it. Details are here http://msdn.microsoft.com/en-us/library/ms366348%28v=sql.105%29.aspx . Although I will caution you, I haven't tried this approach in situation where there wasn't actually mirroring involved, and this connection string example is for the situation where you have mirroring. You may want to try it and see if it works in your situation too. If it does, then you'd still probably want to deploy yet another connection string after migration is completely done, because you'll be losing the original primary server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply