June 11, 2007 at 11:27 am
I would like to get your opinions on the cons and pros of using Windows Only vs Mixed mode authentication in the following environment:
MSDE SP4 on Windows XP SP2. The client is a .NET application running on the same computer. In the future it is possible that some of the data will be shared among multiple instances of similary set up computers. The method of sharing has not been determined at this point but it might be a master server on a separate computer or some kind of linked environment.
The computers might be hooked up to a network but are not expected to authenticate to any Windows domain, NDS tree or anything like that.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
June 11, 2007 at 2:10 pm
1. To use Windows Authentication, the domains must trust each other. That means that the Windows Logins must be able to be authenicated by the domain controller (or wherever the authentication in the domain takes place) for the SQL Server. This is no problem if all logins will come from the same domain that SQL Server is connected to.
2. What happens if the domain goes down for some reason? Or the domain name is changes? You won't have any way to get into SQL Server.
3. The SA account will not work under Windows Only Authenication.
I prefer Mixed Mode and requiring Windows Group Accounts or as needed Windows logins for all users. Then I can still use the SA account if I need to do so.
-SQLBill
June 12, 2007 at 7:16 pm
Windows Authentication is more secure by design. Creating database accounts basically creates shared accounts. Not to mention that it's pretty easy to sniff the network and find database account passwords when users/applications are logging in. So if the database needs to store confidential information, it's worth considering the extra effort and planning.
Generally speaking however, I have mostly seen mixed mode authentication for many reasons. For instance if you take a trace and want to play it back in a lab somewhere else for performance tuning, it will be much easier if you use database accounts since no domain is necessary.
Since there are different ways to strongly secure database accounts, I also prefer mixed mode authentication overall for most applications.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
June 12, 2007 at 7:23 pm
SQLBill,
Thanks for the reply.
Since the app and the server reside on the same computer I was just trying to get an opinion if one of the authentication methods (Windows or SQL) is better then the other. Could both of them use the direct memory connection (or whatever that concept is called)?
The MSDE is going to be installed with the Mixed Mode. But I am not sure if logging into the server should be done using the SQL logins or Windows logins. I am leaning towards using the SQL login so I don't have to deal with the Windows authentication.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply