Wouldn't it be nice if you could just create a security model that backed onto your existing Active Directory infrastructure? At least that way security administration would be the responsibility of the network admins, and us dev's could all go home at night and switch off the support phone. Well sure, not only is this achievable through technologies like .NET and Kerberos, it's also been done many times before. Unfortunately it's not done as often as you'd think.
A lot of small-mid sized applications work off their own secluded databases, which contain logins for its users. They're generally easy to spot as the user is forced to remember their password for each system. A lot of companies strive for single-sign ons, which essentially give them one username/password to access all the systems they're authorized to use. But oftentimes this ideal falls short of the mark. It's a shame too, because if your network's set up correctly anyway, implementing a single-signon is really just a small step forward.
You've probably heard of Kerberos. Furthermore you've probably heard of NTLM (NT Lan Manager) - and maybe how easy it is to crack. To illustrate the differences, consider this diagram:
Pretty common scenario - your computer connects to the web server, which then gives you a page populated with data fed from the backend SQL server. In a nutshell, NTLM and Keberos will work in the following way:
NTLM: Only the web server will know you're you. When you hit the SQL Server, it will see the connection context as whatever your IIS application is set up to run as (ie IISUSR, a network service account etc).
Keberos: Using Keberos is much nicer. When you hit the web server, it switches context to execute as your account. When it hits the SQL Server, it's as if you're executing a query as if you were connected to it directly.
There are two main considerations you need to keep in mind. The first is that although NTLM can't pass authorization info down past the first hop, it's very light-weight so it'll perform better. Also, if you have an outward facing web server, chances are that your visitors aren't members of your domain, hence you'll need your own security model for public users.
So how can you tell what you're hitting your database as? Connecting to your SQL Server, run the following query:
select suser_name(), auth_scheme from sys.dm_exec_connections where session_id = @@spid
That'll give you the domain account and authentication schema you're running the query as depending on how you connected to your server. If you used windows auth, you'll get your domain\username and either NTLM/Keberos back.
The next thing we want to know is how the SQL Server sees us if we connect via the Web Server. Set up a new web app, and on the page load get it to execute the same Sql script on the Sql Server. Again, the results you get depends on how you set up your connection string.
To set up your web app to pass your credentials down the line, you need to open up IIS and set the authentication to windows auth:
Then it's a case of setting up your Web.Config for impersonation:
<system.web>
<identity impersonate="true"/>
</system.web>
Ensuring that your original connection string to your SQL Server was set up as "Integrated Security=SSPI", try running your web app again. If everything's gone smoothly then you should see your domain login and "Keberos" becing returned.
But what if it's not? If you want to see the right data coming back correctly, you can always go back to IIS authentication and enable "Basic Authentication" instead. This passes your plain text username/password to the web server so it can authenticate calls and send them down the line. It's not recommended that you do this though, even if you enable SSL. The best way is always the "do it properly" way.
There have been entire books written on the subject of Keberos alone. For your network to be able to support this authentication schema, one of the easiest ways to get it working is along these lines:
- Run TCP
- Your Services (eg SQL Server, IIS) running as local system accounts
- (the above two enable automatic registration of the Service Principal Names (SPNs))
- In your active directory settings, ensure that your servers are "Trusted for delegation". This enables them to pass your authentication info down the chain
Sure, it's stepping outside the realm of programming, but it's important to be able understand the ideas of this authentication schema. Amongst the benefits, you can do away with managing user passwords, as the underlying active directory will take care of all of this for you. Furthermore you don't need to worry so much about account maintenace - as people enter/leave the organisation, the network admins will manage the creation/deletion of accounts, and hence access to your systems.
The other thing is, if you hadn't notice, is that as the user is hitting the database as themselves, you can then use the in-built Sql Server security permissions etc to control access to databases, data, and work your own permissions from that instead of creating your own mappings.
Very handy stuff, but perhaps the greatest advantage in the users mind is - you only need one username/password!