Connection issue after update SQL 2008 R2 to SP2

Question

Hallo,

Some days ago we succesfully update Our SQL 2008 R2 failover cluster(2 nodes) to SQL 2008 R2 SP2.

Now for 70% users we have connection issues.

First Scenario

For example we use software that connects from user computer to SQL with standart connection string like this:

SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionString); 

On users computers where we see connection issue we got message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

after I run command:

ipconfig /flushdns

, the problem disappears and connection is established, but after some period of time problem resumes. Firewalls disabled for user and failover cluster.

Second scenario

I install on one user computer SQL server management studio, then I try to connect with this user credentials to database I got:

===================================

Cannot connect to MSCLUSTER-SQL.

===================================

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

------------------------------
Server Name: MSCLUSTER-SQL
Error Number: -2
Severity: 11
State: 0

------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

after I run command:

ipconfig /flushdns

, the problem disappears and connection is established, but after some period of time problem resumes.

In SQL log I can't find any bad connections. Any suggestions for this? How can I resolve this problem?

thank you all for your help in advance

Answer 1

This could also be a reason of DNS Server entry ---talk to your Windows\Messaging Admin to check\resolve this.

In the mean time you can test your connections by TCP\IP these should succeed in case DNS is culprit here.

First of all check if somehow the timeout value of SQL got changed. You might wanna set it to 0 - that is unlimited. You'll find this option on Server properties>connections.....refer the below mentioned screenshot.

BUt this will only work in the case of SSMS\Query analyzer and not for .Net\App based connections.

I'll suggest you to check the Network if there is any delay\breaks\burps.

---

Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

I already have 0 and allow Remote connections. Before update to SP2 and change one failover claster network adapter(deleted from wmvare and add)it is working good.

From some client computer I always can connect with SQL cluster IP adress, but can't with name MScluster-sql. nslookup show me everything ok with dns resolving. any suggestions?

Answer 3

Please try changing the remote query timeout also to 0 viz no-timeout.

>>>>>From some client computer I always can connect with SQL cluster IP adress, but can't with name MScluster-sql. nslookup show me everything >>>>>ok with dns resolving. any suggestions?

Check the below settings\options:   Browser service should be started.

1. Named Pipe - enabled.

2. DSN valid entry 

3. SPN name registered & kerberos is working fine. 

http://msdn.microsoft.com/en-us/library/ms191153.aspx

Steps : http://technet.microsoft.com/en-us/library/bb735885.aspx

Hope this helps.

---

Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 4

is where software how can I troubleshout or validate my dns or sql connection validation from client PC?

Browser service started

1. Named Pipe - enabled.

2. DNS valid entry

Answer 5

Check remote query timeout in SQL Server

Conection timeout in connection string

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 6

I've seen a similar issue when a user accidently created a wrong alias on his system -- he mentioned a wrong port.

---

Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 7

hi there,

An onther possiblity would be when you are using DHCP. make sure use Domain controller physical address being used in your public and private network adapters. Also check the DNS of the domain controller being used.

goodluck.

kumar

Answer 8

I think it was bad configured Failover cluster networks, first network i can ping, second i didn't.

I delete second network adapter from wmvare then add. Now I can ping both and it works.

I also change connection timeout, restart nodes.

Many thanks for all.