Local Users and Groups in MS Server 2012

Local Users and Groups management console by typing lusrmgmt.msc in the Start screen and pressing Enter.

Connection to SQL Server requires TCP communication on port 50235?

Question:

We have an SQL Server 2008 R2 with a database instance running on port 1433. 1433 is open for TCP and 1434 is open for UDP.

When connecting through Microsoft SQL Management Studio, our firewall detects TCP traffic at port 50235. Why? The SQL Server does not mention this port as far as I can tell.

Ans1 :

Are you connecting to a named instance? In which case, the initial connection is to the SQL Server Browser service (UDP 1434) and then that dynamically allocates a port for named instances. This port is allocated on instance startup.

See this TechNet Resource on how the SQL Server Browser Service allocates a port for named instances.

There is a way to explicitly define a static port that a named instance will be listening on, but it appears that you are seeing a dynamically allocated port.

Ans2:

You sound like you may already know this but you can try doing a telnet to the sql server machine on port 1433:

telnet <hostname> 1433

If you get a blank screen (open connection) then your service is running on that port and there's no firewall in the way. If that's not working try:

telnet <hostname> 50235

If that works, then as the previous answer suggests you're configured for dynamic ports instead of 1433 (dynamic is the default for Express edition). You can change that in:

SQL Server Configuration Manager > SQL Server Network Configuration > TCP/IP (Right Click > Properties > IP Addresses.

In the IPAll section set TCP Dynamic Ports to blank (not 0) and enter 1433 for the TCP port)

How to enable remote connections in SQL Server 2008?

Article from: http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

You experience the following error message:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

How to solve this issue?

There are a couple of things that might be going on here… (All of the following configurations are made on the computer running your SQL Server 2008 instance)

Allow remote connections to this server

The first thing you want to check is if Remote Connections are enabled on your SQL Server database. In SQL Server 2008 you do this by opening SQL Server 2008 Management Studio, connect to the server in question, right click the server…

… and open the Server Properties.

Navigate to Connections and ensure that Allow remote connections to this server is checked. Check if this solves the problem. If it does, here you go, continue with whatever you were doing and have a nice day.

Protocols for MSSQLServer

If you’re still running in issues let’s dig a bit deeper. The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Make sure that TCP/IP is enabled and try again. Even though I hope that this resolved your problems there might still be an issue with…

The Firewall

If there is still no communication happening between your computer and the remote SQL Server you most likely need to configure your firewall settings. A good first step is to figure out which port is being used by TCP/IP (and which you need to open in your firewall). You can do this by right clicking TCP/IP and selecting Properties.

Click on the tab IP Addresses and voilà – Port 1433 it is :-) That was easy enough and all there is left to do is to allow inbound TCP/IP traffic on Port 1433 in your firewall. In Windows 7 this works something like this. Open the Control Panel and navigate to Windows Firewall.

Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.

This opens the New Inbound Rule Wizard which you can use to allow inbound traffic on Port 1433 for TCP/IP (and which is exactly how you configured your SQL Server in the steps above). Just follow the steps outlined below and you should be good :-)