Sql-server – Why has the database connection failed

connectivitysql serversql-server-2012

Recently I have developed software using SQL Server 2012. Now when I try to connect, I get this error message:

Cannot connect to AAFI\SQLEXPRESS

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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

Best Answer

This is an extremely common error, with literally millions of search results online. The typical troubleshooting steps include:

  • Make sure the SQL Server service is actually running (SQL Server Configuration Manager > SQL Server Services > check the State of the specific instance and make sure it says "Running", otherwise right-click > Start).
  • Make sure the SQL Server Browser service is running, you can check this in Configuration Manager as above, or using sc query sqlbrowser from the command line (if it is not running, right-click > Start or run net start sqlbrowser from the command line).

  • If you are trying to connect to a different machine (or using a name that forces you to route outside of your machine for some reason), make sure the server is configured to allow remote connections. You can fix this by using remote desktop to connect to that server and running this locally:

    EXEC sys.sp_configure N'remote access', 1;
GO
RECONFIGURE WITH OVERRIDE;

    Of course this might be hard to do if it's the local instance you can't connect to, so you'll have to get connected locally using other steps in this list first before you'll be able to talk to the server to change this setting.

  • Make sure the port SQL Server is running on is open, and that TCP/IP is enabled (SQL Server Express Edition, for example, expects Named Pipes for remote connections by default). You can see this in Configuration Manager > SQL Server Network Configuration > Protocols for <instance name> > TCP/IP, right-click, properties, IP Addresses tab, scroll all the way down to see (a) that TCP/IP is enabled (and if not, right-click > Enable, may as well try enabling Named Pipes too), and check what port is listed under IPAll > TCP Dynamic Ports or TCP Port. Note that if it's using dynamic ports, that port could change, and if SQL Browser is not configured to start automatically, you'll be right back here potentially on your next reboot.
  • Make sure the server name is correct, and be sure to try IP address, simple server name, and fully-qualified domain name. If the server is local, try localhost and 127.0.0.1 in addition to the server name. Make sure you can traceroute/tracert to the server and telnet to the running port. If you can connect to one of these hosts but not others, either change your connection string, change your hosts file and/or DNS, or consult your network administrator.
  • Make sure the instance name is correct. Often people type their machine name and don't realize that they are trying to connect to a named instance, which requires syntax like server_name_or_ip\SQLEXPRESS - a common one as SQLEXPRESS is typically the instance name in use when running SQL Server Express Edition.
  • If you can't connect using any of those methods, it is likely a firewall issue. You'll need to determine which ports to open and for whom (of course your SQL Server should be secured from external traffic whenever possible), but you can quickly test if this is the problem by momentarily disabling the firewall altogether.

A lot of this is documented much more elaborately here (found in Paul White's wiki post here):

Related Question