I am running Windows Vista on a new Dell computer with both the Developer 
and Expression versions of SQL Server 2005 installed and I am experiencing 
the following problem:

1) Used modified downloaded "instnwnd.sql" script to create Northwind 
database in directory "C:\DevNet2008\Data" for SQL Server 2005 Express

2) Detached database in Microsoft SQL Server Management Studio by right 
clicking "Nothwind" and selecting "Tasks > Detach ..."

3) Immeditately tried to re-attach by right clicking databases and selecting 
"Attach ..." and adding the database file in the dialog but get the following 
error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

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

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

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

------------------------------

CREATE FILE encountered operating system error 5(error not found) while 
attempting to open or create the physical file 
'C:\DevNet2008\Data\Northwnd.mdf'. (Microsoft SQL Server, Error: 5123)

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

------------------------------
BUTTONS:

OK
------------------------------

Daniel Grassick (dmgrass@community.nospam) writes:
> 3) Immeditately tried to re-attach by right clicking databases and
> selecting "Attach ..." and adding the database file in the dialog but
> get the following error: 

Did you try to attach it to the same instance or the other instance?
 
> CREATE FILE encountered operating system error 5(error not found) while 
> attempting to open or create the physical file 

NET HELPMSG 5 will tell you that this is "Access is denied".

Thus, is is either a permissions problem - or you did not detach the
database from the original instance as you thought.
-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for your response. It doesn't make sense to me but ...

In Management Studio I only connected to the SQL Express instance and did 
the create, detach and attempted re-attach through the UI in object explorer.

Hi, 
Did you install the latest service pack SP2 for your SQL Server 2005 
Express instance?
I performed a test at my side with SQL Server 2005 Developer Edition SP2 
installed on Windows Vista Enterprise SP1, however I could not reproduce 
your issue. Everything worked fine. 

Now I recommend that you first run "SELECT @@VERSION" in SSMS to check if 
your SQL Server 2005 instance version number is equal to or over 9.0.3042. 
If not, please install SQL Server 2005 SP2 first. You can download SQL 
Server 2005 SP2 from the following links:
Microsoft SQL Server 2005 Express Edition Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyID=31711d5d-725c-4afa-
9d65-e4465cdff1e7&displaylang=en

Microsoft SQL Server 2005 Service Pack 2
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&displaylang=en

You may also try using T-SQL statements to see if it helps. You can attach 
your database with the following T-SQL statement:
EXEC sp_attach_db @dbname = N'Northwind', 
    @filename1 = N'C:\DevNet2008\Data\northwnd.mdf', 
    @filename2 = N'C:\DevNet2008\Data\northwnd.ldf' ;
Go

Hope this helps. If you have any other questions or concerns, please feel 
free to let me know. 

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to 
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for 
non-urgent issues where an initial response from the community 
or a Microsoft Support Engineer within 1 business day is acceptable. 
Please note that each follow up response may take approximately 
2 business days as the support professional working with you may 
need further investigation to reach the most efficient resolution. 
The offering is not appropriate for situations 
that require urgent, real-time or phone-based interactions or complex 
project analysis and dump analysis issues. Issues of this nature are best 
handled working with a dedicated Microsoft Support Engineer by 
contacting Microsoft Customer Support Services (CSS) at 
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Thanks for the suggestions but no luck. Both the installed Developer and 
Express editions have version numbers 9.00.3054.00 and I installed SP1 for my 
Windows Vista Ultimate OS. 

It seems it must be a permissions problem. My login "Daniel" is set as an 
administrator but when I try to manually copy either the ".mdf" or ".ldf" 
files I must acknowledge the prompt "You'll need to provide administrator 
permission to copy to this file" for the destination folder and I then get 
the User Account Control dialog with the prompt "Windows needs your 
permission to continue".

When I run the T-SQL statement you suggested I get the error message:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\DevNet2008\Data\northwnd.mdf". 
Operating system error 5: "5(error not found)".

It's not good news that you can't reproduce the problem but I'm still hoping 
for some inspired idea as to what could be going on!

