none
Can't connect to local SQL Server 2008 Database from VB 2010 Express

    Question

  • Hello,

     

    The following likely will make it obvious that I’m a newb, but I’ll start off by saying that my knowledge is very limited…

     

    I’m trying to create a VB tool to manage a couple of databases and I can’t connect to my own local databases using the connection wizard in VB.  My OS is Win7.  I’m running SQL Server 2008 R2 32-bit and am using Visual Basic 2010 Express.  When I browse the path from VB 2010 using the Database Configuration Wizard, I get the following error message:

     

    “Select SQL Server Database

    MyDatabase.mdf

    You don’t have permission to open this file.

     

    Contact the file owner or an administrator to obtain permission.”

     

    Well, since I am the file owner and the administrator, I’ve tried contacting myself to no avail…

    I don’t know if this is a windows security issue or a SQL security issue.  I presume it’s a SQL Server admin setting that needs to be set.  How do I set my databases to be read/write from my local machine?

     

    Thanks,

     

    Peter


    Peter
    Monday, February 21, 2011 3:16 PM

Answers

All replies

  • This one might help.

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/42707a11-d691-4a4c-bfc7-a9243a6e7168/

     

     

     


    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]
    • Marked as answer by JustMee Monday, February 21, 2011 3:56 PM
    Monday, February 21, 2011 3:37 PM
  • Dave,

     

    Thank you! 

    When I ran VB as Admin, I got access.  The problem was the file was in use, despite not being open.  Reseting the database Autoclose option to true as detailed in the link your provided fixed the issue. 

     

    Peter


    Peter
    Monday, February 21, 2011 4:00 PM
  • Good to hear. You're welcome.

     

     

     

     


    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]
    Monday, February 21, 2011 5:29 PM
  • This is because by browsing directly to the MDF file, you are using an User Instance to attach and serve the database file.  Each database must be attached to an instance of SQL-Server running (you can have more than one instance running on your machine if you want to) and usually, these database files are permanently attached and you connect to a particular instance and then, to a database on this instance.

    When a database file is not attached to an instance and that you have SQL-Server Express installed on your machine, you can choose to use an User Instance instead: these are temporary instance copy of the regular instance of SQL-Server Express that are made when you need them and that are used to temporarily attach and server the database file that you are asking for.

    However, even if they are temporarily and that they will be automatically closed (and the file detached) after one hour of inactivity (if I remember correctly), they will lock the database file when it is attached to them like any other regular instance of SQL-Server where the file is permanently attached.

    If you want to open a database file that is connected to an User Instance using another tool, you must make sure that this other tool will also open the file as an User Instance because the same file cannot be attached (and open) at the same time by two different instances.  This means that the dbAttachFileName parameter must also be specified for this second connection.

    An easier way is to permanently attach the file to an ordinary instance (with a specific database name) and connect to this instance/database instead of trying to connect directly to a database file.  The load on your machine will be less because a separate User Instance won't have to be created and you won't have any problem connecting to the same database from different tools at the same time.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    • Proposed as answer by Naomi N Tuesday, February 22, 2011 2:30 AM
    Tuesday, February 22, 2011 12:36 AM
  • This is because by browsing directly to the MDF file, you are using an User Instance to attach and serve the database file.  Each database must be attached to an instance of SQL-Server running (you can have more than one instance running on your machine if you want to) and usually, these database files are permanently attached and you connect to a particular instance and then, to a database on this instance.

    When a database file is not attached to an instance and that you have SQL-Server Express installed on your machine, you can choose to use an User Instance instead: these are temporary instance copy of the regular instance of SQL-Server Express that are made when you need them and that are used to temporarily attach and server the database file that you are asking for.

    However, even if they are temporarily and that they will be automatically closed (and the file detached) after one hour of inactivity (if I remember correctly), they will lock the database file when it is attached to them like any other regular instance of SQL-Server where the file is permanently attached.

    If you want to open a database file that is connected to an User Instance using another tool, you must make sure that this other tool will also open the file as an User Instance because the same file cannot be attached (and open) at the same time by two different instances.  This means that the dbAttachFileName parameter must also be specified for this second connection.

    An easier way is to permanently attach the file to an ordinary instance (with a specific database name) and connect to this instance/database instead of trying to connect directly to a database file.  The load on your machine will be less because a separate User Instance won't have to be created and you won't have any problem connecting to the same database from different tools at the same time.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    Many thanks for this explanation Sylvain. I have just been googleiing for an answer to this question when weyou are using the Express version of Visual Studio and Express.

    Now the question I want to ask is, how can I use the real SQL Express Instance within Visual Studio Express (VB 2010 64 bit, if really required). Of course, the Express editions only give you three options as a to connect to a Data Source (MS Access, SQL Server Compact, and SQL Server Database FILE), the later of which I believe is the "user" instance of the Primary SQLExpress instance.

    I would like to be able to utilise the VS environment to connect and interact with the data source AND use SSMS at the SAME TIME. This is basically for a teaching/learning exercise and the use of both methods to interact with the data would be useful.

     

    Cheers


    Chris
    Thursday, November 10, 2011 12:37 AM
  • Now the question I want to ask is, how can I use the real SQL Express Instance within Visual Studio Express (VB 2010 64 bit, if really required). Of course, the Express editions only give you three options as a to connect to a Data Source (MS Access, SQL Server Compact, and SQL Server Database FILE), the later of which I believe is the "user" instance of the Primary SQLExpress instance.

    I would like to be able to utilise the VS environment to connect and interact with the data source AND use SSMS at the SAME TIME. This is basically for a teaching/learning exercise and the use of both methods to interact with the data would be useful.

    Well, I believe I have solved this little one all on my lonsome.

    In the VS Express Product, in the "Add" or "Modify Connection" Dialog, after making sure that "MS SQL Server Database File" is selected in the "Data Source" field, then click the "Advanced" button at the bottom. In here, navigate to the last filed called "User Instance" and change the value to FALSE.

     

    I believe this is problem solved.


    Chris
    Thursday, November 10, 2011 1:36 AM
  • I've been trying to get VB 2010 Express to play nicely with SQL 2008 Express for some time now. Pain, innit.

    Well, EUREKA! Just don't ask me "exactly" how.

    I went to an ms web-page entitled "Walkthrough: Installing and Configuring SQL Servoer 2008 R2 Express with Advanced Services" (my account has not been verified, sorry - no links) and did everything - including the pre-requisites found by drilling down through links in the article (2 levels! Beware!) - to install AdventureWorks thinking that I would end up with a local instance of SQLSERVER (I even called it LOCALINSTANCE). I actually ended up with another Network Service for LOCALINSTANCE but, on the way, I'd installed the world and his brother. I also found that the SQL Browser was disabled in Services (?) so I enabled it and set it to automatic.

    I still had to run VB2010 as Administrator (s'funny. I am in the administrator group) but I was able to connect to databases in both instances (SQLSERVER and LOCALINSTANCE). Gotcha!

    After this I got rid of the LOCALSERVER instance and, apart from the Administrator annoyance, everything seems fine (so far. I stress, so far).

    Whether it was the Visual Web, the SQL Browser or something in the several Gigs of downloads that the above link recommended I do not know and - you can believe it - I have no intention of retracing my steps to see what fixed what. However, it worked for me and it might work for some of the many other people who have posted on MS and elsewhere with this problem.

    Maybe somebody at MS can take a look and see what, exactly, fixed the problem because there are a lot of folks out there who need to know and loading AdventureWorks, complete with pre-requisites, is quite a slog.

    Now what am I going to do with Visual Web Developer? I'm more Fortran and Cobol, and VB.
    Monday, December 16, 2013 5:45 PM