Authentication mode in SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Authentication mode in SQL Server

14 posts, Page 1 of 2

1 2 »»

Authentication mode in SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

AER

Posted Tuesday, January 10, 2012 3:45 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:30 AM
Points: 511, Visits: 179

Hi everybody,

Most of the Servers in our Company are using SQL 2008 editions with the mixed 'SQL Server and Windows Authentication' mode.
Currently our application is utilizing the connections to the SQL Server from the WEB.Config file using SQL Server login set under SQL Server authentication.

Can anybody please tell me if a 'SQL Server and Windows Authentication' mixed mode is most secured for SQL Server or should we better use just the 'Windows Authentication' mode?

In that case scenario how should we set the connections to the application?

You help with this matter is greatly appreciated.
Thank you.

Alex

Post #1233607

Robson Brandão

Posted Tuesday, January 10, 2012 5:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:01 PM
Points: 124, Visits: 278

Today, the mixed mode is as secure as Windows Authentication mode. In mixed mode, Windows uses a DLL AD policies for accounts and passwords.
If you are not in AD, it uses the local security policy.

Post #1233642

Jack Corbett

Posted Wednesday, January 11, 2012 8:39 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

I still think Windows authentication is more secure. At the very least people do not have to remember a second login, and you aren't storing a login in the web config.

There are 2 ways I know of to get IIS to work with Windows Authentication:

1. Make sure you are using Kerberos authentication. K. Brian Kelley has a good article on this site for setting this up. Using Kerberos allows for multi-hop authentication, so your web app can run under one account, but pass the connected users windows credentials to SQL Server for authentication.
2. A simpler way is to have the app pool your web app is running under use a domain login that you grant the appropriate database rights to. The issue with this is that you can't see the actual user that is using the application, you always see the application account.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1234046

AER

Posted Wednesday, January 11, 2012 9:05 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:30 AM
Points: 511, Visits: 179

Jack,

Thank you so muck for your help. I totally agree that the Windows Authentication is the most secure way of handling the application pools.

Now since I'm more on SQL Developer/DBA side rather than .NET Developer you should forgive me if this question sounds stupid:
in your 2nd case scenario, how would the WEB Users (not Developers) connect to the SQL Server if it is set only with Windows Authentication?

Also I would greatly appreciate if someone will tell me how applicable in this case scenario is an Application Role?

Or is it not a related topic at all?

Any help with this issue would be greatly appreciated.
Thank you.

Alex

Post #1234078

Dev

Posted Wednesday, January 11, 2012 9:16 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

I don’t prefer Windows Authentication based database servers for Web Application. It’s better if user login with Application (Web) Login Name and Application Server translate its credentials to SQL Server login for database operations.

~Dev~

Post #1234097

azdzn

Posted Wednesday, January 11, 2012 9:23 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:46 PM
Points: 1,493, Visits: 239

Dev (1/11/2012)

Why is it better ?

I don't agree because anyone who has access to the web application config file will be able to retrieve sql login and password and then access to databases even if there is no reason for that.
Unless there is some encryption involved.

Post #1234108

Jack Corbett

Posted Wednesday, January 11, 2012 9:30 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

AER (1/11/2012)

I thought I had shared that in my original post. The users would connect to the web site as they currently do. I assume that if they are intranet applications that AD is handling permissions. In case 1 of my original post the web application would then connect to the SQL Server using the domain login that it is running under and the SQL Server wouldn't know who the true end user is unless the web application is coded to pass the information to the database.

In case 2 the connection to the SQL Server would be made using the End users domain account so the SQL Server would know who the end user is using the SYSTEM_USER function, etc...

Also I would greatly appreciate if someone will tell me how applicable in this case scenario is an Application Role?

Or is it not a related topic at all?

Any help with this issue would be greatly appreciated.
Thank you.

Alex

Application roles are a whole different beast and in SQL Server 2005+ you wouldn't really want to use an application role you'd want to use EXECUTE AS.

Post #1234120

Jack Corbett

Posted Wednesday, January 11, 2012 9:33 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

Dev (1/11/2012)

Windows authentication is definitely more secure. It IS more difficult to setup, but more secure. Even for external applications I'd still prefer to have my application handle user authentication and then have the application connect to the SQL Server using Windows authentication via the app pool domain account so that there is no SQL login information exposed.

Post #1234126

Steve Jones - SSC Editor

Posted Wednesday, January 11, 2012 9:46 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:03 PM
Points: 31,406, Visits: 13,723

I tend to agree with Jack. Win Auth is more secure and no passwords are stored in any config file. The only passwords are those stored in the services portion of the app config, or the IIS config.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1234141

Dev

Posted Wednesday, January 11, 2012 10:12 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

If somebody can login to Application Server & read configuration files, he can delete website as well (worst scenario).

SQL Server Logins are manageable for Web Applications. Few logins (Logical Grouping based on roles) can manage overall database operation for the Web Users. I don’t find it logical to create 1000 Users / Logins in database until I have explicit Audit Requirements to track each user’s activity.

~Dev~

Post #1234170

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions