January 10, 2012 at 3:45 pm
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
January 10, 2012 at 5:01 pm
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.
January 11, 2012 at 8:39 am
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
Consultant - Straight Path Solutions
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
January 11, 2012 at 9:05 am
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
January 11, 2012 at 9:16 am
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.
January 11, 2012 at 9:23 am
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.
January 11, 2012 at 9:30 am
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
Consultant - Straight Path Solutions
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
January 11, 2012 at 9:33 am
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
Consultant - Straight Path Solutions
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
January 11, 2012 at 9:46 am
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.
January 11, 2012 at 10:12 am
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.
January 11, 2012 at 10:27 am
Dev (1/11/2012)
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.
Sure, the problem is bigger if they can get to the config files, but why have the DB Exposed at all?
You don't have to create a login for every user to use Windows Authentication if you use the first option I mention. It is still basically using an application login, it is just using an AD account for the app pool instead of SQL Authentication.
You can also use AD groups to limit the # of logins you create. Then login is managed through AD groups and, if you are using least privileges, that works fine.
Jack Corbett
Consultant - Straight Path Solutions
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
January 11, 2012 at 10:28 am
Dev (1/11/2012)
If somebody can login to Application Server & read configuration files, he can delete website as well (worst scenario).
I wouldn't count out some SQL Injection or XSS style attack here. Config files may or may not be more secure than the services configurations, but admins can't get the password from the services items. The password isn't exposed. They could read it, and give a password to someone else when it's it a config file.
January 11, 2012 at 10:43 am
If you are an Everything DBA (Hybrid DBA) then AD management falls in your bucket. I believe AD Management is out-of-scope to DBA roles (in general). However it’s a nice option & I am not denying it.
January 11, 2012 at 10:50 am
Also, Security Measures are based on trust model. If we can’t rely on Administrators (which we shouldn’t) of your application / database then we can’t rely on anyone.
I am aware of incidences where Admin were guilty for security breaches. But should we distrust all?
I remember a recent post in SSC where OP asked to encrypt the data from DBAs for HR modules. Funny though, it’s a valid requirement.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply