Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

SQL Server Security Basics: Why Would I Use SQL Server Authentication?

The standard best practice answer when it comes to connecting to SQL Server is to use Windows authentication. However, SQL Server-based authentication exists, and there are some times when it's a best practice (or only practice) to use it. Wait a second... what do you mean by having two best practices? Well, as with most things in security and architecture, what is the best practice in a given situation depends on the situation. So let's look at two common reasons why you would use SQL Server authentication over Windows authentication.

Third Party Products

This one is a no-brainer. You've purchased a third party application and it only uses SQL Server-based authentication. If that third party application is important to your organization, you're now stuck between a rock and a hard place, right? After all, the recommendation is to use Windows authentication! Well, not really. The fact of the matter is you can't do it. The application doesn't support it. So you have to make do with what the application supports. Either that, or you've got to eject the application from your environment. That's not happening? Then SQL Server authentication it is. And that reminds me of a baseball analogy.

When I played Little League I was blessed with some really good coaches. They didn't just teach the skills, they taught how the game worked. Simple things which seem obvious to me as an adult I realize wasn't so obvious as a 10, 11, or 12 year-old kid. Like one time my team was down by more than 4 runs. The bases were loaded and I was going up to hit. Before I left the batting circle, my coach motioned to me so I could look at him. He said, "Make contact, get a hit. Don't overswing and end up striking out." I think I remember saying, "But if I get a homerun, we score 4 runs!" To which he said, "Even if you hit a homerun, we're still losing the game. It's more important to get a base hit. So keep your eye on the ball and get a good swing. Don't overswing!" And it clicked what he was saying. So I went up, put the ball in play and got a base hit, drove in a couple of runs, and didn't get an out. Unfortunately, we lost that game (we lost every game that season).

The same idea is true here. If it can't be done, it can't be done. The application doesn't support Windows authentication and you can't make it do so. You can request a future version support it, but until the vendor writes that into the application (if the vendor does so), you use SQL Server authentication. If it represents an exception to your environment, save the installation/configuration documentation. Do a short exception write-up about where in the documentation it says you have to set it up to use SQL Server authentication. If need be, get acknowledgment from the appropriate powers that be and save that evidence with those documents. And then move on.

Applications in Untrusted Domains or in the DMZ

Using Windows authentication assumes you have a connection coming from a trusted computer. The simplest example is when everything is in the same domain. There are cases where a computer may be coming from an untrusted domain or the computer isn't in a domain at all. In these cases, you can't use Windows authentication. The most common example is a computer in the DMZ, the area firewalled off from the Internet, but firewalled off from your internal network. Computers in the DMZ are usually accessible from the Internet.

For instance, a web server that is your company's presence on the Internet is likely in a DMZ. By rule, these computers are supposed to be off any internal domain. The reason is simple: if I compromise this DMZ computer from the Internet and it's on an internal domain, I can launch a direct attack against at least one of your internal domain controllers. And that's not good. By being on the domain it's got to be able to talk to domain controllers. And the way it'll talk to a domain controller is the same pathway I would use to attack that domain controller. Therefore, these computers are usually put in workgroups. Since they are in a workgroup, they can't use Windows authentication to connect to SQL Server. They must use SQL Server authentication.

What About Shared Services and Resource Pooling?

It's not unusual to have a case where multiple systems hit a particular resource and that resource makes a connection to the database server. A good example is a web service which multiple applications use. The web service may authenticate the individual incoming connections, but for the purposes of efficiency you want to make a connection back to the database server using a single account. This means the connection string can be the same every time and this means you can take advantage of resource pooling. A perfect candidate for SQL Server authentication, right? Well, not necessarily. If the web service runs on servers where you can establish that trusted connection to SQL Server, a better option is to use a domain user account, only make sure it doesn't belong to a particular user. A lot of folks call this a service account. A savvy domain administrator can even lock down what servers that account can run against, meaning if Joe Smith somehow got the username/password and tried to run it from his workstation, it would fail. In this case, Windows authentication is the best option. You still get resource pooling. You're not relying on a SQL Server-based login. And you can apply additional security options to the account to help make it harder to be abused.

 

Comments

Posted by Bobby D on 24 April 2009

