locked
sql express problem RRS feed

  • Question

  • User-1260064810 posted

    I have a sql express database runs fine on one server (2008), then I moved the entire website including the sql express file to another machine (also 2008). I created asp.net 4.0 in the application pool and used the windows network service account.  I also set appropriate file permissions for network service to the sql express file. Everything worked fine for a while, then for some reason I cannot access the database.  I did something more, trying to fix it, basically, remove and recreate the entries in the application pools.  Now, I just cannot make it work.  The error message I get is "404 file or directory cannot be found" if I enable asp.net 4.0 class. 

    If using ASP.NET v4.0 integrated, I got 

    CREATE DATABASE permission denied in database 'master'.
    An attempt to attach an auto-named database for file D:\inetpub\wwwroot\App_Data\RefSchedule.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Monday, July 14, 2014 11:05 AM

All replies

  • User-1260064810 posted

    The sql express file was created using Visual Studio 2010 on one server (virtual machine), and copied to another, then, I try to open the file using Visual Web Developer 2010 Express, but I can't.  I notice that I actually can only create .sdf sql compact file, not .mdf sql express file.  

    Is it simply impossible to make it work because I don't have Visual Studio 2010 on that server?

    This is one of the reasons, I don't like Microsoft.  I spend so much time on such a stupid stumble.  Why doen't Microsoft just say sql express will not work without vs studio?  Now, MS seems to push for sql compact.  But that one would not include view, stored procedure, etc.

    I go back to my PHP.  I am mad.

    Monday, July 14, 2014 5:03 PM
  • User1918509225 posted

    Hi comego,

    According to your post,it seems that your problem is related with permissions.

    Generally the user that you are using to run the SQL Server service will not have access to your personal user folders, that is why you're getting the error. 

    You either need to change the credentials used for the service, or move the database to another folder, which did the trick in your case.

    More details please refer to the link below:

    http://stackoverflow.com/questions/14872/ado-net-errror-create-database-permission-denied-in-database-master-an-att

    Best Regards,

    Kevin Shen.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Tuesday, July 15, 2014 1:52 AM
  • User-1260064810 posted

    Checking file permission was the first thing I was looking into while troubleshooting.  The .mdf file is in App_Data folder.  I have assigned network service account with full control.  What else I might have missed?

    Tuesday, July 15, 2014 10:11 AM
  • User-1260064810 posted

    Now, I come to a point that the page would be shown correctly once in a while, and stay on for some time, then, it could break again and stays on broken for some time until it suddenly works again.  

    Any clues?

    Tuesday, July 15, 2014 5:27 PM
  • User1918509225 posted

    Hi comego,

    first ,According to your description, do you mean your problem will occur occasionally.not all the time.

    second, have you tried to move your database file to new folder.

    Best Regards,

    Kevin Shen.

    Wednesday, July 16, 2014 3:11 AM
  • User-1260064810 posted

    Hi comego,

    first ,According to your description, do you mean your problem will occur occasionally.not all the time.

    second, have you tried to move your database file to new folder.

    Best Regards,

    Kevin Shen.

    Yes, it seems.  Most of the time, get "The wait operation timed out" message, occasionally accessing database seems to be ok.

    As I said, I first developed the app on a virtual machine using visual studio 2010, then move the site to another server.  The website structures are exactly the same.

    Wednesday, July 16, 2014 9:32 AM
  • User1918509225 posted

    Hi comego,

    It maybe caused when running very long queries .

    To avoid it altered the SQL command properties to increase the timeout and allow the command to wait longer before calling the timout.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

    Best Regards,

    Kevin Shen.

    Thursday, July 17, 2014 3:48 AM
  • User1918509225 posted

    Hi comego,

    Do you still have the problem?

    If not ,please tell me.

    Best Regards,

    Kevin Shen.

    Thursday, July 24, 2014 9:22 PM
  • User-1260064810 posted

    Kevin Shen - MSFT

    Hi comego,

    Do you still have the problem?

    If not ,please tell me.

    Best Regards,

    Kevin Shen.

    Yes, I am still struggled with it.  

    Now the error message is: 

    The system cannot find the file specified

    I have the connect string like this:  Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\App_Data\RefSchedule.mdf;Integrated Security=True;User Instance=True;" As I said, the code is working just fine on the other two machines: I first wrote the code on my windows 7 desktop with IIS 7 and vs 2010, then copy to a windows 2008 server (virtual machine).  The problem occurs when I tried to move the code to a real window 2008 machine.

    Things are getting complicated and confusing, because I tried things here and there, and it's been for many days now.  Hard to describe.  Look like I have to re-install the windows 2008 server and start from a complete scratch.

    Friday, July 25, 2014 10:47 AM
  • User-1260064810 posted

    I know I have hit the dead end, but want to ask the question one more time. 

    Other than the problems I posted above, I find I cannot use vs2010 to create a new sql server database (eg. database.mdf), the error I get is:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)

    Tuesday, August 5, 2014 10:52 AM