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
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
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
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
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
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 (firstname.lastname@example.org!).
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
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!