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

application connecting.

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!