SQL Server connection problem
Posted by Sinclair Hughes on 15 September 2022 10:43 AM

If you are unable to establish a connection to the SQL database there are a number of steps you can try to trouble-shoot the problem.

SQL Server engine configuration

  • Is the SQL Server engine running.
    Use 'SQL Server Configuration Manager' to check the SQL Server engine is started.  This will sometimes stop and not restart automatically after a Windows update.  Right click for the Start option.
  • Is the correct account being used for the service.  If you haven't configured permissions for the account used when the SQL Server was installed, in Properties, try using the Built-in account of 'Network Service'.


Firewall

The main cause of connection failure is that the firewall on the SQL Server is blocking access .  

  • Turn the firewall off on the SQL server (temporarily).  If you now can connect, turn the firewall back on and start working through the access options
  • Make sure there is an entry in the firewall to allow incoming access via the port and protocol you are specifying (usually TCP 1433)
  • If you are using SQL Browser, make sure you open port 1434 using the UDP protocol
  • Check the remote workstations have their out-going ports open to allow the connection.

Use Telnet or a port query tool to determine the port availability

Connection Protocols

Run 'SQL Server Connection Manager' - SqlServerManager15.msc (for SQL 2019) or from the Start menu

  • open SQL Server Network Configuration > Protocols for MSSQLSERVER and make sure the TCP/IP protocol is Enabled.
  • Right-click to edit the Properties of the TCP/IP option.  'Go to the IP Addresses' tab. Scroll to the bottom of the list to get to the 'IP All' branch.
    • If you are using the SQL Browser, set the 'TCP Dynamic Ports' option to 0 (zero)
    • If you are using a specific port (usually 1433), make sure the port is specified in 'TCP Port' and that 'TCP Dynamic Ports' is completely empty.
  • When creating the ODBC connection to the database, make sure you are using the port specified or 'Dynamic Ports' option that is selected here.  You can't connect to port 1433 if only the SQL Browser (Dynamic ports) option is selected.
  • If you are using SQL Browser (Dynamic ports), make sure that the service is running.  Right click to start or run Services.msc to start from there.


Virus-checker

Check that the virus-checker isn't restricting access.  

SQL Authentication Mode

The Incisive applications use a SQL Server account to log into the database via the ODBC connection.  'Mixed Mode' or 'SQL Authentication' MUST be enabled for the SQL Server.

Run 'SQL Server Management Studio'.

  • Try connecting using the 'SQL Server Authentication' and the database login used for the Incisive application. 
    If this fails, it could mean the login doesn't have permission to connect to the database.
    Try using 'sa' login and password.
    If this fails, try connecting using the 'Windows Authentication option.
    If the Windows Authentication option fails, it means your login doesn't have permission to the database or the SQL Server engine isn't running.
    If the Windows Authentication option succeeds, it could mean the SQL Server was not installed with the SQL Authentication option and will need to be reinstalled with the option enabled.


  • Make sure 'SQL and Windows Authentication' is chosen in the Connection properties of the SQL Server engine.  Right click on the SQL server and select Properties

    In 'Security' select 'SQL Server and Windows Authentication mode'


  • Ensure unlimited number of connections are available.  Sometimes it is configured to only allow 1 connection.
    In the Properties of the SQL Server engine choose 'Connections' and put a 0 (zero) in the Maximum number of concurrent connections field.


  • Make sure the SQL Authentication logins have been created in Security > Logins


ODBC connection

The ODBC connector is used by the Incisive application to connect to the SQL Server.  Use the 32-bit ODBC driver.

  • Use 'System DSN' not 'User DSN' otherwise the ODBC connection will not be available for other Users who log onto the workstation
  • Select the 'SQL Server' driver, not 'SQL Server Native Client 11.0'
  • In the 'Server' field, enter in the exact same server\instance name that is in the SQL Server Management Studio
  • Choose the 'With SQL Server authentication...' option
  • Open 'Client Configuration' and choose 'Dynamically determine port' if SQL Browser is being used on the SQL Server, or enter the Port number
  • Use the login and password that has been enabled for the database.
  • Make sure you select the correct database.

Occasionally  the DSN server may not recognise the server\instance name

  • Replace the DSN name with the IP address & port number e.g. 192.168.0.15,1433
    Note that the comma is used as the separator.  If the port is used you do not need to specify the Instance

If you are still having problems connecting from the ODBC, try using the 'Windows Authentication' option (with an Administrator level login).  If this succeeds, then there is something incorrect with the SQL Authentication permissions for the SQL Server or database.