Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm writing Windows Service in C# and inside of it I'm unable to connect to the database. This is the question I've asked recently: SqlException: Cannot open database "path\db.mdf" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'

What I've done so far:

1) In server properties set flag "Identity verification with Sql Server and Windows" 2) Created login for sql server. Added user to the database with that login. 3) Added all available roles to this login.

Part 1: If I connect to the server using Sql Server 2012 Identity Verification and than trying to attach database that is in some folder I get:

CREATE FILE encountered operating system error 5(Access is denied.) 
while attempting to open or create the physical file 'path\db.mdf'. 
(Microsoft SQL Server, error: 5123)

But I can attach the database if it's in this folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

That's in SSMS.

Part 2: What do I get when trying to attach database from Visual Studio 2012:

The attempt to attach to the database failed with the following information:  
Login failed for user test_user

If I use this connection string in my code:

<add name="ConnectionName"
         connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;
     provider=System.Data.SqlClient;provider connection string='data source=(LocalDB)\v11.0;
     attachdbfilename=&quot;path\db.mdf&quot;;
     connect timeout=30;
     User Id=test_user;Password=1;
     MultipleActiveResultSets=True;
     App=EntityFramework'"
         providerName="System.Data.EntityClient" />

I get from my Windows Service app (written in C#) the following exception:

EntityException: The underlying provider failed on open. 

Inner excetion: SqlException: Login failed for user test_user.

I'm sure I typed the login properly. I get this error whether the database is in MSSQL\DATA folder or not.

There is something wrong with the permissions to access some folders on my computer, but I don't know why, I've given this user all the permissions!

Also, one time after computer restart the windows service started properly and was able to connect to the database, but after some time it lost this ability. And I don't know why.

Please help, I'm struggling with this issue for the last 4 days. Thanks!

share|improve this question

closed as off-topic by Paul White, RolandoMySQLDBA, Mike Walsh, bluefeet, Shawn Melton Dec 4 '13 at 10:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, Mike Walsh, bluefeet, Shawn Melton
If this question can be reworded to fit the rules in the help center, please edit the question.

    
See this question - dba.stackexchange.com/questions/53756/… does this help? The SQL Server Service account needs the permissions and you may be bumping into a UAC related issue. –  Mike Walsh Nov 26 '13 at 23:54

2 Answers 2

The error indicates that the SQL Server Service does not have permissions to the folder that the mdf file is located in.

Since SQL Server 2008 the account running SQL Server has by default a minimal permissions set. If you want it to access files in non default directories, you need to explicitly grant access to that location.

In SQL Server 2012 things are complicated even more by Service Isolation.

In 2008 you can just grant access to the folder to the account is setup to start the sql server service. You can find that account in the SQL Server Service Manager.

In SQL 2012 you need to grant access to an account that looks like this: NT SERVICE\MSSQLSERVER or like NT SERVICE\MSSQL$InstanceName.

In your case, as you are using a default instance of SQL Server 2012, granting NT SERVICE\MSSQLSERVER to have write access to the path that contains db.mdf should make things work.

share|improve this answer
    
Thanks for replying, I've given the NT SERVICE\MSSQLSERVER account the db_reader and db_writer permissions and now I don't get "error 5(Access is denied.)" in SSMS, but I'm still unable to connect to the db from Visual Studio 2012 (and my application too). I updated the question, see "Part 2". Thanks! –  Aleksei Chepovoi Nov 27 '13 at 7:07

Is that account given role of sysadmin? If yes then check on the folder permissions. There may be problem in configuration also. Check that one also

share|improve this answer
2  
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Max Vernon Jul 8 at 21:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.