58

I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed.

I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS.

However when we come to connecting through any software or script using an IP Address it won't allow the connection.

I have tried:

  • Turning off the Firewall.
  • Allowing Remote Connections for the SQL Database.
  • Enabling TCP/IP within the SQL Configuration.

When we attempt to connect via the software 'SQL Server Management Studio', we get the following message:

Error Message:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

Can you please let me know when your free so we can take a look because I seem to be getting know where, I’ve amended the details as per some information UK Fast sent me but they have said "It’s not within the support remit", so they can’t help any further.

I look forward to hearing from you.

Improve this question
4
  • Have you tried appending the port number when connecting?
    – TTeeple
    Mar 31, 2014 at 13:00
  • Are you trying to connect to "192.168.0.1/SQLEXPRESS" (where 192.168.0.1 is really your server's IP address), or are you omitting the instance name?
    – Rowland Shaw
    Mar 31, 2014 at 13:27
  • 2
    Are you really using a forward slash / and not a back slash `\`?
    – Aaron Bertrand
    Mar 31, 2014 at 14:49
  • localhost\SQLEXPRESS Worked for me.
    – Don Dilanga
    Mar 3, 2021 at 12:11

5 Answers 5

Reset to default
112

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS.
When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your server without using instance name you have to reconfigure your server to use static TCP port. To do it please perform the following:

  1. open SQL Server Configuration Manager;
  2. switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
  3. double-click the TCP/IP protocol;
  4. select the Yes value in the Enabled field;
  5. switch to the IP Addresses tab;
  6. find the IPAll section;
  7. clear the TCP Dynamic Ports field in that section;
  8. specify the 1433 value in the TCP Port field:
    enter image description here
  9. restart your server
    Try to connect to your server using just its IP address.
Improve this answer
7
  • 10
    Thank you for providing that information, it works just fine except for one thing. In sql management studio I still have to provide the port to connect to the server, like, x.x.x.x\instance,1433. I think that's sort of weird since I'm using the standard port.
    – Tomas Jansson
    Oct 22, 2014 at 17:35
  • 1
    It worked for me, except for exactly the opposite of what @TomasJansson reported: it works in the Management Studio without a port number, but I have to specify one in my connection string even tough I'm using the default port (1433).
    – lpacheco
    Jul 24, 2015 at 14:03
  • I addition to the excellent answer above, in SQL Server 2017, I realized that you can't leave the user (especially sa) password field blank. Make sure you have a password setup - otherwise it will not connect.
    – aLearner
    Sep 6, 2018 at 5:12
  • 1
    USE netstat -a -b and find the sqlservr.exe and be SURE that the port is 1433. Not always the case. You will go crazy otherwise.
    – jwize
    Jul 24, 2019 at 5:43
  • 1
    6 years later, still saving lives
    – Captain Squirrel
    Sep 29, 2020 at 10:52
21

Using SQL Server Express 2017, in addition to the answers above, when trying to connect using the port, ensure you use the comma syntax, not the colon syntax i.e:

 MyServerName,1433\InstanceName

After wrestling with the above for a couple hours, following all of the suggestions, and it still not connecting, I used the above instead, and it connected right away through the SQL Server Management Studio remotely. A first step to success.

Improve this answer
2
  • 1
    This fixed it for me (moving the port between the server name and instance name). Entering the port at the end didn't work.
    – masospaghetti
    Apr 27, 2018 at 19:46
  • Using SQL Server Express 2019, this in combination with this guide solved it for me knowledgebase.apexsql.com/…
    – datchung
    Jun 19, 2020 at 1:33
13

You should enable , following for me the issue solved by enabling following and restarting all service.

  • Shared Memory
  • TCP/IP
  • Named Pipes

SQL Server Configuration Named Pipes

Improve this answer
1
  • 1
    This one should have more likes!
    – Stephan Møller
    Nov 24, 2022 at 8:36
3

I was able to connect remotely by explicitly specifying the dynamic port number, however this is obviously not ideal.

I appears the problem was that the SQL Server Browser service was disabled. Once I enabled and started this service, I was able to connect remotely by simply specifying the host name and instance name.

Improve this answer
1
  • This is the simplest answer that worked for me! Thanks
    – F0r3v3r-A-N00b
    Mar 6, 2020 at 2:37
0

Just adding on to what has been previously said. I had the same issue of not being able to connect to the server (client and server on the same desktop) and I ran through all the steps listed here trying to fix it. However, I eventually found out what the issue was. All I had to do was go into SQL server management studio and click "SQL server services" and then single-click the server I needed to connect to. Then click the green 'run' button. I'm not sure if this was specific to me since my computer was both running and accessing the server, hope it helps anyone else out there though!

Where the run/start button is

Improve this answer
1
  • 1
    how on earth has this comment been so underrated? simple and fixed my issue asswell
    – Cavid Hummatov
    Jul 17, 2018 at 6:18

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