Getting IIS to authenticate to your DB without a password

Before I start, yes, I know this is not a good security practice. I will explain why, at the end. This is meant for Dev & Test servers, because those usually don’t require strict security. Dev environments are usually a fine place to apply the kiss principal.

Mostly, this blog-post is for future-me. I’ve probably done stuff exactly like this, dozens of times, and described it to dozens of people, but I don’t have good notes on it anywhere. I seem to forget the process each time, and I have to re-discover it. Next time I can look it up here instead. If you also find it useful, that’s great too.

How it works

A SQL server can treat an IIS server (or app within IIS) as if it is just one single domain user. That way your DBA doesn’t have to make a pesky (SQL Server native) bot account (login/pw) for your program’s connection string, which will probably end-up (in plain-text) in your config file. That always feels gross.

I know what you’re thinking; instead of a bot account, or doing this stuff, we could use Kerberos to allow IIS to pass-through domain credentials to SQL server (also known as a “double hop”), but that doesn’t work as nicely/seamlessly with full-stack (js) apps. Machine authentication is pretty easy and still makes sense in some scenarios, like for full-stack stuff, imo. (using SAML with your API service, etc)

IIS config

Objective: Allow IIS to authenticate/connect directly to SQL server, as IIS’s machine account (ie, the web server’s domain name)

1. IIS Application Pool identity – This is the magic sauce which is easy to overlook (or difficult to see).
   Set up a new IIS app pool for your web app.
   Change “Advanced Settings”, set the Application Pool Identity to use the “built-in account” called “NetworkService”
   
   
   …and set your IIS web app to use this new Application pool
   
   
2. Anonymous – It might seem better to do pass-through authentication, which makes each user log-in with their own domain credentials, and then pass those through to SQL server. However, that is called a “double-hop” scenario. It is a hassle, and doesn’t really work well for “full stack” (js) apps. For this example, we will keep it simple and just do anonymous (my full-stack app will handle 3PA w SAML)
   

SQL server config

Objective: Create a SQL account for your IIS server (treating the server like it is a person or something), and grant permission(s) to database(s)

1. SQL Server, “Security” (for the whole server), create a new login for your web server.
   In this example, I have a web server called “WebServerDev01” on MyDomain. So the domain account for my server will be [MyDomain\WebServerDev01$].
   The $ suffix means this domain account is a server and not an actual person. AD seems to add that or require it. This is the secret handshake that gets SQL server to recognize your entire server as if it was one account.
   
   Also in SQL Server, Security (for the whole DB server), grant permission (for our new server account) to access any databases which will be used by our web server. Ideally, this will be the db_reader and db_writer roles, but if you are using Stored Procedures, this could get pretty involved.
   [ picture below ]
   
2. DB, Security, Users – grant permissions (roles, tables, SPs)

Testing

Ideally, now you should be able to just open your app and * Ta-da! * it magically works. However, you might need to adjust your configs (connectionString), or other settings, permissions (grant exec for your SPs) etc.

So, why don’t you want this in prod?

IKR. This was pretty simple and since no clear-text passwords are sitting unencrypted in your web.config, this seems pretty secure. Right? Well, not exactly. Let me talk about a few caveats that we just sorta opened up, and why/when that might be a bad thing.

• The whole web server at once – A web server can hold a lot of stuff. A sneaky person (hacker, developer, etc. who has gained access to your web server’s file system) in theory, could just simply put any sort of app anywhere on your web server. All apps on your web server would have permission to get to your DB and it would be difficult to distinguish between any of the apps on that server.
  Yeah, I know; if somebody is already on your server, don’t they pretty much have anything they need to get into your DB anyway? Well, maybe. However, I’ve seen a Sr developer/admin make a simple/honest mistake during a deployment. It wasn’t malicious, but because of the machine-account, it was still frustrating and elusive.
• Least privilege – On the SQL server side, if you are using an ORM or EF (or something) and not using SPs, it will be easy to manage, by granting read/write. If you are using Stored Procedures, managing (granting) permissions to all of those fellers (SPs) will drive you nuts. You might be tempted to grant “db_owner”, y’know, just to simplify things. but that really means any app could also alter tables, create SPs, drop stuff, or fully rampage on your DB. Keeping things nice & safe will take away all of the fun.

TLDR: Easy stuff is typically not very secure. This fits that rule. I don’t sweat my dev server(s) very much, usually. However, in prod, I will put-in more effort for security stuff, and so should you. 🙂
Stay leet!