Step by step SQL troubleshooting

  1. Check in the Configuration Manager to see if the SQL Server and that the SQL Server Browser are running and are set to Automatic. If it is not running and start is greyed out follow the steps below to start the service.
    • Right click on the SQL Server Browser
    • Click Properties
    • Click on the Service tab
    • Set the Start Mode to Automatic
    • Click Ok
  2. If they are using SQLEXPRESS make sure the user has SQLEXPRESS as the instance name.
  3. Make sure the SQL server is in mixed mode
    • Open the SQL Server Management Studio Express
    • Right click on the server and choose Properties
    • Under Select a Page choose Security
    • Server authentication should be SQL Server and Windows Authentication Mode (this is mixed mode)
  4. Check to see that Shared Memory, Named Pipes and TCP/IP are enabled in the SQL Configuration Manager.
  5. Try putting ,1433 after the ServerName\InstanceName
    • Example : PJLM-Tech4\SQLEXPRESS,1433
  6. Enter 1433 into TCP Dynamic Port on the IP1 and IPAll
    • Open the SQL Configuration Manager
    • Go to SQL Server 2005 Network Configuration
    • Choose Protocols for SQLEXPRESS
    • Double click the TCP/IP protocol to open the properties page
    • Click on the IP Address tab
    • Enter 1433 into the TCP Dynamic port on the IP1 and IPALL sections
  7. Check to see that Remote Connections are allowed
  • Open the SQL Server Management Studio
    • Right click on the server and select Properties
    • Under Select a Page choose Connections
    • Check the box to “Allow remote connection to this server”
    • Restart the SQL server

NOTE: In the Log directory of the MSSQL instance there will be an ERRORLOG file, for many types of connection problems more information about the specific issue can be obtained by looking up the state, severity and error code of the message that appears in this file when connection problems occur.

How did we do with this article?