DNS Alias, Clustered instance, Default Port query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » DNS Alias, Clustered instance, Default Port...

DNS Alias, Clustered instance, Default Port query

Rate Topic

Display Mode

Topic Options

Author

Message

GavDeer

Posted Thursday, August 23, 2012 9:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 120, Visits: 266

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

Post #1349227

SpringTownDBA

Posted Thursday, August 23, 2012 5:54 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 316, Visits: 1,484

Kerberos doesn't work with dns aliases. You have to use A records, not CNAMES.

Post #1349400

GavDeer

Posted Friday, August 24, 2012 11:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 120, Visits: 266

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

Post #1349835

GavDeer

Posted Monday, August 27, 2012 3:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 120, Visits: 266

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

Post #1350284

2ndHelping

Posted Tuesday, August 28, 2012 2:32 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:58 AM
Points: 11, 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.

Post #1351258

« Prev Topic | Next Topic »

Permissions