Sql-server – Frequent Remote connectivity issue for SQL Server 2008 SP3

sql serversql-server-2008

I follow all your blogs all helped me in my real-time issues. From last 2 days I'm facing connectivity issues to SQL server even though all the parameters are correct and cross verified with your blogs and everything is fine.

MY ISSUE:

We have hosting server in which we provide databases for clients, the problem here is from last 2 days I'm facing problem while generating script for database with data, initially the server is connected and able to start the activity but the problem is at the last step it is throwing an error stating as follows and some times it is working fine, I really not sure what was the issue, could you please help me on this? I will be thankful to you.

Note: Instance is Default, firewall is turned off, ping and tracert reports are fine.

The error I'm getting is as follows:

A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: Named
Pipes Provider, error: 40 – Could not open a connection to SQL Server)
(.Net SqlClient Data Provider)

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

------------------------------
Error Number: 53
Severity: 20
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.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover)
   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.Smo.DataEnumerator.get_Connection()
   at Microsoft.SqlServer.Management.Smo.DataEnumerator.MoveNext()
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.AppendDataScriptAsInsertStatements(ScriptWriter scriptWriter, IEnumerable fragment)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.AppendDataScriptFragment(ScriptWriter scriptWriter, IEnumerable fragment)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.AppendDataScript(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptObjectContainer.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptedObjectCollection.ScriptAll(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.DependencyScriptHandler.ScriptCore(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptHandler.Script(ScriptWriter scriptWriter)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.SingleScriptTarget.OnScriptingFinished(Object source, EventArgs e)
   at System.EventHandler.Invoke(Object sender, EventArgs e)
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptProcessor.OnScriptingCompleted()
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptProcessor.GenerateScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.Scripting.ScriptingEngine.GenerateScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.GenerateScriptMaker.DoScript()
   at Microsoft.SqlServer.Management.SqlManagerUI.GenScriptWizForm.Script()

Please give me any suggestion where I have to check for the RCA of the same.

Nony

Best Answer

Check the SQL Server log for any message at that time. If your connecting login has default database other than master then following scenario may be related.

One of our application generated same error in dev that you mentioned. It was due to fact that application login was not able to open default database because database not accessible at that time. SQL Server log was showing following error

Related Solutions

Sql-server – Unable to connect SQL Server 2012

If you haven't verified that Named Pipes is enabled on the SQL instance, do this: Open SQL Configuration Manager, usually found in Start > Microsoft SQL Server 2012 > Configuration Tools (in Server 2008/R2. If using Server 2012, just search for it with WinKey+S) Go to SQL Server Network Configuration on the left. Open Protocols for . Right-click Named Pipes if it's disabled (will be red) and choose Enable.

.Net seems to require named pipes (and your error above mentions it). There are probably other ways to connect that would not require this.

SQL Server – Informix DB Linked Server Configuration

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.

I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.

Ultimately, the solution had several parts, of which you've done the first few:

The correct IBM Informix ODBC Driver must be installed on the database server.
Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Product Name: Ifxoledbc
- Data Source: $YourSystemDSNName
- Provider String
  - Driver = {IBM INFORMIX ODBC DRIVER}
  - Database = $YourDatabaseName
  - etc...

If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:

DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
  + N'DATABASE= ;'  --Informix DB name
  + N'HOST= ;'      --Informix Hostname or IP adddress
  + N'SERVICE= ;'   --Informix service name, ex.  ifx1_tcp1
  + N'PROTOCOL= ;'  --Informix protocol, ex.  onsoctcp
  + N'SERVER= ;'    --Informix server, ex. ifx1
  + N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed

EXEC master.dbo.sp_addlinkedserver 
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc', 
@provider=N'MSDASQL', 
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;

Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.

I hope that helps.

Best Answer

Related Solutions

Sql-server – Unable to connect SQL Server 2012

SQL Server – Informix DB Linked Server Configuration

Related Question