SQL Server login failure in middle of application run

Question

I have an application that suddenly started throwing the following exception:

System.Data.SqlClient.SqlException: Login failed for user 'username'.

The username and password are correct and the app does some queries/inserts using the same login before throwing the exception.

What are some other reasons a login can start failing? Does SQL Server have a default maximum number of connections that may have been reached? I'm using LINQ to SQL - does the fact that I'm reusing a DataContext to insert multiple rows of data rather than creating a new one each time have any relevant consequences?

Answer 1

Using the same DataContext for multiple sequential operations is just fine, but make sure you're not sharing the DataContext among multiple threads. If, for example, you're creating an ASP.NET application, make sure that each new request uses its own DataContext.

Answer 2

I think the issue was with the way I was creating DataContexts - I was keeping one around in the class and creating a new method-local one every time I did an update (see here for more details). I just refactored the code to create a new DataContext for each transaction and the error went away.