SQL Authentication Types

[rayj00]

I know enough about SQL to be dangerous. I do not normally
use the product, but I do perform security audits on it.

My question has to do with authentication. There are
3 methods to use: 1. Windows 2. SQL 3. Both

From my little experience, I know that number 1 (Windows)
is the recommended authentication method. What I'd like
to know is: How does method 3 work? I'm guessing that
if a user cannot authenticate using SQL, then Windows
authentication is tried. Am I correct?

PS: I did search Microsoft but came up empty.....

Thanks,

Rayj00

 

[SQLBill]

In SQL Server, you create a login. This login can ONLY use one type of authentication - either Windows Authentication or SQL Server Authentication. When the person logs into SQL Server, their login is checked to see what type of authentication it is supposed to use. Then SQL Server gives access if everything including the password is correct.

Let's say I want a login on SQL Server and it's using Mixed Mode. I can make the login SQLBill with Windows Authentication and then I don't need to provide a password when I login...my Windows account takes care of that. Or I can make a login SQLBill with SQL Server Authentication, but then I have to provide a password which I will use every time I need to log into SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

One thing that SQLBill didn't mention is that when the user connects to SQL you have to tell the SQL Server what type of authentication you are going to be using. On some applications (such as SQL Enterprise Manager, Query Analyzer, etc) there are radio buttons or check boxes. For some applications it's hard coded in the connection string as to which method will be used.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rayj00]

Since MS suggests to always use Windows authentication,
what are the security implications if you use SQL or
even mixed authentication?

Thanks for your quick replies guys, I appreciate it!!

Rayj00

 

[SQLBill]

The main problem with using Windows Authentication ONLY is that in a multi-domain environment, not all of the domains may trust each other. This means that if you are a member of Domain A, you authenticate your log via Domain Controller A. But the SQL Server on Domain B, uses Domain Controller B for authentication. If DCA and DCB don't recognize each other's logins, then it's an untrusted connection and Windows Authentication will fail. But if they do recognize each other's logins (usually by each being a backup for the other), then it's a trusted connection.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

The problem with SQL auth accounts is that the username and password are sent in clear text. Also SQL auth accounts can be brute force attacked.

This is much harder to do with Windows accounts as most domains are setup to lock out an account after a few failed attempts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rayj00]

Thanks for all of the replies, guys.
You've been a big help...

Ray