Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, January 4, 2011 8:06 PM
Hi guys ,
I am facing a weird issue with SQL Server. Our application team is getting these kind of error since this morning while login through the Application and sometime while fetching the data from the application.
Can Some one please help me on this matter. Is this something related to the SQL Server. We are using SQL Server 2008 R2 on Window Server 2008 R2.
I have been googling this whole day. I could find the fix to this problem.
Please share your thought if someone experienced the same problem.
Thanks
Error Message:
1/4/2011 10:07:30 AM - security_UserManager_login - System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
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.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
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.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
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 XXXX.Data.Client.DataWrapper.ExecuteProcedure(String storedProcedure, DbParameter[] parameters)
Tuesday, January 4, 2011 8:12 PM
By any chance did you just change the default value for "user connections" using sp_configure ? Is there any real intermittent issue in your network setup? Any more logs from SQL trace and windows event viewer ?
Thanks, Leks
Tuesday, January 4, 2011 9:37 PM
Thanks for replying Lekss
I did not make changes to User connection setting:
Min-0
Max-32767
config_value=0
Run_value=0
I did not find any errors in the Window event and SQL Error log. Network people confirmed they don't have any issues. I thought it might be something with total connection to the SQL Server . I found there were 250 connections at that time. But connections used to exceed to 300 before and there used to be no issues.
We just rebooted the Db Server and everything seems to work fine now with total of 100 connections.
Is it possible that we exceeded no of connections and SQL Server drops the connections....I believe 32767 is the total no of connection we can right ?
Also I want to update something else.. when we rebooted the server all the app and SSMS on XP machines were connecting to the db server but the App and SSMS on Window 7 machines could not connect.
but after some time window 7 machine could also connect ....
Any Ideas ....?
thanks
Wednesday, January 5, 2011 1:36 PM
This would probably get better results on the .NET forum.
However, has this every worked? Is this a new installation? What has changed?
This seems to indicate you are trying to run a stored proc using a connection string. I would suspect either the user does not have access to the stored proc or some other security issue is blocking the user.
Wednesday, January 5, 2011 2:01 PM
Has the password for the Login changed?
The error message states that the error occurred during the login process.
This to me suggest a possible authentication issue for the Login you are connecting with. I suggest validating the credentials used by Login that your application/client is using, by testing them by connecting to SQL Server using SQL Server Management Studio.
John Sansom | SQL Server DBA Blog | SQL Server Consultants - Santech Solutions
Wednesday, January 5, 2011 3:20 PM
Thanks for your inputs Tom and John.
I am not a .Net guy. I am newbie in SQL , working as junior dba. Everything is good on the SQL Server side. This is QA region. Login credentials,permissions and everything was working fine from last 5 months but suddenly client started getting this error as above:
** an error occurred during the login process**. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
As I said before, sometimes when client uses some fetch functionality in the App it does not returns complete results. They get the same error in between the process.
I am just worried if it's some thing wrong from the SQL Server side. I hope it's nothing concerned with the number of simultaneous connections to the SQL Server.
Can you please shed some light on this.....?
Thanks
Thursday, November 8, 2012 1:31 AM
Maybe there were many equipments connecting the SQL server, but you had limited the amount of users. Enlarge the number and try it again.
"provider: TCP Provider, error:0" means the login amount exhausted
thanks.
Thursday, May 25, 2017 10:12 AM
Sometimes this error comes due to overload of connections on SQL Server. So just change your DB to Single User and again change it to Multi user.
Or simply execute this query.
use master
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DatabaseName SET MULTI_USER
That solved my issue. :)
Enjoy..!!
Thursday, May 25, 2017 12:23 PM
That is not a solution, those commands simply disconnect all connections to the database.
The kind of problem is generally related to an "out of resources" problem. You need to determine the actual cause and fix it.
Friday, May 26, 2017 4:04 AM
Six year old thread.
J.