In June I wrote The

Case for SQL Logins, an article that turned out to be far more popular than

I expected. Seems we all have our opinions about SQL logins versus NT

authentication and may of you were kind enough to post your comments - some

agreeing and some disagreeing. That's a good thing. I think the combination of

an article and reader feedback provides a first rate learning experience. If you

haven't read the article yet I encourage to do so now and do take the time to

read through the comments too!

And before we begin, I'd like to announce that the winner of the

participation content linked to the article is CurtM! Curt will be

receiving a copy of SQL

Server 2000 Performance Tuning provided by our friends at Microsoft

Press.

I promised to follow up some ideas 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 came up with. I'll do the best I can, it may well end up being

more than a two part series. Let's start by talking about some of the

objections. I'll consolidate/paraphrase the comments here to keep this succinct:

If you use SQL logins you can't track user activity in the database.

This is true but incomplete. Definitely you loose the ability to leverage

built in functionality to attach the NT username to any action that occurs. If

you're using stored procedures for access (and you should) then can work around

this by adding one extra parameter to each procedure. Alternatively you could go

with the simpler approach of logging each time they connect, recording hostname,

username, date, then you at least have a way to see who was connected and you

could do the auditing from based on hostname. Or something along those lines.

Downside is some additional complexity, possibility of someone spoofing the

procedures maybe?

Users should only have access to the database via stored procedures.

In theory I agree. Even in practice for the most part. Right up to the point

where you need to right an extended search mechanism. Then you're forced to

write dynamic sql (either on the client or in a proc), but in either case

they'll need table/view access to make it work. Trying to do it without using

dynamic sql leads to some serious maintenance issues and/or dirty hacks which

are not worth it in my opinion. Using procedures only means no ad hoc access,

something that you'll need sometimes.  Regardless of your point of view on

this, it really comes back to authentication first.

What about application roles?

As a reader pointed out application roles really go hand in hand with WA.

They let you use WA for authentication and then seriously restrict their access.

Cool concept. The problem is managing the password you have to use for

sp_approle. Once you commit to managing the password, why not just go with a sql

login? Before you scream, I realize there are reasons to still go with NT

authentication, but still - see the point about the passwords?

You can't expire/require strong sql passwords.

Just to be clear, in no way am I advocating each user having a sql login and

password. I'm 100% for the concept and practice of near zero administration!

What I am advocating for enterprise development is one sql login and password

per application. Setting that password then becomes just a very minor management

task for the DBA - big IF here - you haven't compiled the password into the

application!

If a user has rights to run a procedure based on WA, what's wrong with

letting them run the proc from Access or whatever? If they can do something bad

it must be a bad design.

Have to disagree with this one. The only way to make it safe to run the procs

from any application is to code all the business logic into them, when it should

almost always go in the middle tier (my opinion of course, but I think its a

generally accepted principle). Not all business logic can go in a proc. Rules

change often, more importantly they often depend on data that is outside the

scope of the proc itself.

Are you saying that administrators should connect using SA instead of WA?

I believe that normally no one should use SA for anything. For administration

I see two options, each viable. The first is to grant syadmin to users via NT

group (Domain DBA or whatever), this lets you instantly disable the account if

they leave. Downside is that unless they have more than one NT login, they are

always working with sysadmin privileges. The other option would be to have one

sql login per admin and make it a member of sysadmin. If the user leaves change

the password. In my opinion when an admin leaves due diligence requires all sa

level passwords to be changed in addition to disabling their NT account.

WA is better/easier for tools intended for DBA's.

I have to admit preferring WA for admin access. I think the deal here is that

you have to support both methods, which leaves the choice of which is better to

the end user!

Isn't not having an automatic lock out after x failed attempts a serious

flaw?

No doubt! If someone is hacking your SA account, sure, but in practice I'm

not sure it matters at the application level. I say this because once the app

has been debugged and deployed, you should never have an error in the login

process from that app AND no one should be using that login administratively

other than maybe an admin or developer to test a change or a problem in the same

security context of the app (roles really are the way to avoid even this case).

I don't want an NT admin to hijack my databases.

I think removing builtin\admins provides a good enough fix for this. If

you're worried beyond that, perhaps you need a new NT admin? I work at a small

company with one admin and four people with domain admin access so I'll admit to

