SQL Server Forum / General / SQL Server Tools / July 2008
SQL Server 2005 can't attach database on Windows Vista
|
|
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
|
|
|