Chapter 13, p444, Access is denied

Brian419 · December 13th, 2016, 12:06 AM

Hi,

Page 444, step 6. Double-clicked the PlanetWrox.mdf file and got the following error:

Unable to open the physical file
âC:\BegASPNETCS\Site\App_Data\PlanetWrox.mdfâ. Operating system error 5: â5(Access is denied.)â.
An attempt to attach an auto-named database for file C:\BegASPNETCS\Site\App_Data\PlanetWrox.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm using Windows 7, SQL Server 2012 Express, and VS Community 2013.

Successfully completed chapter 12. Fyi, I log into SSMS with sa authentication.

Any help you can provide would be appreciated!
-Thanks!!

Imar · December 14th, 2016, 06:26 PM

Hi there,

Take a look at the following posts: https://www.google.com/#q=site:p2p.w...+UNC+share.%22

Other people reported similar problems with different solutions so you'll have to try them out and see which one works for you.

Cheers,

Imar

Brian419 · December 18th, 2016, 08:08 PM

Thanks for the links Imar. However without a lot of experience with this type of error I'm not exactly sure what applies to my situation and what doesn't. I'm still researching & re-reading the appendix as well, but hoping you might be able to provide some further guidance & direction?

This is just a guess, but is it possible I'm getting the error because when I created the PlanetWrox database I was logged into SSMS using the sa account? Could I just log in as sa, delete the existing PlanetWrox database, log out and then log back in using Windows Authentication and recreate it (Ch 12, TIO, p429, step 1)?? If so, is there anything I would need to do in the VS Site project first before beginning that?

Below are some notes from when I installed SQL Server & VS (both were installed prior to starting the book):

SQL Server 2012 was installed first:
SQLEXPRADV_x64_ENU.exe. Express with Advanced Services.
Stand-alone installation.
Selected all the features to be installed, except LocalDB.
Installed as a Default instance. Not a Named instance.
Mixed Mode (SQL Server authentication and Windows authentication).

Visual Studio Community 2013 was installed months later. I selected all of the optional features to be installed which included: Microsoft SQL Server Data Tools.

This is my own home PC (Win 7 pro), and I'm the only user.
-Thanks for any help you may be able to provide!

Imar · December 19th, 2016, 04:17 AM

Yeah, that's definitely worth a try. Alternatively, you could detach it instead of deleting it so you can keep your data.

Cheers,

Imar

Brian419 · January 3rd, 2017, 08:23 PM

Happy New Year!
Well, unfortunately deleting the PlanetWrox db I had created in SSMS under the SA account and recreating it using the Windows Authentication account did not work. Even using Run as Administrator on VS, I still get the same exact error.

So, I've been exploring permissions. The App_Data folder (inherited permissions) has the following:

Group or user names:
Authenticated Users
SYSTEM
Administrators
Users

Permissions:
Full control
Modify
Read & execute
List folder contents
Read
Write
Special permissions

I'm the owner: Brian (computerName\Brian), and belong to both the Users and Administrators groups. The Users group has the least permissions: Read & execute, List folder contents, and Read.

I'm assuming the os is denying access to the folder, not necessarily the .mdf file itself? But to whom is it denying access, me or VS? Is VS a 'User' or some other entity, or am I looking at it incorrectly? So which user or group should I modify permissions for, and which permissions should I add? I'm assuming the Users group, but which additional permissions? I'm also assuming if it doesn't work I can always change these back? While annoying, it's still a great learning opportunity!

Many thanks for your help! This is a really great book, and I am determined to get through all of it!
-Thanks, Brian

Imar · January 6th, 2017, 01:01 PM

Did you try assigning permissions to Everyone temporarily? If it works then, it's a permissions issue. If it doesn't, it's something else.

Did you follow the link I sent and the articles from the search results? Some articles seem to imply it's a machine issue and deleting a folder from your local profile folder seems to do the trick.

Cheers,

Imar

Brian419 · January 12th, 2017, 02:19 AM

