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


Authentication mode in SQL Server


Authentication mode in SQL Server

Author
Message
AER
AER
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 245
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



Robson Brandão
Robson Brandão
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 314
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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18814 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
AER
AER
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 245
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



Dev
Dev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3510 Visits: 1602
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.
azdzn
azdzn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 289
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.



Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18814 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18814 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63652 Visits: 19115
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
My Blog: www.voiceofthedba.com
Dev
Dev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3510 Visits: 1602
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search