ctp 3 and rc0: Can't access mdf file: locked out of folder because of permissions setting?

Question

Big problem: I can't attach my database under ctp3 denali.  SSMS (in the attach dialog box) doesn't allow me any access to subdirectories under "MyUserName" folder under "Users".   (Perhaps I made a mistake in accepting the defaults for service accounts and passwords during install, but it did not have a button to set them all to the same one, i.e. my user account.)

How do I correct so I can attach my mdf?  Thank you.  Below is the error message.

TITLE: Locate Database Files - MyPCName
------------------------------

C:\Users\MyUserName\MyFolder\sql
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. 

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

 

Answer 1

(This is all under windows 7 sp1 x64  ultimate.)
I found a temporary workaround, but I'm fairly sure this is NOT what I'm supposed to do:

I ran Sql Server Configuration Manager, selected Sql Server Services in the left pane, and then right-clicked on Sql Server on the right pane, and selected properties.  In the logon tab, I deleted the default (I assume):
NT Service\MSSQLSERVER
and put in my user account:
.\[mywindowsusername]

Then, ONLY if I run SSMS elevated as administrator (right-click, run as admin), do I have access to my database underneath the Users folder in my home drive. 

Perhaps someone knows what the proper solution is.  (I'm assuming there must have been some change in Denali regarding service accounts?)

 

Answer 2

Unfortunately, with this workaround, I can confirm that ANY app that connects to sql server denali requires elevation (run as admin).  For example, MS Access 2010 will not open up sql server tables unless it runs in elevated mode.  Also, visual studio 2010 gives the following error when trying to access sql server denali (unless it is run in elevated mode):

TITLE: Connect to Server
------------------------------

Cannot connect to (local).

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'pcName\username'. (Microsoft SQL Server, Error: 18456)

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

 

Answer 3

EDIT: Added in Connect, that default installation should not block access to local user databases on standalone or workgroup (non-domain) installations:
https://connect.microsoft.com/SQLServer/feedback/details/680278/18456-error-ctp3-default-blocks-access-in-local-desktop-installation

 

Looks like I found a solution: windows 7, when running denali and with UAC on, now requires that one add the sysadmin fixed server role to each administrative user (after switching the logon user from the virtual account to the windows user name in Sql Server Configuration Manager). 

http://msdn.microsoft.com/en-us/library/cc281849(SQL.110).aspx

Once done, things work normally, without the need for elevation.

How to add "sysadmin" is here:
http://msdn.microsoft.com/en-us/library/aa905868(SQL.90).aspx

 

Answer 4

Also, in rc0 for some reason I was blocked again (access error on attaching user databases) until I went to the user database folder and manually added user permissions to the database mdf and ldf files (and for good measure I did the same for the whole user database folder and also the temp folder)--it looks like installation of rc0 reset the permissions on me.