I recently built a single MongoDB server on a Ubuntu 16.04 LTS server, and I was trying to connect to it with mongoimport from a Windows machine.
C:\Program Files\MongoDB\Server\3.2\bin>mongo –host ubuntu-mongodb
MongoDB shell version: 3.2.8
connecting to: ubuntu-mongodb:27017/test
2017-11-26T17:22:21.224+0000 W NETWORK [thread1] Failed to connect to 192.168.1.150:27017, reason: errno:10061 No connection could be made because the target machine actively refused it.
2017-11-26T17:22:21.224+0000 E QUERY [thread1] Error: couldn’t connect to server ubuntu-mongodb:27017, connection attempt failed :
A local connection was working using the mongo shell, but not remotely, so I started puzzling about firewalls either on Windows or Ubuntu. As a further test, I tried a connection from another Windows machine – a Windows 10 laptop – with the same result. The remote Ubuntu server obviously responded to pings.
Drawing a blank with firewalls, I did start to consider whether MongoDB disables remote connections by default, something that has become increasingly common with database platforms for obvious reasons. I had a quick look at the mongodb.conf file, and the local loopback IP address stood out for the bind_ip parameter:
# Listen to local interface only. Comment out to listen on all interfaces.
I opened the mongodb.conf file in VI, commented out the line with bind_ip – and then spend far too many minutes wondering why it wouldn’t let me save it. I’d forgotten to sudo to VI.
From time to time, DBAs will be called in to investigate connection issues – “my application couldn’t connect to the database at 13:30…. was the database ok?”
Management Studio is great for validating connectivity, but it’s not easily portable. Port Query UI (portqry) is a convenient graphical tool that allows ports to be tested to see if SQL Server is listening – and can be connected to from a test workstation or server. It comes as a ZIP download, it doesn’t install anything, and you just need to run the EXE. The added bonus is that it’s not part of the SQL Server suite, so application teams can often be persuaded that it’s legitimate proof that SQL Server really is available and listening.
Once it’s running, you just need to enter the server-name or IP address, and then either provide the TCP/UDP port – or select the SQL Server drop down, which will try the default port of 1433 together with the SQL Server Browser port of 1434. Then just click on the Query button, and the results will be displayed at the bottom.
One of the almost daily pieces of DBA troubleshooting is to find out why someone (or something) couldn’t login – if you’re lucky, it’s a login failure, and will be in the SQL Server log.
SQL Server doesn’t given the full details back to the client as a security best practice, and even with SQL Server 2005 some of the error codes were cryptic…. hence every DBA will have run across Troubleshooting Error 18456 from the SQL protocols team. However, it hasn’t been updated.
SQL Server has got much much better at providing more informative error messages in the logs – SQL Server 2000 simply reported state 1 for almost everything!
| State|| Descripton
|1||SQL Server 2000 failure – no further information provided OR this error can occur when a SQL login is used, but the SQL Server is in Windows Authentication only mode
|5||No permission to login to SQL Server
|8||Incorrect password – since SQL Server 2005, passwords are case sensitive
|10||Issue with SQL logins? See KB925744
|11||Windows user validated, but no server access – sometimes a user is dropped and recreated in AD. Sometimes explicit DENY CONNECT
|12||Permission to connect to database engine is denied – eg Login failed for user ‘myUSER’
|13||SQL Server service paused
|16||No permission to default/specified database OR the default/specified database is not available OR the database name is mis-spelled OR the database is in restricted or single user mode OR this can occur in the time between SQL Server being ready for client connections and the specified database being available after recovery. OR this can occur in SQL2005 or later with schema permission issues
|23||SQL Server is shutting down
|27||Initial database could not be determined
|38||Initial database not available (SQL Server 2008)
|40||No permission to default database – either fix the default database, or specify the database to open. This error message is SQL Server 2008R2
|57||The password change failed. The password of the account must be changed.
|58||Are you trying to use a SQL login with SQL Server 2008 when the server only allows Windows authentication?