SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DNS Alias, Clustered instance, Default Port query


DNS Alias, Clustered instance, Default Port query

Author
Message
GavD
GavD
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 557
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
SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 1499
Kerberos doesn't work with dns aliases. You have to use A records, not CNAMES.
GavD
GavD
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 557
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
GavD
GavD
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 557
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
2ndHelping
2ndHelping
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 51
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search