none
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired

    Question

  • What is the solution for below error:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired

    Is this problem with Network or with sqlserver?

    Thanks,

    Ron.

    Tuesday, December 18, 2012 3:32 PM

Answers

  • Hi Ron,

    This appears to be Network configuration related issue. I suggest you to check the following things:

    1. Make sure your database engine is configured to accept remote connections
    • Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
    • Click on Surface Area Configuration for Services and Connections
    • Select the instance that is having a problem > Database Engine > Remote Connections
    • Enable local and remote connections
    • Restart instance

    2. Check the SQL Server service account
    • If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

    3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
    • Usually the format needed to specify the database server is machinename\instancename
    • Check your connection string as well

    <connectionStrings>

    <add name="SampleConnectionString" connectionString="Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName="System.Data.SqlClient"/>

    </connectionStrings>

    4. You may need to create an exception on the firewall for the SQL Server instance and port you are using
    • Start > Run > Firewall.cpl
    • Click on exceptions tab
    • Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn, check your installs for the actual folder path), and port (default is 1433)
    • Check your connection string as well

    5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

    6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

    7. Check that you have connectivity to the SQL Server.
    Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
    • Start > Run > cmd
    •netstat -ano| findstr 1433
    •telnet myserver 1433
    •ping -a myserver

    Check what ports are IP addresses are being returned.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, December 18, 2012 3:37 PM

All replies

  • Hi Ron,

    This appears to be Network configuration related issue. I suggest you to check the following things:

    1. Make sure your database engine is configured to accept remote connections
    • Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
    • Click on Surface Area Configuration for Services and Connections
    • Select the instance that is having a problem > Database Engine > Remote Connections
    • Enable local and remote connections
    • Restart instance

    2. Check the SQL Server service account
    • If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

    3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
    • Usually the format needed to specify the database server is machinename\instancename
    • Check your connection string as well

    <connectionStrings>

    <add name="SampleConnectionString" connectionString="Data Source=machinename\instancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName="System.Data.SqlClient"/>

    </connectionStrings>

    4. You may need to create an exception on the firewall for the SQL Server instance and port you are using
    • Start > Run > Firewall.cpl
    • Click on exceptions tab
    • Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn, check your installs for the actual folder path), and port (default is 1433)
    • Check your connection string as well

    5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

    6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

    7. Check that you have connectivity to the SQL Server.
    Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
    • Start > Run > cmd
    •netstat -ano| findstr 1433
    •telnet myserver 1433
    •ping -a myserver

    Check what ports are IP addresses are being returned.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, December 18, 2012 3:37 PM
  • It is on MSSQLServer 2008 R2.

    Thanks,

    Ron.

    Tuesday, December 18, 2012 3:54 PM
  • Ron

    This solution also applies to any version of SQL Server. Just use SQL Server 2008 R2 Configuration Manager.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, December 18, 2012 4:01 PM