0

It seems this is a common problem over the internet so let me give you details on my environment and the steps I did:

Client - Windows 7 32-bit with SQL Management Studio Server - Windows Server 2008 R2 Standard with SQL Server 2008 R2 [Default not named instance] and SQL Management Studio For the server I added Application server role. I think I also added Web server role. The server and client are on the same LAN.

After installation and testing windows authentication to the SQL server, this is -in short words- what I did to enable SQL server remote login:

  1. Enabled and started all SQL Server services.
  2. Enabled TCP/IP for SQL Server. [I restarted the server to be sure after this step]
  3. Enabled TCP port access on the firewall.
  4. Allowed sqlservr.exe and sqlbrowser.exe on the firewall.
  5. Created another user rather than sa and give it sysadmin role

On the client machine, when I open SQL management studio, I tried to login with server IP address, server name, server name or IP with port [192.168.1.25,1443]. Username and password are correct.

After the previous explanation, I can't access the server's SQL Server remotely, I get the famous error "A network-related or instance specific". Notice that the same error message appear, but with different error codes after performing some steps from the above. I can't provide you with the error codes since I'm not with the machine right now. Any help is really appreciated! Thanks!

Improve this question
2
  • Your step 4 was done on the Windows firewalls? Do you know the port is opened on the server? If so, can you telnet to the port from the client machine?
    – user507
    Jan 19, 2015 at 1:20
  • I think I did it on the server but not the client firewall. I'll check that. What if telnet did not work ? I mean how to enable telnet on the server ?
    – 3bdalla
    Jan 19, 2015 at 6:35

1 Answer 1

Reset to default
1

SSMS uses port 1434 and the UDP protocol. This has to be allowed on the firewall, too.

Quote:

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

From here.

Improve this answer
5
  • Are you sure about that? The SQL Server Resolution Protocol (SSRP), specifically SQL Server Browser Service uses the UDP port 1434. You will not make client connections over that port.
    – user507
    Jan 19, 2015 at 2:06
  • @ShawnMelton - the question says he's using SSMS on the client machine.
    – Michael Green
    Jan 19, 2015 at 2:18
  • SSMS is a mute point, does not matter if I was trying to use PowerShell or a .NET application. As well, the OP stated they are working with a default instance so 1434 does not come into play in this situation. SQL Server Browser service can be disabled, and generally is during installation if a default instance is all that is installed. BOL:Configure the Windows Firewall to Allow SQL Server Access
    – user507
    Jan 19, 2015 at 4:06
  • On the client machine I turned off the firewall. I'll check more of this, thanks!
    – 3bdalla
    Jan 19, 2015 at 6:37
  • My friend found a batch script on Microsoft website that adds many firewall rules on many ports, it worked. I will get the script and post it as answer. Thanks all for the help
    – 3bdalla
    Jan 19, 2015 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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