Who uses Windows authentication only?

Question

Who uses Windows authentication only?

jxg548

SSC Enthusiast

Points: 130
More actions
August 31, 2009 at 4:42 am

#133275

Just curious to know how many people use Windows auth only v. Mixed mode, and how they generally setup their logins.
For example, when I'm creating a report/application, often I'll create a SQL Login and grant the appropriate perms to that login. How would you handle such a situation with Windows-only? Grant the perms to a db role and add the Windows logins/groups to the role?
Does anyone use SQL logins to prevent users from connecting to the instance directly (via query analyzer, ssms), and if so, why does that concern you?
Granting a set of permissions to an entire department works sometimes, but that's more of an exception than the rule in my case; usually it's a only handful of people from varying departments that need access to specific things.
Is everyone more or less in the same boat?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735912 More actions · Answer 1

I've done both, sticking with Windows only logins if possible for users as this ensures that users who leave, or are added, to the company get the proper security permissions.

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

We are using Windows auth only. It is more secure. If you are using windows auth than your application should be more powerful security feature or settings.

You need to create Windows Group and assign respective permission to group.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Ross McMicken SSCarpal Tunnel Points: 4431 More actions · Answer 3

We have to get an exception approved to use a SQL login, and those are only granted for vendor proivded applications. In house developed apps must use windows authentication. I support one app with a SQL login, and maintaining passwords and such is a pain. I much prefer to use the Windows authentication. Our in house apps use stored procedures for data manipulation performed by user programs. Server side programs run under a service ID that has the appropriate permissions to execute SQL statements.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

As a consultant working at many different sites and scenarios, I often have to use what is given to me. However, if I have the choice and/or option to recommend, I always push for Windows Authentication only if possible. The only time I will use SQL logins is if the database is running on standalone server with no Active Directory available (usually hosted server supporting web application).

Even in your instances, I will recommend setting up AD accounts for the application (with nonexpiring passwords, in a different OU that by policy is set to not allow interactive logon on machines). Then I will have middle-tier components (web services, WCF, or DCOM) run under that account, and log into database server with Trusted Authentication. If users need access to the database, I add them (individually, or as a group, or domain users group) to the database with db_datareader roll membership only.

Hope that helps

Mark

jxg548 SSC Enthusiast Points: 130 More actions · Answer 5

Thanks for the replies! I would have liked to hear more from people stuck using Mixed-mode (and why) but I know Windows Auth is the best way to go so I'm not surprised those people would rather stay quiet 🙂

Unfortunately, where I work, it seems the dba is considered more of a PITA than anything... I tried getting a security group created once but was told to leave all that "networking stuff" to the pros (!!!)

Anyway, I'll reference this post to demonstrate how Windows Auth only is the rule rather than the exception... Thanks again

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 6

As a consultant ive seen a lot of environments - some of them were using sql, other were using windows and some both types of authentication. Like other people said - windows is more secure and flexible - but it require some stuff in application (integrated security/windows) and some stuff on client environment (i.e.: active directory, nt domain...).

In the other hand when you are using n-tier systems, problem you have described getting smaller. Because only processing layer has direct connection to the database, and client are not connecting to the database. In this case all security setting are maintained by application engine.