0

From here:

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.

Apparently when UDL or SSMS used for connecting remotely to SQL Server instance name, the query for resolving the instance name's port number store's somewhere in client machine.

I tested this by two client machines. When the 1434 UDP port was open first machine could connect to SQL Server instance name. Then I closed the port and tried again with that machine. The first client still could connect without the port being open. Then I tried with second machine but it couldn't connect.

I just wondering how and where this caching takes place?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahmad
  • 129
  • 12

1 Answers1

1

Client APIs like SqlClient use connection pooling by default to avoid the overhead of name resolution, physical network connection, and authentication every time a connection is opened. When the initial connection is closed, the connection is added to a connection pool where it can be reused the next time another connection with the same attributes is opened. The client API in that case simply retrieves and unused connection from the pool avoiding the significant overhead of establishing the physical connection.

With a named instance, connection pooling also avoids the need to query the SQL Server Browser service every time a connection is opened so this explains your observations. I suspect if you exit and re-launch the application after blocking UDP port 1434, the SQL connection for the named instance will fail due to the failed SQL Server Browser data gram query during the initial connection open.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71