SQL Server 2005 can't attach database on Windows Vista

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.
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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 DeleteThis @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.
=========================================================
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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 RemoveThis @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
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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=0...0.3054&

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

OK
------------------------------
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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.DeleteThis@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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.TakeThisOut@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
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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 DeleteThis @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
>
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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.RemoveThis@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
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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.RemoveThis@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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 RemoveThis @microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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 ***
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista 

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.
 >> Stay informed about: SQL Server 2005 can't attach database on Windows Vista