3

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)

  • So, how did you verify that the instance name is correct, that it's running and configured to accept remote connections? – mustaccio Sep 15 '15 at 13:52
  • @kh aafi, have you check , you are giving name instance name is correct. for troubleshooting you can ref social.technet.microsoft.com/wiki/contents/articles/… – Md Haidar Ali Khan Sep 15 '15 at 13:56
  • Did you ensure that an actual instance of SQL Server Express is running? When you developed the database, it might have just been Visual Studio giving you a temporary runtime edition (or localdb), which is not the same thing as a permanent, running instance. – Aaron Bertrand Sep 15 '15 at 14:04
  • 1
    Next time, please hit Ctrl+C on the error dialog, and paste the text of the error message. We don't need a screen shot of your entire monitor, with the text of the error message so small we can barely read it anyway. A screen shot is also not searchable. – Aaron Bertrand Sep 15 '15 at 14:07
8

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):

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.