never having had to solve this particular problem.

How about a poll so we can see which method other readers are using?

Great idea! Please look

here for the results. As I write this looks like 'both' is the most common

answer.

Using WA makes it easy to move databases between servers, no ID's to fix

up when you're done.

True. If you move a database with sql logins you will probably have to run

sp_change_users_login for each one unless you've moved master as well.

Ridiculous that sp_attach doesn't offer the option to do this automatically and

tell you about any that are still bad after that (sqlwish@microsoft.com!).

Ranting aside, it's easier enough to handle and not a good enough reason for

deciding one way or the other - in my opinion anyway.

Was that fun or what? My thanks again to all who offered feedback. If I've

missed responding to an important point...or you still don't agree, post again!

Now on to a few other points to support my case. Let's start by talking about

the biggest problem we have with sql logins - we either have to compile into the

application, rely on the user to enter, or have a place to store the password.

Let's rule out having the user enter as bad and unwieldy.

Compiling into the application has the merit of simplicity right up until you

need to change the password. Why would you need to? If it's one login per

application and no end user has the password, do you need to change it?

Potentially you do if/when a developer leaves since this represents a large

security hole...IF...they have the means to access the network. If you don't

think you'll ever change the password...this method is for you! The other

downside is that you don't want to put the login or password into the executable

as plain text - it's way too easy to open an app in NotePad and browse for

readable text. I'm not qualified to start a discussion of encryption, but I will

say that just obfuscating them is far better than doing nothing and very easily

done.

What if you do want the ability to change the passwords? Option number one is

to recompile and redeploy the application. How much this hurts depends...! To

start with you'll save yourself a lot of time if all applications stored

logins/passwords in a separate code module (connections.bas or whatever) and use

the same technique for obfuscation. Change the password, have whoever does the

build enter the new value in the app, recompile, test. Now to get it to the

desktop. One way is to have the users do it. Clunky, but good enough sometimes,

if they have permissions to do so. Or you can visit every machine, login in as

local admin, install. Volunteers for that? Moving to a more realistic solution,

you can use a tool like SMS

that let's you deploy remotely, or build your own. One method I've seen that

works well for simple apps is a launcher.bat file that checks a network share

for a newer version, copies it down to the correct folder, then runs the exe.

This not only gives you the ability to put out a new build for password changes

but for changes in general - few apps are static. Installshield

is also offering a product that let's you do a 'Windows Update' for your

applications. Other options exist. My point here is that you need a way to

deploy the applications anyway, so once you have that compiling for password

changes is only a minor task.

Yet it does seem clunky doesn't it? Option number two is a concept I called 'keymaster'

last time and which several readers mentioned they had used in one form or

another. Basically it's a way to store the password where the client app get

access it yet you can easily change it. Simple options are an ini file, a udl

file, or a registry key. In all cases you definitely need to at least

obfuscate the password. Please! They all work. Making the password change

though...how to do that? One way is to copy them out to the users in a login

script. Another would be to keep the file on a share that users can access (from

the app of course). Securing them is tricky since all users have to have read

access, potentially allowing them to view the clear text. We're all SQL people

here so an obvious solution is to have a central table that stores the

application name, login, and password. Put this table in a separate database,

use WA to access it and the only access allowed will be a proc that returns the

obfuscated (or encrypted!) login and password based on a requested application

name (in simple terms, but is probably better to use a GUID or some other

identifier so that even if the user executes the proc directly, they first have

to figure out how to identify a row in the table). Then in your applications the

first step is always to connect to the keymaster, retrieve the appropriate

connection information, decrypted/deobfuscate - after that it's business as

usual. You can even put all that code into a shared dll.

Ultimately I think if you have a way to deploy your applications easily AND

easily change the passwords, using sql logins for applications becomes quite

feasible. You could of course use the same system to use WA in concert with

application roles, not a bad idea at all. I do agree with the readers who stated

that there is a place for WA and that one solution fits all rarely works. My

goal here is to get you to really think about how all this works. Most of us

learn the login stuff very early on and take the MS line on it for granted, not

a bad thing now that we're older and maybe wiser to revisit to see what really

works and what doesn't.

No contest this time, but I'll hope you'll post your comments anyway. We're

all here to learn and your comments are valuable!