Daniel Grassick (dmgrass@community.nospam) writes:
> Thanks for the suggestions but no luck. Both the installed Developer and
> Express editions have version numbers 9.00.3054.00 and I installed SP1
> for my Windows Vista Ultimate OS. 
> 
> It seems it must be a permissions problem. My login "Daniel" is set as an 
> administrator but when I try to manually copy either the ".mdf" or ".ldf" 
> files I must acknowledge the prompt "You'll need to provide administrator 
> permission to copy to this file" for the destination folder and I then get 
> the User Account Control dialog with the prompt "Windows needs your 
> permission to continue".
> 
> When I run the T-SQL statement you suggested I get the error message:
> 
> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file "C:\DevNet2008\Data\northwnd.mdf". 
> Operating system error 5: "5(error not found)".
> 
> It's not good news that you can't reproduce the problem but I'm still
> hoping for some inspired idea as to what could be going on! 
 
What is the service account for SQL Server? What rights does this 
service account have in the directory you use?



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi,
In addition to Erland's suggestion, I also would like to ask you some other 
questions:
1. What is the result if you turn off User Account Control?
2. Are there any anti-virus software installed on your OS? If so, what is 
the result if you turn if off?
3. What is the result if you perform a test with a different database with 
different data file locations?

Look forward to your response.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Thanks for all the suggestions. I've replied to an earlier post to avoid 
nesting too deep. Securtiy is something I don't know a lot about but the SQL 
Server Express permissions appear to have a lot of problems as explained 
later. I did turn off the basic security in Norton Internet Security but I 
didn't turn off the User Account Control as I didn't know how to do this and 
I was focused on the permissions issues. The steps I took are as follows:

1) Checked the "Local Users and Groups" in "Comptuer Management".

Only the following users are defiined:

Administrator
Daniel
Guest
IUSR_DMGMAIN2008

There are a bunch of groups including 8 for SQL Server such as:

SQLServer2005MSSQLUser$DMGMAIN2008$MSSQLSERVER
SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS


2) Checked the directory permissions for the original "C:\DevNet2008\Data" 
directory.

Groups or users listed under security are:

Authenticated Users
SYSTEM
Administrators (DmgMain2008\Daniel\Administrators)
Users (DmgMain2008\Daniel\Users)

The "Admistrators" and "SYSTEM" have all permissions except "Special" and 
"Authenticated Users" lacked "Full control" as well.


3) Created another "Northwind" database in the default server directory.

Created "Northwind" DB in the following directory using the unmodified 
"instnwnd.sql" script :

c:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA

This directory included full control permissions for 
"SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS" but got the same error as did 
for the original "C:\DevNet2008\Data" directory. The "northwnd.mdf" file 
permissions included this same setting.


4) Checked permissions for some databases.

I deleted the "Northwind" files and recreated the database as above. The 
permissions under "Database Properties" had no "Users or roles" listed and 
just a link to "server permissions" which I explored in the next step.

The permissions for the "model" database was the same as for "Northwind" but 
the permissions for "master" included  "##MS_AgentSigningCertificate##" and 
"guest" under "Users or roles" but clicking on the "Effective Permissions" 
button resulted in a similar "can't access" error to the one listed under the 
next step but with error number 15517.


5) Right clicked on instance "DMGMAIN2008\SQLEXPRESS (SQL Server 9.0.3054 - 
DmgMain2008\Daniel)" in SSMS and selected "Properties" then "Permissions" in 
the "Server Properties" window. 

There are a whole bunch of "logins or roles" including four with "##" at the 
start of the name such as:

##MS_SQLAuthenticatorCertificate##

Tthe other logins or roles are:

BUILTIN\Administrators
BUILTIN\Users
DmgMain2008\Daniel
DMGMAIN2008\SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS
NT AUTHORITY\SYSTEM

The list of permissions for all these latter roles contain 25 permissions 
for the grantor "DmgMain2008\Daniel" with nothing checked and a duplicate 
entry for "Connect SQL" for grantor "sa" with "Grant" checked. Clicking the 
"Effective Permissions" buttons shows all 25 permissions for the following 
logins or roles:

DmgMain2008\Daniel
NT AUTHORITY\SYSTEM

Clicking this button for all other logins or roles including the four with 
"##" at the start of the name results in the following error:


TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

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

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

------------------------------

Cannot execute as the server principal because the principal 
"DMGMAIN2008\SQLServer2005MSSQLUser$DMGMAIN2008$SQLEXPRESS" does not exist, 
this type of principal cannot be impersonated, or you do not have permission. 
(Microsoft SQL Server, Error: 15406)

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

------------------------------
BUTTONS:

OK
------------------------------

Daniel Grassick (dmgrass@community.nospam) writes:
> I didn't turn off the User Account Control as I didn't know how to do this 

Sometimes ignorance is a good thing! Keep in on, even if you ever learn 
how to turn it off.

> 1) Checked the "Local Users and Groups" in "Comptuer Management".
>... 
> 2) Checked the directory permissions for the original "C:\DevNet2008\Data" 
> directory.

One thing is missing from your list: what is the service account for
SQL Express? You find this in SQL Configuration Manager, double-click
the service and look at the Log On tab (which is the first).
 



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks Erland! I checked the "Log On" service accounts and found:

SQL Server (MSSQLSERVER) uses account ".\Daniel"
SQL Server (SQLEXPRESS) used account "NT AUTHORITY\NetworkService"

These settings work fine on my Windows XP machines but I changed the account 
for SQL Express to ".\Daniel" on the Vista machine and now the DB attach 
works!

I don't understand how "NT AUTHORITY\NetworkService" can work on the XP 
machines since I don't see any user or group with that name nor any login or 
role under server permissions in SSMS. Should I have done something 
different? I assume I need to change the account for the following services 
which currently use "NetworkService":

SQL Server Integration Services
SQL Server Browser

Thanks so much!
Daniel

"Erland Sommarskog" wrote:

> Daniel Grassick (dmgrass@community.nospam) writes:
> > I didn't turn off the User Account Control as I didn't know how to do this 
> 
> Sometimes ignorance is a good thing! Keep in on, even if you ever learn 
> how to turn it off.
> 
> > 1) Checked the "Local Users and Groups" in "Comptuer Management".
> >... 
> > 2) Checked the directory permissions for the original "C:\DevNet2008\Data" 
> > directory.
> 
> One thing is missing from your list: what is the service account for
> SQL Express? You find this in SQL Configuration Manager, double-click
> the service and look at the Log On tab (which is the first).
>  
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Daniel Grassick (dmgrass@community.nospam) writes:
> SQL Server (MSSQLSERVER) uses account ".\Daniel"
> SQL Server (SQLEXPRESS) used account "NT AUTHORITY\NetworkService"
> 
> These settings work fine on my Windows XP machines but I changed the
> account for SQL Express to ".\Daniel" on the Vista machine and now the
> DB attach works! 
> 
> I don't understand how "NT AUTHORITY\NetworkService" can work on the XP 
> machines since I don't see any user or group with that name nor any
> login or role under server permissions in SSMS. Should I have done
> something different? 

NetworkService is something built in.

I guess it works on XP, but not on Vista because Vista is stricter
on security and permissions.

The recommendation is to run SQL Server from a domain account, but 
not one that has admin rights. I usually create a separate account
to run SQL Server from. The problem if you do this now, you would
have to grant that account the Windows privileges you need, or at 
least I think so. When you specify an account at setup, Setup does
that for you.

The problem with using your own account is that you may change your
password some day (well, at least you should), and in this case, you
need to change the password for the service as well, or SQL Server won't
start.
 
> I assume I need to change the account for the
> following services which currently use "NetworkService": 
>
> SQL Server Integration Services
> SQL Server Browser
 
Integration Services, yes. But I think NetworkService (or is that
LocalService) is preferred for the Browser service.) The Browser is
the one that tells a connecting client that instance ABC is on port
4711.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks again! I think I'll stick with my main account with admin rights for 
the short term because I don't think I know enough to troubleshoot problems. 
I would greatly appreciate any references that would help me learn about all 
the SQL Server security issues particularly as they relate to Windows 
accounts and security. It's OK if you don't know of anything offhand because 
I am very thankful for the time and help you have given me already!

Daniel

