Sql-server – Login to SQL Server 2008 R2 remotely

sql serverwindows-server

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!

Best Answer

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.

Related Question