My apologies in advance if this is a puerile question (I'm a developer, not a dba), but reading your post makes it seem like authenticating to a sql server is an either/or situation.  Why not used mixed-mode logins wherein users may connect via windows authentication or sql server authentication?  You can easily restrict users using the latter via permissions on the database server.  Or is there a security risk involved in doing this?

Posted by K. Brian Kelley on 24 April 2009

It is. But apart from the configuration of the SQL Server, you want to use Windows authentication when possible. This allows a single security system (Active Directory) to control authorization throughout the environment. Also, it ensures the password isn't stored in the SQL Server, but on a domain controller. These tend to be locked down and restricted more than the average SQL Server. Therefore, getting at the password hashes to attempt to crack 'em is more difficult.

With respect to SQL Server configuration, the Principle of Least Privilege applies here... if you can restrict a SQL Server to only use Windows authentication, then you configure it to do so. That prevents attacks against sa or any other SQL Server-based logins. Therefore, reducing the surface area where SQL Server-based logins are not permitted is something you do if you can.

Posted by Steve Jones on 24 April 2009

Nice write-up, Brian. 3rd party apps and a lack of a domain are the biggest places I've seen this ysed.

You can't have both because how can you tell that MyDomain\BrianK and BrianK are the same person? Or do you assume they are, try trusted and fall back to SQL auth and request a password? I could see this as a way to implement security, but it's not there now. I would vote for this if you filed it on Connect.

Posted by L. Mueller on 25 April 2009

Refers to "Applications in Untrusted Domains or in the DMZ"

If I must use SQL Server authentication, then I have to store SQL Server login credentials on the web server. Isn't that also a risk for an attack?

Posted by K. Brian Kelley on 25 April 2009

lmu92, no more so than with Windows authentication. If I have compromised your web server to where I can make an attack on the SQL Server credentials (you've encrypted them using something like ASPNET_IISREG), I can compromise the process where your web application is running. For instance, I drop my own ASP.NET pages into your web application. So whether you use Windows authentication or SQL Server logins, hopefully the Principle of Least Privilege has been applied with respect to database access. It won't stop all damage, but it will minimize it to some extent.

Posted by Ewan Hampson on 27 April 2009

I use SQL authentication to improve security.  Eh?  We have several published apps that are used intermittently during the day, that launch and shutdown slowly (20-50 seconds).  Using Windows authentication the user would log in to the domain and the app launch straight in to the db.  But because logging off and restarting means a perceptible delay, the outcome would be the same user staying logged in all day and everyone using their account - so much for the audit trail.  So I prompt for a second and SQL authenticated login once the app is running under the domain account, and provide a simple and instant logout/login button on the front screen of the app.  Even if the app itself is left running under one person's domain account, the user is much more likely to log off/on, and activity will be recorded against the right (SQL) user.  It is, of course, a pain to maintain, but as soft engineering goes it seems to work.

Posted by K. Brian Kelley on 27 April 2009

Ewan, I understand what you're saying, but what causes the slowness of the application start-up/shutdown? If it can't be helped, it can't be helped. But it almost sounds like the wrong problem is being attacked.

Posted by Ewan Hampson on 27 April 2009

We have a well-established Citrix environment that delivers our standard office apps as well as less-used minor programs.  Apps run from a datacentre with load-balanced servers in Citrix farms, and it all runs very well.  But when you launch a published app in Citrix it has to set up the Windows environment and then launch the app, which takes appreciably longer than just running the app on a PC that is already logged on.  I guess that's the hit you take for having an app that can be so easily deployed.

In principle, we should educate people to live with the overhead, especially since failing to log out, and using someone else'e account, are contrary to security policy.  But I have tried to help people behave as we want them to behave.

Posted by K. Brian Kelley on 27 April 2009

Ewan, aaaah that makes more sense. You can do some things to improve response times on Citrix, but you can't get rid of it altogether. However, to educate them, you may print out the login process and show everyone what all Citrix goes through to make settings, printers, etc. "seamless" to them. Hope you have a large plotter! :)

Posted by Vitali Lisau on 27 April 2009

What about running scheduled jobs with SQL Server account as their owner? I had cases when Domain Controller had network (or some other) issues, and the jobs failed because SQL Server could not authenticate the domain account. Also, when you have frequent jobs (like Biztalk purging jobs that run each minute), this provide additional stress to the domain controller. Maybe, it is a better idea to have SQL Server account as their owner.

Posted by John Chapman on 27 April 2009

Ewan, using windows API's, you can achieve the same instant login/out using windows authentication (rather than SQL authentication). I have a .Net application that does exactly that. You've probably used the "runas" command line utility to run an application as another user, in principal, you can do the same thing within your application and then make a new trusted connection to the DB under the new user's context. Have a look at the "LogonUser" API to get an idea about how this works.

Posted by Ewan Hampson on 28 April 2009

John, thanks for the pointer.  I have seen ways to invoke the Windows authentication routine, but had thought no further than "once I'm running under this account I'm stuck", which is still partly true - I assume the "shell" environment remains the original user's profile.  I guess I would need to re-engineer the app to provide a launcher for the main program, and to think hard about the security issues (after all, I'm allowing the launch app to be run under someone else's account, though I'm not providing access to much functionality).  I'll give it some thought for the next re-write, as maintaining parallel SQL logins is a nuisance.

Posted by John Chapman on 29 April 2009

Ewan, the windows API's allow you to switch an applications thread to another accounts context, you can then make a fresh trusted SQL-Server connection (which will be using the new account). When you're done, you can revert back to the original user context. Although this uses the same underlying mechanisms of the "runas" utility, you this can be done on the fly within your application! This allows you to achieve the same on-the-fly permission elevation that you're achieving with the SQL Server connection method.

Posted by Ewan Hampson on 29 April 2009

John, my thanks again.  I'm going to need to look at this properly.

Brian - thanks for letting me/us borrow your blog!

Posted by ajay.vengalil on 25 March 2010

Hi Brian,

I want your help desparately on the below question.

Could you please help me on how to create a database with SQL server Authentication with a username and password.

And also how to create a database in the server and access it from a client module.

Also I would like to know how to find the path or provider for the database so that I can connect it my VB6.0 program.

Hoping that you will help me.

Thanks & Regards,

Ajay

Leave a Comment

Please register or log in to leave a comment.