So somewhere in the backwaters of connecting an IIS hosted web app (a.k.a ASP Core 2.x SPA like app that uses SQL Server as it’s data host), we find the secret not so secret way to wire up a Windows Credential for Database access.

Write this down: 

IIS AppPool\DefaultAppPool  or Generically   IIS AppPool\<Name My Pool>

We start here in IIS with our favorite App Pool- 

Now this one is just for example… an ASP Core app pool, of course, would have a No Managed Code like this:

Either way, the SQL Windows credential set up is the same….
In SQL server the new login looks like this: Note the IIS AppPool\My Favorite App Pool

If the database already exists, you can select the default database at this time…

Now a word about SQL Roles:

Before creating the new SQL Login – one should add at least add the dbcreator role for this login. It will be important if you plan to use EF Core Migrations, and of course if you want to have the new web site automatically create and seed a new db instance as well as perform and EF Core Migration.  Don’t skip this step if you intend to do continuous deployment.

Now the last bit is the connection wire up in your Web App:

In your appsettings.json file, normally you set a default connection string… it should look something like this:

“DefaultConnection”: “Server=<Your Server SQL Instance>;Database=<My AppDatabase>;Trusted_Connection=True;MultipleActiveResultSets=true” 

Now this connection string does vary but basically– Server=   Database= and Trusted_Connection=True are the important parts… the other parameters are optional.

Now, notice no “User Id=” or “Password=” are in the string. Well, that is the point. You certainly can set up a SQL user and password for your database. But the whole point of setting the app pool windows credential in SQL was to avoid putting passwords in a appsettings file.  

One last matter- and it can drive you crazy. When you publish your web site (or app) to IIS, make sure your actual App pool is assigned correctly. I know it’s like a “DUH” moment here. But your App Pool/SQL Credential/Web Connection string need to be aligned. Or guess what? Indeed, connecting errors will be in your path… or way depending on how you look at it.

Now back to Web App’ing 

~SG