Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Authentication mode in SQL Server Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 3:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 11, 2014 2:21 PM
Points: 517, Visits: 219
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
Posted Tuesday, January 10, 2012 5:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:30 AM
Points: 127, Visits: 296
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
Posted Wednesday, January 11, 2012 8:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 10,282, Visits: 13,265
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
Posted Wednesday, January 11, 2012 9:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 11, 2014 2:21 PM
Points: 517, Visits: 219
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
Posted Wednesday, January 11, 2012 9:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1234097
Posted Wednesday, January 11, 2012 9:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:57 AM
Points: 1,547, Visits: 258
Dev (1/11/2012)
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.


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
Posted Wednesday, January 11, 2012 9:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 10,282, Visits: 13,265
AER (1/11/2012)
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?


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.




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 #1234120
Posted Wednesday, January 11, 2012 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 10,282, Visits: 13,265
Dev (1/11/2012)
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.


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.




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 #1234126
Posted Wednesday, January 11, 2012 9:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Posted Wednesday, January 11, 2012 10:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1234170
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse