February 4, 2009 at 9:45 pm
Can anyone provide a list of pros and cons of both methods of authentication?
February 4, 2009 at 10:00 pm
Windows Authentication mode allows a user to connect through a Microsoft Windows user account
and
Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.
SQL Server Authentication is provided for backward compatibility. For example, if you create a single Windows group and add all necessary users to that group you will need to grant the Windows group login rights to SQL Server and access to any necessary databases.
February 5, 2009 at 7:09 am
Hi,
Kindly refer to this article for more details on this
http://www.sqlserverdba.co.cc/2009/02/advantage-disadvantage-or-pros-cons-or.html
Thanks! 🙂
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 5, 2009 at 7:46 am
How about the biggest one: single source for all security management is Active Directory? If you've given access via Windows groups, a Directory Services administrator adds the user to the group. Access is automatically granted. Takes advantage of economies of scale with multiple SQL Servers, multiple permission sets, etc. When the user no longer needs access, pulling out of the group removes access. And security cases where you need to immediately terminate access is easy and quick. Disable the AD account and access everywhere is blocked.
You also get into Kerberos authentication, which ensures that the client can verify the SQL Server is legitimate (which you can't do with SQL Server authentication).
K. Brian Kelley
@kbriankelley
February 6, 2009 at 6:54 am
One downside of using Windows authentication is that a user could create an Excel, Word, or Access file that connects via ODBC directly to the database. That is a big security risk we could not afford.
We chose SQL Server authentication. Our front-end application has several modules, and it inspects the user's Windows ID to determine which module(s) they have access to. The various managers can control those permissions for their employees directly from the application. The front-end runs on a terminal server, so AD controls whether or not they can connect to the TS and thus to the application.
February 6, 2009 at 7:27 am
WILLIAM MITCHELL (2/6/2009)
One downside of using Windows authentication is that a user could create an Excel, Word, or Access file that connects via ODBC directly to the database. That is a big security risk we could not afford.We chose SQL Server authentication. Our front-end application has several modules, and it inspects the user's Windows ID to determine which module(s) they have access to. The various managers can control those permissions for their employees directly from the application. The front-end runs on a terminal server, so AD controls whether or not they can connect to the TS and thus to the application.
In this particular case, you're better served using a Windows service account for the application. This eliminates your concern and puts the account used for login back into Active Directory's hands. That means it'll be controlled by the domain password security policies for complexity (which could be bypassed on SQL Server) and it also means it can be locked down where it can only run on particular systems. With a SQL Server login, you don't have that lock down capability.
K. Brian Kelley
@kbriankelley
February 6, 2009 at 7:39 am
With a Windows service account we would not have the granularity i.e. each user has specific permissions within the app based on their Windows ID.
February 6, 2009 at 7:54 am
But you've said the application was already determining what they could and couldn't do, right? If that's the case, you're relying on the app to enforce security. But you're already doing that anyway. Or at least, from a usability perspective, you should be. No point giving the user what looks to be functionality which, when they try to use it, they're told they can't.
K. Brian Kelley
@kbriankelley
February 6, 2009 at 8:10 am
Yes, the application determines what they can do, based on their Windows ID. If we had used a Windows service account then we could not distinguish who the user is, and therefore could not determine what they have access to.
The user interface will hide certain modules, or disable buttons within visible modules, based on the permissions granted within the app by their manager.
February 6, 2009 at 8:22 am
WILLIAM MITCHELL (2/6/2009)
Yes, the application determines what they can do, based on their Windows ID. If we had used a Windows service account then we could not distinguish who the user is, and therefore could not determine what they have access to.The user interface will hide certain modules, or disable buttons within visible modules, based on the permissions granted within the app by their manager.
The service account takes the place of the SQL Server login that you're using so it only works from the application to the SQL Server. They still log on to Terminal Services using their Windows account. So the application still knows who they are and handles security appropriately.
K. Brian Kelley
@kbriankelley
February 10, 2009 at 2:11 am
Windows authetication is certainly more secure and more easily managed (allocating permissions to Windows groups, centralising security at the Windows level) BUT.....plenty of vendor supplied packages assume mixed mode authentication and use SQL Server logins so by insisting on Windows authentication at your siteyou'll be excluding a raft of products which won't please the business.
In fact 'security' on many of our vendor supplied systems is rubbish, they don't think security is their problem but their application stops working when you try and tighten it!
February 11, 2009 at 8:07 am
I'd just like to add that having Windows only authentication disables the super 'sa' admin account. That may or may not be a good thing, depending......
Tim White
February 11, 2009 at 10:13 am
2 Tim 3:16 (2/11/2009)
I'd just like to add that having Windows only authentication disables the super 'sa' admin account. That may or may not be a good thing, depending......
True, but anyone with sysadmin membership or CONTROL SERVER permissions has the same rights. Also, all I gotta do is flip the registry setting to a 0 or 2 and get you to restart SQL Server (or the OS) and the sa account is now active unless you've disabled it in SQL Server (it is by default in 2008 when you install in Windows authentication only mode).
K. Brian Kelley
@kbriankelley
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply