Integrated Security is quite nice in the sense that you don’t need to configure login names in config files for various servers. And there are some other benefits in terms of manageability, etc: http://serverfault.com/questions/13292/is-using-integrated-security-sspi-for-accessing-sql-server-better-for-web-appl . And while it will take a bit of administration to configure proper impersonation, etc. to get Integrated Security working properly, for simple cases (or even test boxes) where you can have IIS and the database on the same box, it’s quite easy to set up. Here’s how:

If you create a new IIS Application, it creates a new App Pool with a corresponding App Pool user (or you use an existing one). You can configure the App Pool user to be a domain account (which you’ll need to enable remote connections), but by default, it’s an IIS App Pool user. If you run under Integrated Security, it’s this account that your application runs under. If you require database access via Integrated Security, then this user needs permissions on the database server. If you’re using something like EF that tries to create / modify databases as part of initialisation (possibly through mogrations), then the App Pool user will need the required permissions. To create the database, for example, it would need dbcreator permissions. It’s quite annoying to go through and create the permissions through the UI of management studio. Running the following script will give you the desired results:

USE [master]
GO

CREATE LOGIN [IIS APPPOOL\appPoolName] FROM WINDOWS;

GO

CREATE USER UserNameYouWant FOR LOGIN [IIS APPPOOL\appPoolName];  -- not entirely necessary

GO

ALTER SERVER ROLE [dbcreator] ADD MEMBER [IIS APPPOOL\appPollName];
GO

Of course, you’ll need to run the script under a user account that has permissions to do so, but from then on in, the app pool user will be able to create required databases, and will be the owner of the databases it creates. As such, those EF (or similar) bits will work as you want, and your website will be able to access the databases as required.

Basic stuff, I know. But it’s caused me annoyance enough times in the past to warrant a write up.

Note: The connection string can look like “Data Source=SERVERNAME; Initial Catalog=DbName; Integrated Security=SSPI”