Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Case for SQL Logins - Part Two

By Andy Warren,

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 (!). 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 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!


Total article views: 6779 | Views in the last 30 days: 2
Related Articles

change password

change password


changing passwords at next login?

How do users change passwords at next login when they are sql server logins?


Password change

Password change


login issue from application

can not login from application


PowerShell – Change SQL Server Login Password

Here’s a quick post detailing a PowerShell script that can be used to change the password for a SQL ...

sql server 7