Unable to access database simultaneously from application and SSMS

0
votes

I have a database in the App_Data directory of my ASP.NET(C#) application. If I open the database in Sql Server Management Studio I cannot access the database from my application. If I close the SSMS connection and try to access the database from my application it works but then I cannot use the database in SSMS after reconnecting. I have checked that "Restrict Access" option is set to MULTI_USER in Database properties.

EDIT: The error I get in ASP.NET page when SSMS is running is:

Cannot open user default database. Login failed. Login failed for user 'ravi-PC\ravi'.

When I close SSMS(I have to close SSMS, disconnecting Object Browser Doesn't Work) access the ASP.NET page the ASP.NET Page works fine.

When I reopen SSMS and try to access the database the error I get is:

Unable to open the physical file "File path" Operating System Error 32: "The process cannot access the file because it is being used by another process" (Microsoft SQL Server Error: Error 5120)

Edit 2 : This question is not a duplicate as I am neither using LocalDB or remote server. I restarted the SQL service and now get a different error. I am able to connect but when trying to access the Tables I get this error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

Cannot open database "C:\USERS\RAVI\SOURCE\WEBSITES\FIRSTTRY\APP_DATA\MYDATABASE.MDF" requested by the login. The login failed. Login failed for user 'ravi-PC\ravi'. (Microsoft SQL Server, Error: 4060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4060&LinkId=20476

2
sql-serverssms
Please define "cannot access". What error messages do you get? – Alex
Updated the question with errors. – Ravi
Possible duplicate of C# VB Simultaneous connections LocalDB Database – Alex
I checked that question. I am not using LocalDB but SQL Server Express. – Ravi
What is the value of Server-Properties -> Connections -> Max number of concurrent connections? – MatSnow

2 Answers

0
votes

The AttachDbFilename and User Instance options are used to spin up a single user instance of SQLExpress, attached to this specific MDF-File.

You wrote that you're not using LocalDB but a "normal" SQLExpress-instance instead. So the MDF-File is locked, because it's already attached to the SQLExpress-Instance.

To get it working, change the connection string to:

"Server=.\SQLEXPRESS;Database=MyDatabase;Integrated Security=True;"
0
votes

The solution was really simple, in Visual Studio I had to find the server in SQL Server Object Explorer from there I got the connection string which works as I wanted to.