Problem attaching to a sql server express db file using network service account

Question

I am using VS2010 Beta 2 to create an IIS hosted WCF service. I added a SQL Server database file to use as a simple data store and then generated some Linq to SQL classes to interact with it.

The problem I have happens when Linq to SQL tries to connect to the database. I get the following error below:

CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file c:\Source\SampleApp\App_Data\GameData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

To get around a previous problem I have already changed the web.config to not use an user instance:

<add name="GameDataConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\GameData.mdf;Integrated Security=True;User Instance=False"
  providerName="System.Data.SqlClient" />

My first reaction is that the application pool's user account (NETWORK SERVICE) is unable to open the file so I have granted full control to the database file and log file but this did not help.

Interestingly if I change the application pool to run under local system everything works correctly. Is there something else I have to grant NETWORK SERVICE access to?

Answer 1

Yopu must use Sql Management Studio Express and add the Network Service Account as a login. Then assign it DBO permissions on the specified Database or simply give it the sysadmin role. Note when you install SQL Server 2005 any edition you have the initial option to set the login account for the services. If you specifiy Network Service. by default the network service account will be granted sysadmin role.

Hope this helps

Answer 2

If you isn't using <identity impersonate="true"/>, your application pool user doesn't have enough access to your database.

You can also to create a database user (SQL authentication) and to fix it's credential (username/password) into your connection string. That's what I usually do.