SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Case for SQL Logins - Part 1

By Andy Warren,

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!

Total article views: 12606 | Views in the last 30 days: 3
Related Articles

Passwords - case sensitive in Login using SQL Server Authentication

Passwords - case sensitive in Login using SQL Server Authentication





SQL Authentication – Forcing Password Changes

When you create a SQL Server login (with SQL authentication), you have the option of enforcing passw...


login issue from application

can not login from application


SQL Login Password Audit

Audit your SQL logins to find passwords that have never been changed or that are very weak.

sql server 7