Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / SQL Server Tools / July 2008

Tip: Looking for answers? Try searching our database.

SQL Server 2005 can't attach database on Windows Vista

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Daniel Grassick - 01 Apr 2008 20:31 GMT
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
------------------------------
Erland Sommarskog - 01 Apr 2008 22:59 GMT
> 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.
Signature

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 - 02 Apr 2008 01:30 GMT
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.
Charles Wang[MSFT] - 02 Apr 2008 08:08 GMT
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.
============================================================
Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================
Daniel Grassick - 02 Apr 2008 20:27 GMT
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!
Erland Sommarskog - 02 Apr 2008 23:03 GMT
> 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
[quoted text clipped - 15 lines]
> 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?

Signature

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

Charles Wang[MSFT] - 03 Apr 2008 11:13 GMT
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.
=========================================================
Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================
Daniel Grassick - 03 Apr 2008 17:07 GMT
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
------------------------------
Erland Sommarskog - 03 Apr 2008 23:10 GMT
> 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).


Signature

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 - 04 Apr 2008 17:21 GMT
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

> > I didn't turn off the User Account Control as I didn't know how to do this
>
[quoted text clipped - 10 lines]
> the service and look at the Log On tab (which is the first).
>  
Erland Sommarskog - 04 Apr 2008 23:23 GMT
> SQL Server (MSSQLSERVER) uses account ".\Daniel"
> SQL Server (SQLEXPRESS) used account "NT AUTHORITY\NetworkService"
[quoted text clipped - 7 lines]
> 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.

Signature

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 - 05 Apr 2008 15:34 GMT
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
Charles Wang[MSFT] - 07 Apr 2008 12:58 GMT
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.
=========================================================
Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================
Daniel Grassick - 08 Apr 2008 16:43 GMT
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

> Hi Daniel,
> Windows Vista expands its use of the principle of least privilege by
[quoted text clipped - 31 lines]
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
Isaiah Okorie - 09 Apr 2008 11:21 GMT
Hi guys I felt I should drop a line on this issue since I just finished fighting it with some seed from the replies Erland and Charles gave.

I had created an SMO app for attaching my database. That failed on running it on Vista Home Premium Edition, but works fine on XP.

Then I peeped into the Event viewer on Vista and got confirmation it was a permission issue.

I then wrote a little bat file with the following:

osql -S "(local)\sqlinstance" -E -i "\update\isql.txt" -o "\update\osql.txt"

given that "isql.txt" exists in the executing
folder.

isql.txt contains:

EXEC sp_attach_db @dbname = N'MyDatabase',
@filename1 = N'C:\Data\db.mdf',
@filename2 = N'C:\Data\db_log.ldf' ;
Go

After running this bat, I got the error:

"create database permission denied in database master"

I then tried turning off User Account Control in Vista by going to Control Panel->User Accounts->Turn User Account Control on or off.

This means that Vista would allow my account(an admininstrator) to behave like an administrator, at least as far as common file operations are concerned!

Then Vista requires that you restart the machine.

After that I could now attach my database.

I think that the moral of the story is that Vista failed to allow SQL Express 2005 to attach my database. I didn't need to install SQL Express 2005 SP2.

There may well be a security risk here but Vista should not affect SQL operation in the way it did.
Erland Sommarskog - 09 Apr 2008 23:03 GMT
> After running this bat, I got the error:
>
[quoted text clipped - 17 lines]
> There may well be a security risk here but Vista should not affect SQL
> operation in the way it did.

UAC has created some confusion, and many has opted to turn it off. That is
a pity.

Way back then when I had admin privileges on Unix, I was never logged
in as root. Or some other account with admin rights on. When I needed
to do something which required privileges, I ran something called "sudo"
which took a command as argument, and ran that command as root. After
I had supplied the root password.

Always running as in Admin has been common on Windows, but it is not
really good as it increases the possibilities for malware to work.

There are several ways to address the issue you ran into. Disable UAC
is probably the worst. The best is probably to give the service account
rights where it needs to. But else you can right-click the shortcut
for the command-line window and select "Run as administrator".

Signature

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

LoRez - 05 Jun 2008 20:56 GMT
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!?
Charles Wang [MSFT] - 09 Jun 2008 10:48 GMT
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.
=========================================================
Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

=========================================================
Phadermchai Kraisorakul - 09 Apr 2008 01:13 GMT
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
Daniel Grassick - 09 Apr 2008 14:36 GMT
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.

> With Microsoft SQL Server Management Studio
>
[quoted text clipped - 7 lines]
>
> *** Sent via Developersdex http://www.developersdex.com ***
S J - 15 Jul 2008 19:45 GMT
Check the owner of mdf file you are trying to attach.  If you have
changed computers, then the new owner may not have permissions.  Refer
to this link to change ownership.

http://www.lockergnome.com/windows/2006/11/22/take-ownership-of-a-folder
-in-xp/
ams.gavrila@gmail.com - 30 Apr 2008 23:55 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.