Windows authentication is bad.
Almost everything you read tells you that Windows authentication is the way
to go. There's even an option to use Win authentication exclusively. Why? Here
are the main reasons:
- Account lockout after x incorrect attempts
- Password expiration, password length, ability to prevent users from using
same password when old one expires, requiring a "strong" password
- Access can managed by groups - so you can use the Win tools to manage
membership, you can also delegate management of access to a network admin
(or in Win2K, whoever is managing the organizational unit)
- Single user login
I don't disagree with any of those features, they are worth considering. Too
bad we don't have them for SQL logins! But let's look at some practical reasons
why Windows authentication is often the wrong choice.
What? Wrong choice?
The first reason is that for now, to use Windows authentication you have to
be a member of the domain. Not workable for internet applications is it? While
it's possible to build web apps that skirt the issue, most avoid it entirely by
using a sql login. As soon as you need ONE sql login, that option that lets you
use Windows authentication (WA from now on) is not an option!
Now let's say we are just running on a intranet, everyone IS a domain member.
Why not use it? Well, does your application use stored procedures and views
exclusively, no direct table access? Even if it does, what would happen if
someone accessed a stored procedure or view outside the context of the
application, maybe to run sp_Employee_SetSalary? Can't happen? How many of your
internal machines have Access installed? Excel with support for ODBC data
sources? Microsoft Query? Query Analyzer? None of those? How about ADO 2.x?
Anything stopping the user from running some VBScript to open a connection and
do some browsing, maybe run a few stored procedures?
The problem is that you've authorized the user to see the data from those
views, to execute those stored procedures. You "could" try to restrict
it further by checking app_name() in a trigger and only allowing certain
applications. This is not bad and actually gives you a fair measure of
protection. Assuming the app name you use in the trigger is not the real name of
the app! This would help on protecting the data against changes, but so far we
don't have a select trigger - if you're using views (or a table directly), how
to further safe guard that? One further point is that the app name gets set in
the connection string, there is no way to verify that it's actually the real
Overkill? Paranoia? Maybe. Maybe not!
Assuming you've stuck with me this far, hopefully you're thinking that SQL
logins aren't any better. After all, you can't track which user is really logged
in (though you can tell the workstation via host_name), you can't enforce
password expiration (or can you?), require strong passwords, etc. Plus you have
to have everyone using the same login/password, or incur the overhead of
adding/removing users as they come and go - if you have a lot of turnover in
staff this can be a pain! If they have the login/password they can still use any
of the methods I listed above to bypass the application and do whatever they can
figure out how to do. So while we could solve the expiration, length, and
strength of password problem (really, we could if we tried!), it doesn't really
solve the problem, does it?
What we need to accomplish is to make the user go through a "gate
keeper", either our application or a COM/COM+ object, that absolutely
prevents the user from accessing the database from any other tool. We can do
that pretty easily by embedding the sql login/password in the gate keeper.
What? Holy insecurity, Batman!
How do we change passwords if we do that? What's to stop a user from browsing
the executable using Notepad looking for the password? How do we know who is
really using the application?
Let's look at those, starting with how do we change passwords. How often does
it need to be changed, really? Only your developers know it, so you need to
change when one leaves (maybe). Options? Recompile the application with the new
password and redeploy. Store the password externally, perhaps in a registry key.
Of the two I much like the first one. If you don't have a way to easily deploy
changes, you should get one! I'll discuss this further in an upcoming article.
The second one isn't bad, you can just push a new registry value out in a login
script (or however you want to do it).
Which really leads us to the second question, what's to stop a user from
browsing the executable, the registry, ini file, UDL, etc, and finding the
login/password. The answer is to either obscure or encrypt both values. Even the
simplest obscuration is usually sufficient, you unravel it in the application
and build the connection string.
How do we know who is really using the application? Windows authentication.
We know who the logged in user is. We can log this manually using a simple
stored procedure right after we connect. Extra work? Just a little, but the
information can be quite useful, sometimes in unexpected ways. One example I use
myself is that I have a lot of databases that are on non-continuous
replication - by checking the login history I know to not worry about those
that no one has accessed.
What about application roles? They rely on a passed login and password, so
they really require the same effort that a sql login does. I've also heard...but
not seen myself...that some controls open a second connection for background
work..and that connection fails because sp_setapprole hasn't been executed.
Really app roles exist to totally override a set of permissions that the user
would normally have - usually from WA!
Objections? Come on, I know they are coming! Post your comments!
In a follow up article I'll talk more about quickly redeploying internal
applications, the "key master", obscuring passwords, securing an
application, when WA really does make sense, AND try to respond to whatever
objections you can come up with!
Book Drawing! Rate this article and post a comment
about it (good OR bad) before midnight on July 22, 2002, and you'll
automatically be entered for a chance to win a copy of Microsoft
SQL Server 2000 Performance Tuning. Our thanks to Microsoft Press for
providing us with a review copy!