Imar hi,
Before you replied last I had been thumbing through chap 19 (even though I'm not there yet), p742, and decided to try granting Modify permissions to the Users group for the App_Data folder. Wasn't able to do so without first changing the owner of the .mdf file from MSSQLSERVER to the Users group, which added Modify and Write permissions automatically to the Users group for the folder. Still same error. Decided to change the owner back, but got an error that the name MSSQLSERVER could not be found. So, ended up deleting/recreating the PlanetWrox db in SSMS. Interestingly those Modify and Write permissions for the Users group remained. But still same error (even with Run as Administrator).

Ok, so checked my SQL Express install notes. Discovered I had selected Default (not Named) instance, and had left the Instance ID as MSSQLSERVER (the default). Didn't know any better at the time. Just assumed the default ID name would be ok.

Today, after going through more of those links, in VS I went to Tools -> Options -> Database Tools -> Data Connections and the SQL Server Instance Name is: (LocalDB)\v11.0.

Same thing in Server Explorer -> Data Connections, right-click PlanetWrox.mdf and Modify Connection shows Data Source: (LocalDB)\v11.0, and Properties shows Connection String:
Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\BegASPN ETCS\Site\App_Data\PlanetWrox.mdf;Integrated Security=True.

I was getting ready to try adding the Everyone group to the App_Data folder next, but after discovering this wanted to check with you and see if I should try changing the Data Source name to MSSQLSERVER first? If so, where's the best place to change it (Tools-Options, Modify Connection, Properties), or doesn't it matter?

Thanks again for all of your help!
-Brian

Imar · January 12th, 2017, 02:50 PM

>> Ok, so checked my SQL Express install notes. Discovered I had selected Default (not Named) instance, and had left the Instance ID as MSSQLSERVER (the default). Didn't know any better at the time. Just assumed the default ID name would be ok.

That sounds more like a regular instance of SQL Server, not Express. Are you sure it's Express? Can you log into your SQL Server using SQL Server Management Studio? If so, try running the command @@version in a query window and look at the output which should tell you the version of SQL Server.

If you did indeed install a standard version of SQL Server, your instance is probably called . (just a dot) or the name of your machine.

>> (LocalDB)\v11.0

Besides the full versions and the Express edition, LocalDB is yet another version of SQL Server. With that set up in VS, it should work too. Try using (LocalDB)\v11.0 wherever you see references to .\SqlExpress.

Hope this helps,

Imar

Brian419 · January 12th, 2017, 09:12 PM

Yes, I do log into SQL Server Management Studio. When I run the query: select @@version, the result is:
Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
Sep 23 2016 15:49:43
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I installed it back in March of 2015 for use with a MS Press book (MS SQL Server 2012 T-SQL Fundamentals). So I've used SSMS a lot with that book since then.

I had selected the download: SQLEXPRADV_x64_ENU.exe 1.9GB. I take screen shots whenever I install anything so I can see the selections I made, and note any errors that may come up.

On the Feature Selection screen, I had selected everything except the last item which was Local DB, because I didn't know what it was and guessed that at that point I was choosing between Express or Local DB. Since I'd worked with SQL Express 2005 years ago, I wanted Express.

On the Instance Configuration screen, the default choice was 'Named instance' with the name MSSQLSERVER in the field after it. Below that, the Instance ID field was already filled in with MSSQLSERVER as well. Because I had planned on this being the only installation, I selected 'Default instance' instead, but did not change the Instance ID field. Perhaps I should have changed it from MSSQLSERVER and typed in SQLExpress instead, but I didn't know any better at the time. I'm wondering now if I probably should have chosen Named instance instead as well?

My concern is, because of the choices I made during installaion, did I somehow confuse Windows 7 (Professional), VS, and SQL Server? In VS should I leave the Data Source as: (LocalDB)\v11.0 ? As of yet, I've not encountered any references to .\SqlExpress (do you mean any references to it in the book as well?).

Thanks!!
-Brian

Imar · January 13th, 2017, 05:30 AM

It doesn't really matter. A named instance is mostly that: a name for an instance. So a SQL Express Installation called .\SqlExpress works the same as one called .\Something else.

My book assumes local DB so if you want to follow along you need to install it. My comment "Try using (LocalDB)\v11.0 wherever you see references to .\SqlExpress." wasn't appropriate here; it would apply to older versions of the book that use Express.

So bottom line you have two choices:

1. Install Local DB
2. Use SQL Express and use your named instance anywhere you see a reference to Local DB.

Cheers,

Imar