SQL server

Finding instances on SQL server.

I was helping a client to connect to a SQL instance on a server. This was a named instance, and we were getting an error: “…. sql network interface error 26…”. It took me a bit of time, but I finally realized that there was a typo in the long FQDN server name\ instance name combo. This is how I stumbled upon a method to discover all the SQL instances available on a SQL server.

As per Microsoft:  “Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these information, client does know how to connect the server and it fails with this specified error message. ”

So, querying port 1434 on the server in question, will return info that is required for client connections. Info, such as all the SQL instances on the server. The free Microsoft tool for querying a port can be found at:  PortQry Command Line Port Scanner Version 2.0.

General syntax (not for V2): portqry.exe -n servername -p UDP -e 1434

Provide only the server name without any instance names. Version 2 works differently, but as of this writing, the older version is also packed with the download. I am having issues querying a clustered server, so right now I am still experimenting with my new findings.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s