Close the Active connection of the DB before running web application

Posted on 2007-07-29
Last Modified: 2010-04-15

      I have a database created in MS SQL 2005, and I am using an 2.0 and C# to develop a system. When I open the database in Visual Studio Database Explorer or Microsoft SQL Management Studio and I forget closing the connection before running my web application, when the web application tries to read from the DB it cannot login to the DB. I know it is because that the DB is open somewhere. How can I close any open connection(i don't mean datareaders or dataadapters connection, i mean the connection which was opened for example using sql management studio to be explored) of the DB? I want something generic as well because the application has been designed to support different DBMSs.
Question by:afsheenarab
    LVL 16

    Expert Comment

    SQL Server is designed to handle numerous simultaneous connections to the DB, unless you somehow got put into single user mode, so this shouldn't really be an issue. What kind of error messages are you getting in your web application?

    Author Comment

    Cannot open database "TSRDb" requested by the login. The login failed.

    The name of the File Database is TSRDb.mdf, and when I have it opened in the Visual Studio Server Explorer the web application cannot connect to the DB and throws the above exception. When i close the connection to the DB from the Visual Studio Server Explorer then everything is fine. Please note that I have not defined any username and password for the database that I have created. the connectionstring I use is for testing under MS SQL Server 2005 Express is:

    "Data Source=.\SQLEXPRESS;AttachDBFileName=E:\TSRDb.mdf;Database=TSRDb;Integrated Security=True;User Instance=True"
    LVL 16

    Expert Comment

    Connect to your database in Management Studio express and navigate to the database you're using. Right-click on the database and go to properties. Click on the options page in the top left box and then scroll all the way down to the bottom of long list of options. The last option is "Restrict Access". Make sure that is set to MULTI_USER then click OK. Do this for each database you might be connecting to.

    Now let's check on the server itself. In the object explorer window select your server which will be the root menu item and should look something like "COMPUTERNAME\SQLEXPRESS". Right-click on this and go to properties. Navigate to the connections page and make sure that "Maximum number of concurrent connections" is set to 0.

    See if any of that helps.

    Author Comment

    Your reply was really useful and I really learned something from it. I did not know that these options exist and I am sure later on they will help me a lot. Unfortunately they did not help this time because all the settings are already configured as you said. I mean Restrict Access = MULTI_USER and Maximum number of concurrent connections = 0

    I think there should be a way to do it in the Application_Start of the global.asax, maybe I can find the instances of the db and check if it has an open connection or something. It is really stupid i guess. Because I have this problem when the ASP.NET Development Server is up (imagine I have started the web app, and then close it, and the ASP.NET Development Server is up still) I cannot open the Database in the Server Explorer because of the same error I have mentioned. Probably it is another microsoft security stuff, but still I am sure there is a way to solve this.

    Thanks for your help btw
    LVL 51

    Accepted Solution

    Remove 'AttachDBFileName=E:\TSRDb.mdf;' from your connection string.  It may be the problem and isn't needed.

    Also, in the web application, using 'Integrated security' might not work as you intended.  It is easier to configure you web application to use a SQL account rather than Windows authentication.  Configuring windows authentication in IIS applications is complex.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B. provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
    Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now