MS-SQL Error: No such host is known - (Named Instances)

Randall Berg -

If you’re connecting the a Microsoft SQL database and you’re getting a ‘No such host is known’ error, then you’ve come to the right place.  If you’re getting this error then you’re trying to connect to a database with multiple SQL instances.  Before proceeding, make sure that the datasource you’re trying to connect to meets all of the following criterias: 

  • The database your connecting to has multiple instances

  • In the database IP field on BrightGauge you’re entering:

    • machinename\instancename

               or

    • 192.168.1.2\instancename  (192.168.1.2 is replaced with your database’s IP address)

The problem is that there’s a translation issue going from the agent with the database information and getting the data from the database.  Luckily we have been able to get a few fixes to this issue. 

 

 

Fix 1 - Disable Unused Instance (The fastest, but not always applicable) 

This fix has you turning off the default MSSQLSERVER instance.  This only works if:

  • you only have two instances

  • the default instance is not used

If you do not meet the above criteria, then do not proceed with this fix, move onto the next one. 

To turn off the default instance:

  1. log into the server hosting the SQL database.

  2. open up the SQL Server Configuration Manager

  1. Go to the SQL Server Services section (#1 in the screenshot below) and find the SQL Server (MSSQLSERVER) service (#2 below)

  1. Right click on the service and go to properties

  1. In Properties, go to the service tab #1), and under start mode (#2) switch to Disabled (#3)

  1. Click Apply and then click ok.

  2. Now you need to stop the service.  Right click on the SQL Server (MSSQLSERVER) service again and click on stop

  3. Now go back to BrightGauge and re-enter your credentials.  The difference this time is to remove the instance name from the database IP information:

    • If you had machinename\instancename or 192.168.1.2\instancename, before now you’ll use everything before the slash, so: machinename or 192.168.1.2 (IP address)
  1. Test the connection and if it works, then save the connection and you’re done! 

 

 

 

Fix 2 - Using Named Pipes

This fix is the one that most people will be able to use.  It requires a few things before you get started though: 

  • The agent MUST be installed and connected on the same server as the SQL database

  • You can restart the SQL Server service.  This means working on this fix should probably be done off hours 

  1. log into the server hosting the SQL database.

  2. open up the SQL Server Configuration Manager

  1. Go to the SQL Server Network Configuration (not the 32 bit version), #1 in the screenshot and then click on Protocols for your named instance (#2 in the screenshot)

  1. Find the Named Pipes section to the box to the right, right click on it and go to enable

  1. Right click on the named pipes section again and this time go to properties.  In the box that shows up, highlight your pipe name, copy, and paste the name to keep in your records for later.

  1. Now go back to the SQL Server Configuration Manager.  Go to the SQL Server Services section (#1 in the screenshot below) and find the service for your named instance (#2 below)

  1. Right click on the service and click on restart

  1. Once the service has restarted, go back to BrightGauge and enter in your credentials, except this time use the Named Pipes information that you saved from step 5 instead of the Database IP.  In the named pipes name, there is most likely a period after a double slash (\\).  Replace the period with your Windows machinename.  Also in the information from step 5, there’s a period at the end of the string.  In some cases, removing this period can help, so try with and without the period.  

    • instead of machinename\instancename or 192.168.1.2\instancename, use \\WindowsMachineName\pipe\MSSQL$namedinstance\sql\query

Also make sure that the agent IP and port is the IP and port from the agent that is configured on the MS-SQL server.

  1. Test the connection and if it works, then save the connection and you’re done! 

 

 

Fix 3 - Using an Alias

 If Fix 2 failed for you, then that work was not for loss.  We’ll use the Named Pipes setup and then use an alias to point at that named pipes.  If you still have the agent and Named Pipes setup still configured from Fix 2, then skip to #3.

 It requires a few things before you get started though:

  • The agent MUST be installed and connected on the same server as the SQL database

  • You can restart the SQL Server service.  This means working on this fix should probably be done off hours 

  1. The agent MUST be setup on the same box as the SQL server.  If you don’t, you’ll most likely get an error later where a connection cannot be made on aliasname:1433

  2. Setup Named Pipes (this is mostly the same as Fix 2)

a) log into the server hosting the SQL database.

b) open up the SQL Server Configuration Manager

c) Go to the SQL Server Network Configuration (not the 32 bit version), #1 in the screenshot and then click on Protocols for your named instance (#2 in the screenshot)

d) Find the Named Pipes section to the box to the right, right click on it and go to enable

 

e) Right click on the named pipes section again and this time go to properties. In the box that shows up, highlight your pipe name, copy, and paste the name to keep in your records for later.

 

f) Now go back to the SQL Server Configuration Manager. Go to the SQL Server Services section (#1 in the screenshot below) and find the service for your named instance (#2 below)

 

g) Right click on the service and click on restart

 

  1. Now that the service has restarted, we can create an Alias.  In the SQL Server Configuration Manager, expand the SQL Native Client 11.0 Configuration (32 bit) section (#1) and then go to Aliases (#2).  Doing the 32 bit Native Client configuration is imperative since the BrightGauge agent is a 32bit installation and will not be able to communicate with a 64bit alias.

  1. Right click in the box to the left and click on New Alias

  1. Now you’re going to create your alias using the Named Pipes setup from step 2e from this fix (step 5 from Fix 2).  

a) The Alias name (letter a) should be simple.  Just use letters and numbers, no special characters
b) Switch the Protocol (letter b) to Named Pipes.  This is a dropdown box and by default it’ll say TCP/IP.  If may not look like a dropdown box though, so just click on the box and it’ll activate.
c) In the Server Name box (letter c) put in the machine name and instance name.  So it’ll look like machinename\instancename.  You cannot use the machine’s IP address here.
d) The Pipe Name box should now autoconfigure to what the Named Pipes setup was under step 2e of this fix (step 5 from Fix 2).

  1. Click ok to finalize the alias setup.

  2. Now go back to BrightGauge and enter in your credentials, except this time use the Alias name that you created in step 5a instead of the Database IP.  Also make sure that the agent IP and port is the IP and port from the agent that is configured on the MS-SQL server.

  3. Test the connection and if it works, then save the connection and you’re done!

 

 

 

 

Fix 4 - Using a second NIC and second IP address to listen on port 1433

This fix is a bit complicated and should be only tried by those who understand how to do this without a walkthrough.  It requires setting up a second NIC on a box, something easier done on virtualized servers.  Have the new NIC associate with a different IP address.   Like Fixes 2 and 3, the agent must be configured on the same server, but make sure that the 'local ip' section of the agent is using the second IP address. Now go to SQL Configuration Manager and under the TCP/IP settings for the instance, use one of the IP sections (not IP ALL) to listen to communications from the new IP address using TCP port 1433 (forcibly add TCP Port 1433).

Everything on here are advanced configurations for SQL servers and should not be attempted without knowledge of how the server is setup.  That being said, everything done here can be walked back.

If you encounter any issues while attempting to follow this document, please let us know at support@brightgauge.com

Have more questions? Submit a request

Comments

Powered by Zendesk