Home / server / Questions / 502895
Accepted
ojek
Asked: 2013-04-27 05:25:06 +0800 CST

How to connect to ms sql express?

  • 772

I have sql express on my local network computer, I created new account, I enabled server auth, and when I am trying to connect to this account from local machine, everything works fine. But when I try to log in from a network pc, it says that login failed for this account. Also, I have disabled all the firewalls.

What else do I need to do?

sql-server

3 Answers

  1. Best Answer

    A fresh install of SQL Server Express will not be functional. You must take additional, manual, steps to complete the installation.

    1. Enable TCP/IP.

      By default SQL Server will not be a functional server. For that you need to make it a server by enabling network protocols.

      Click Start, SQL Server Configuration Manager. Under SQL Server Network Configuration, Protocols for MSSQLSERVER you can see that tcp/ip is disabled:

      enter image description here

      Right click TCP/IP and select Enable. Then you will need to restart the SQL Server service (or reboot the machine if you don't have permission).

    2. Enable TCP/IP.

      By enabling TCP/IP you should be able to connect to the server remotely on port 1433. Unfortunately the Sql Server Configuration Manager does not do everything that is required to enable TCP/IP; it doesn't enable port 1433 on the firewall.

      Click Start, Windows Firewall with Advanced Security. Right click Inbound Rules, and click New Rule.

      • Under "What type of rule would you like to create", select Program, and click Next

      • Select This Program, browse to the path to sqlservr.exe, e.g.

        C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

        and click Next.

      • select Allow this connection, and click Next

      • ensure Domain, Private, and Public profiles are checked, and click Next
      • give the rule a name, e.g. SQL Server 2012 Express, and click Finish

      The Windows firewall may, or may not, start allowing connections to an existing listening socket. You may, or may not, have to start the SQL Server service (or reboot the machine if you don't have permission).

    3. Enable Named Pipes

      The above steps didn't work for me, because SQL Server Management doesn't know how to connect over TCP/IP. You must also try enabling named pipes.

      Click Start, SQL Server Configuration Manager. Under SQL Server Network Configuration, Protocols for MSSQLSERVER you can see that Named Pipes is disabled:

      enter image description here

      Right click Named Pipes and select Enable. Then you will need to restart the SQL Server service (or reboot the machine if you don't have permission).

    • 2

  2. I have installed normal instance of sql server (not express), everything works fine on this new instance...

    • 0

  3. The tricks are as follows:

    • Check the errorlog for the problem instance. e.g. the errorlog file here: "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\" (or wherever your instance is)

    There will be lines in there "listening on..." e.g. Server is listening on [ 'any' 1129].

    • This tells you to connect to that ip on port 1129. In sql client, this looks like this:

    servername,1129

    or by ip

    192.168.66.101,1129 // assuming server is on that ip

    • If there is no "listening" line in log, then use the server config tool to enable IP on that SQL instance
    • 0