Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Close the Active connection of the DB before running web application

Hi,

      I have a database created in MS SQL 2005, and I am using an asp.net 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.
0
afsheenarab
Asked:
afsheenarab
  • 2
  • 2
1 Solution
 
TSmoothCommented:
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?
0
 
afsheenarabAuthor Commented:
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"
0
 
TSmoothCommented:
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.
0
 
afsheenarabAuthor Commented:
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
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now