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.
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.
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="path\db.mdf"; 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!