none
Does SQL Server logs Database Connection Error ? RRS feed

  • Question

  • Hi,

     

    There is an application using ODBC connection with SQL Server 2008R2 as Backend.

     

     

    Business Analyst reports that some users have connection problem and cannot access the database at backend.  Our Network Administrator says that it is not a Network Problem as the error message is "Cannot connect to Database".

     

     

    In this way, the BA says that SQL Server must have some logging mechanism to log who / which connection fails and ask me to give her the log file.

     

     

    As far as I know, we only get Server Log in SQL Server and no such connection log (Please correct me if I am wrong).

     

     

    I just would like to seek your fellows’ input.

     

     

    Thanks

    Wednesday, August 7, 2019 6:21 AM

Answers

All replies

  • Can you look into the ERROR.LOG and post here the error message?

    Make sure that where you define the "Create a New Data Source to SQL Server" dialog/window, the incorrect "Server name:" is MIGHT specified. If for example  you have a named instance of SQL Server, type in the name of the SQL Server host, a backslash, and the instance name. Here is the format:  SQLHostName\SQLInstanceName


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 7, 2019 7:24 AM
    Moderator
  • There is a setting to log failed Logins, see Configure Login Auditing (SQL Server Management Studio)

    But failed Logins means here, the Client can reach SQL Server, only the Login Fails by missing permissions/wrong Password. If the Client can't reach SQL Server, then it can't log a failure.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by TonyJK Wednesday, August 7, 2019 10:16 AM
    Wednesday, August 7, 2019 8:26 AM
    Moderator
  • I find that we already enabled SQL Server Login Auditing with "Failed Login Only".  And we cannot find any Login Failure in ERROR.LOG file.

    May I ask what should be the next step in troubleshooting ?

    Thanks again

    Wednesday, August 7, 2019 10:17 AM
  • You can test the connection in the ODBC Settings, on the very last dialog of the wizard there is a test button.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 7, 2019 11:30 AM
    Moderator
  • Business Analyst reports that some users have connection problem and cannot access the database at backend. Our Network Administrator says that it is not a Network Problem as the error message is "Cannot connect to Database".

    That sounds like an application-specific message. You would need to get your hands on the actual error message from ODBC.

    Do these users always get this error or is intermittent?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 7, 2019 9:17 PM
    Moderator
  • If your app cannot reach SQL Server in the first place, then SQL Server cannot log that (since SQL Server has no knowledge that something tried to reach it and failed). Talk to your network people and see what they can do for you. Logging in ODBC can be one option, but it is likely that you find just something like it cannot find the server. Here are a column of trubleshooting options from MS: https://support.microsoft.com/en-us/help/4009936/solving-connectivity-errors-to-sql-server and https://msdn.microsoft.com/library/mt750266.aspx

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, August 9, 2019 3:27 PM
    Moderator