Catch on SqlException not Catching

1

I have a Visual Studio Express 2010 c# application using Ling-SQL. I'm getting a login failure, so I added a try-catch as follows:

try
{
Execute a Get stored procedure to read data from a table
}
catch(SqlException sqlEx)
{
log an error
}

But instead of hitting the SqlException, I get a pop up box:
SqlException Occurred
Cannot open user default database. Login failed.
Login failed for user 'Harry'

This code does work for a valid user. I am purposely creating this error because I need to catch SqlExceptions.

The other issue is that the error does not appear in the SQL Server log. Is there some option for logging that I am missing?

2

The database that "Harry" is set to use as a Default does not exist - its either been dropped or renamed.

I doubt that is catchable (in SQL code, you can catch it in Application code) as the user cannot connect to the database server at all - so it is not executing your SQL at all.

3

The database does exist because if I use a valid user login the code works.

4

Do you instantiate the SqlConnection in the Try/catch block?

5

Ah, sorry, I thought you were trying to catch this in SQL rather than in C#

6

You have to turn on an option for Failed Logins (and Successful Logins, if you want them to) to be included in the SQL Error logs)

7

Its in SSMS under:

Server's Properties - Security - Login Auditing

8

If it is indeed an SqlException that is thrown (and it does seem like it is from your description), I don't have an explanation. However, you could do the following, and then examine the details of the Exception object.

try
{
   Execute a Get stored procedure to read data from a table
}
catch(SqlException sqlEx)
{
   log an error
}
catch(Exception ex)
{
 // Examine the properties of ex here
 // to get a better understanding.
}
9

I did this but the SqlException still pops up so it's not caught by either catch(SqlException or Exception)

10

Thanks.
I set the option to log Successful and Failed logins. It logs the successful ones but not the failed one.

11

If you can, post your connection string. After you click OK on the popup, does the code fall into either of the catches, or does it continue past the catch?

12

Here is the connection string:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RvReporter.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

The popup is really an SqlException window so you can't get past it. The call to the stored procedure that causes the exception is a call to a stored procedure to read data from the InstallationData table. The line where it actually stops is in InstallationData.designer.cs which is the module built automatically by LINQ to SQL.

13

I don't know anything much about C# and exceptions via that programming route, so this may be off-target, but having been following the thread over the last couple of days it seems to me that the Exception is raised on attempting to create a connection to the DB, and that it doesn't get anywhere near actually executing the Sproc

I read something the other day (and thought it was too trivial so I didn't mention it at the time) which suggested just doing an "Open Database Connection" to test if the connection string (and therefore Login) was valid, and handling the Exception on that. Maybe that would provide a catch-able exception.

One thing we (also) check for/Catch at that point is Database Offline / Not available. This enables us (on a website) to display a useful message to the user saying "Database offline for maintenance, please try later" (of course the APP doesn't know if it is "for maintenance", it might be "Hosed"!!, but I don't want to get the users all excited!)

14

That makes sense and could be why the failed login isn't registered but it doesn't explain why the C# code isn't
catching the SqlException. Maybe I need to post on the Visual Studio C# forum.

15

Here's the code I found via Google, sorry but no idea if it is any help / relevant.

Dim sqlConn As SqlConnection = New SqlConnection("your_conn_string")

Try
sqlConn.Open()
Catch ex As SqlException
If ex.Number = 1234 Then	' replace 1234 with proper sql code
...
End If
End Try

http://www.justskins.com/forums/catch-sql-login-failure-53536.html

Also possibly this:
http://stackoverflow.com/questions/26311138/how-to-handle-sql-exception-for-logon-failure

17

I have discovered that I have 2 other applications that use the same method and they both catch the error. I've traced all of them and the execute the same path of code and fail at the same point. But 2 of them catch and the other one stills pops up the exception window.

I've put a post out on the MSDN Visual Studio C# forum.

18

Is the call to SqlConnection inside the try/catch, or does it look like Kristen's example?

19

APP programming not my thing, so just curious:

Are you thinking that the error might be in

SqlConnection = New SqlConnection("your_conn_string")

rather than in

sqlConn.Open()

??

Thanks :smile:

20

Yes, due to the nature of the error message

Login failed for user 'Harry'

21

So there is some actually connection to the DB made in the assignment using NEW?

I had assumed that just allocated memory etc. and there was no actual "Hello SQL" and ACK until the sqlConn.Open() call.

Its only of academic interested to me to understand how the things actually behaves.