I have an MS-Access 2007 front end. I will have multiple users on it. They are all going to be on the network company domain. Should I be using Windows authentication or SQL Server authentication to be connecting to SQL Server 2008 via ODBC?
+5
A:
Windows auth will allow a more seamless authentication process, single sign on!
From the below article:
Disadvantages of SQL Server Authentication
- If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
- SQL Server Authentication cannot use Kerberos security protocol.
- Windows offers additional password policies that are not available for SQL Server logins.
Advantages of SQL Server Authentication
- Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
- Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
- Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned SQL Server logins to receive the status of their orders.
- Allows SQL Server to support Web-based applications where users create their own identities.
- Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.
Here is a good article on the pro's/con's for each. http://technet.microsoft.com/en-us/library/ms144284.aspx
Dustin Laine
2010-10-22 17:14:10
It's a switchable option in our application. The IT folks in larger organizations tend to like the single sign-on for support, and the users like it for simplicity.
DaveE
2010-10-22 17:24:52
"•Windows offers additional password policies that are not available for SQL Server logins." well the password policies apply to sql server logins when you click the "enforce password policies" option.
DForck42
2010-10-22 19:30:31
+1
A:
Like many things when it comes to security implementations what you should do depends on your goals.
I am not sure from your question if you intend to pass each user's credentials to SQL Server or if you plan on having a single login using either Windows Authentication or SQL Server Authentication. If you choose the later option SQL Server Authentication is probably the way to go as it limits network accounts. If you choose the former Windows Authentication is probably better for your users as it provides single sign on.
As an aside my personal preference is to have each application use its own set of credentials for database interaction. This limits users to only having the privileges provided to them by the application. In this manner your users needn't worry about authenticating to the database as your application will take care of that for them.
ahsteele
2010-10-22 17:20:43
+2
A:
Dustin hit on a good list of pros/cons. I know nobody wants to make a decision like this for you, but based on your criteria (multiple users, all on company domain); I can't think of a single reason to use SQL Server Authentication instead of Windows Authentication. It was designed primarily for scenarios like this.
Brazzle
2010-10-22 17:29:40
@brazzle if i use windows authent, then dont i have to use every individual user into the users list?
i am a girl
2010-10-22 17:31:28
@i_am_a_girl I believe the default SQL server install will allow anyone on the same domain as the server to connect without adding them explicitly
Brazzle
2010-10-22 17:40:22
@i_am_a_girl I believe this can still work, but you must startup your application as a user on that domain for net requests by running with `runas /netonly /user:domainName\userName path`
Brazzle
2010-10-22 17:51:57
You can add the Domain Users group to the appropriate security role for your database. I don't like doing that -- I tend to create security groups specific to a particular database, and then assign users (or user groups) to those groups. But then, I always have domain administrative permission on the SQL Servers I work with.
David-W-Fenton
2010-10-23 00:56:13
+3
A:
If you have a Domain, and Active Directory, you should not even think of something else than Windows Authentication .... not a single second!
Philippe Grondier
2010-10-22 18:44:10
@phili i appreciate ur advice. the thing is sometimes the same user accesses the database frmo a different location
i am a girl
2010-10-22 18:46:26
a different location on the same network\domain? He then just have to logon with his\her credentials?
Philippe Grondier
2010-10-22 21:46:34
If the database is accessed from another domain, with other credentials, you'll have to manage something called 'domain trust' (I think it is the right expression)
Philippe Grondier
2010-10-22 21:47:48
@phili so looks like i was right sql server authentication is the best
i am a girl
2010-10-22 22:09:21
I do not understand. What is the situation that forces you to use sql server authentication? Once you go for it, you abandon your ability to have an integrated and centralised security management system, and you'll definiteky regret it!
Philippe Grondier
2010-10-22 22:19:19
+1
A:
Seeing as you have a desktop front end that is connecting directly to database...your best option is to go with windows auth. Keep in mind by doing this, the user can bypass the frontend and access the database directly. You'll need to grant them the minimum db permissions required. Also, you'll want to use a AD security group instead of adding each user.
Sql auth is best for when you control the config file...for example a web app.
dotjoe
2010-10-22 18:54:52
right. You have to be carefull when giving rights to the user. But this is true whatever is the authentication mode. How many databases are running with hundreds of users being given dbo rights, just because the guy who installed the database and the software didn't give a shit about security? There is even one in my company!
Philippe Grondier
2010-10-22 21:50:45
@Philippe Grondier it is true for a desktop application accessing a database directly. You really need another layer to securely access the database...be it stored procedures, web service, etc...
dotjoe
2010-10-23 02:31:48
@ David: hehe! neither Philippe nor David! I will not talk even under torture!
Philippe Grondier
2010-10-23 07:14:54
In my work, I'm constantly running on to this idiot from about 10 years ago. His name is David W. Fenton.
David-W-Fenton
2010-10-24 02:50:51
Not ideal, but you can use an Application Role that has all the privileges the app needs and users/user group can have very limited access.
Jeff O
2010-10-24 21:03:06