Hi Daniel,
Windows Vista expands its use of the principle of least privilege by 
further reducing the privileges and access to the files, registry keys, and 
firewall ports it assigns to most services. That is why the NETWORK SERVICE 
account could not write to the folder by default. I think that you can also 
work around this issue by explicitly assigning write permission to the 
NETWORK Service account on the folder. Generally since the Network Service 
account is the least privilege windows built-in account, it is not 
recommended for SQL Server and SQL Server Agent services. Local User or 
Domain User accounts are more appropriate for these services. 

You may refer to the following articles:
Service Account
http://technet.microsoft.com/en-us/library/ms143691.aspx 
Setting Up Windows Service Accounts
http://technet.microsoft.com/en-us/library/ms143504.aspx 
Selecting an Account for the SQL Server Agent Service
http://technet.microsoft.com/en-us/library/ms191543.aspx 
Service Accounts (Clusters)
http://technet.microsoft.com/en-us/library/ms143763.aspx 

Hope this helps.


Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Thanks for your helpful reply and the great links. I've only glanced at the 
information and it looks as if I have my work cut out for me if I really want 
to understand the details. Thanks again!

Daniel

"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> Windows Vista expands its use of the principle of least privilege by 
> further reducing the privileges and access to the files, registry keys, and 
> firewall ports it assigns to most services. That is why the NETWORK SERVICE 
> account could not write to the folder by default. I think that you can also 
> work around this issue by explicitly assigning write permission to the 
> NETWORK Service account on the folder. Generally since the Network Service 
> account is the least privilege windows built-in account, it is not 
> recommended for SQL Server and SQL Server Agent services. Local User or 
> Domain User accounts are more appropriate for these services. 
> 
> You may refer to the following articles:
> Service Account
> http://technet.microsoft.com/en-us/library/ms143691.aspx 
> Setting Up Windows Service Accounts
> http://technet.microsoft.com/en-us/library/ms143504.aspx 
> Selecting an Account for the SQL Server Agent Service
> http://technet.microsoft.com/en-us/library/ms191543.aspx 
> Service Accounts (Clusters)
> http://technet.microsoft.com/en-us/library/ms143763.aspx 
> 
> Hope this helps.
> 
> 
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================
> Delighting our customers is our #1 priority. We welcome your 
> comments and suggestions about how we can improve the 
> support we provide to you. Please feel free to let my manager 
> know what you think of the level of service provided. You can 
> send feedback directly to my manager at: msdnmg@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
> 
> 
>

Thanks for your reply and that of Isaiah. It is nice to know I wasn't the 
only one struggling with the issue and it is interesting to see other 
options. My personal belief is that working with the Service Account as 
suggested by Erland and Charles is the best solution. I would be great if 
Microsoft could release specific recommendations perhaps in their knowledge 
base to address this issue.

"Phadermchai Kraisorakul" wrote:

> With Microsoft SQL Server Management Studio
> 
> You must use "Right Click" on the menu and use "Run as Administrator" to
> open the program
> 
> 
> I use this to open and then I can attached my old DB from SQL2005
> Express Edition
> 
> Now I'm using SQL2005 Dev. Edition on new computer
> 
> *** Sent via Developersdex http://www.developersdex.com ***
>

Hi Daniel,

I've stumbled upon the same problem today.

I am glad to see you solved your problem.

I did too ... and I had to disable UAC and change all permissions on
the folder to FULL CONTROL .. even to All Users Group. And it worked.

But that's not the best solution .. isn't it ?? :)

So I figured out a better one .. Running the Management Console under
Administrator Privileges.

Right click on Management Console and choose Run as Administrator ...
and works like a charm !

Funny .. isn't it ??

Andrei

Something interesting I observed: 
Detaching a database from SQL 2005 results in security permissions for the 
file being wiped out for all users except the one I used to detach the file 
(in my case a windows sys admin user).  This includes the local system 
account!  When I added back SYSTEM, I could attach no problem.  Why would 
detaching wipe out security permissions for the file!?

Hi LoRez,
Could you please have a new post at the community so that we can better 
track it?

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your 
comments and suggestions about how we can improve the 
support we provide to you. Please feel free to let my manager 
know what you think of the level of service provided. You can 
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================