Saturday, July 25, 2015

Can we connect to a named instance without Browser Service running?

SQL Server Browser Service is one of the services installed when SQL Server is installed. Generally this service is required to be running for accessing named instances since ports or named pipes are assigned dynamically to named instances. 

At the start, it claims UDP port 1434 and collects all ports and named pipes for all instances reading the registry. When a client makes a requests to an instance, the client network library sends a UDP message to the server using port 1434 and then Browser service responds with the correct port number or named pipe of the requested instance. Client network library uses the received information for completing the connection and then communicate with required instance.

For mor info on Browser Service, read this: https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

As per above fact, we cannot access a named instance if Browser service is not running. But there are certain situations that allow us to make the connectivity to instances even when the services is disabled. An instance can be access when;
  • Connecting to the default instance that uses port 1433 or pipe \sql\query.
  • Connecting to an instance that uses a specific port number.
  • Connecting to an instance using a connection string that contains the port number.
Here is an example of it. I have two named instances called DINESH-PC\SQLTest and DINESH-PC\SQLTest2. Protocols of Shared Memory and Named Pipes are disabled for both and SQLTest instance is set with dynamic ports and SQLTest2 is set with specific port. 




Let's disbale Browser Service and test.



Connecting to SQLTest


Connecting to SQLTest2



No comments: