locked
Access to SQL Migration Error, What is the answer to error occurance while connecting to server error 40 Provider instance? RRS feed

Answers

  • Hi RodSQL,

    As your description, you come across the error 40. Does this error occur when you connect to SQL Server? If so, please help to post full SQL Server error log according to Scott's post. By default, SQL Server error log file locates in C:\Program Files\Microsoft SQL Server\MSSQL<version number>.MSSQLSERVER\MSSQL\Log.

    Based on my research, there are many causes of the error 40, such as the SQL Server service is not running, TCP/IP is not enabled, or TCP port isn’t added to exceptions in firewall, and so on. However, the causes differ between a local connection and a remote connection to SQL Server instance.

    If you just connect to a local SQL Server instance, please follow the steps below to troubleshoot the issue:

    1. Make sure that TCP/IP protocol (named pipes, or shared memory) is enabled in SQL Server Network Configuration node of SQL Server Configuration Manager (SSCM).

    2. Make sure that service SQL Server<instance name> in SQL Server Services node is running.

    However, if you connect to a remote SQL Server instance, besides the steps above, please pay attention to the points below.

    1. Make sure that the Network is workable and stable. You could test the Network connection using the PING grogram: ping <Server Name>.

    2. Make sure that TCP port number of the instance and UDP port number for SQL Server Browser Service are added to exceptions in firewall. For more information about the process, please refer to the article: http://technet.microsoft.com/en-us/library/ms175043.aspx

    3. Make sure that SQL Server Browser service is started in SSCM.

    4. Make sure that ‘Allow remote connections to this server’ is checked in Server Connections properties in SQL Server Management Studio (SSMS).

    For the detailed troubleshooting steps, please refer to this article:  http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    Regards,
    Michelle Li

    Tuesday, December 2, 2014 10:06 AM

All replies

  • Let's start by providing a complete error message, exactly as displayed.  Context helps as well.  You posted in migration - is this issue related to migration?  What were you trying to do when the error occurred?  What version and edition of sql server are you using?  You mentioned "servers" - plural.  Are there multiple servers in this situation?  You mention "Access" - is that used as a verb or as a reference to the MS Access product? 
    Monday, December 1, 2014 4:59 PM
  • Hi RodSQL,

    As your description, you come across the error 40. Does this error occur when you connect to SQL Server? If so, please help to post full SQL Server error log according to Scott's post. By default, SQL Server error log file locates in C:\Program Files\Microsoft SQL Server\MSSQL<version number>.MSSQLSERVER\MSSQL\Log.

    Based on my research, there are many causes of the error 40, such as the SQL Server service is not running, TCP/IP is not enabled, or TCP port isn’t added to exceptions in firewall, and so on. However, the causes differ between a local connection and a remote connection to SQL Server instance.

    If you just connect to a local SQL Server instance, please follow the steps below to troubleshoot the issue:

    1. Make sure that TCP/IP protocol (named pipes, or shared memory) is enabled in SQL Server Network Configuration node of SQL Server Configuration Manager (SSCM).

    2. Make sure that service SQL Server<instance name> in SQL Server Services node is running.

    However, if you connect to a remote SQL Server instance, besides the steps above, please pay attention to the points below.

    1. Make sure that the Network is workable and stable. You could test the Network connection using the PING grogram: ping <Server Name>.

    2. Make sure that TCP port number of the instance and UDP port number for SQL Server Browser Service are added to exceptions in firewall. For more information about the process, please refer to the article: http://technet.microsoft.com/en-us/library/ms175043.aspx

    3. Make sure that SQL Server Browser service is started in SSCM.

    4. Make sure that ‘Allow remote connections to this server’ is checked in Server Connections properties in SQL Server Management Studio (SSMS).

    For the detailed troubleshooting steps, please refer to this article:  http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    Regards,
    Michelle Li

    Tuesday, December 2, 2014 10:06 AM