<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Mixed Mode Authentication
20 posts, Page 1 of 2
1
2
»»
Mixed Mode Authentication
Rate Topic
Display Mode
Topic Options
Author
Message
Michael G
Michael G
Posted Monday, September 28, 2009 8:43 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, November 05, 2009 5:54 AM
Points: 112,
Visits: 17
Hi,
I'd like to find out the general feeling amongst dbas as to whether to go mixed mode authentication or not?
I've kinda answered my own question already. I have to use mixed mode auth for legacy reasons, but I need {some great} reasons to move away from mixed mode.
Cheers,
Michael
Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
:D
Post #794530
Sergey Vavinskiy
Sergey Vavinskiy
Posted Monday, September 28, 2009 8:53 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 9:12 AM
Points: 200,
Visits: 296
Michael G (9/28/2009)
Hi,
I'd like to find out the general feeling amongst dbas as to whether to go mixed mode authentication or not?
Cheers,
Michael
Microsoft recommends to use Windows authentication when possible:
http://msdn.microsoft.com/en-us/library/ms144284(SQL.90).aspx
But it really depends on environment and a company standards.
Post #794537
Bradley Jacques
Bradley Jacques
Posted Monday, September 28, 2009 9:03 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, March 02, 2010 10:36 AM
Points: 95,
Visits: 119
We always stick to mixed mode authentication, it's a company policy.
Post #794542
Steve Jones - Editor
Steve Jones - Editor
Posted Monday, September 28, 2009 9:31 AM
SSChampion
Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 23,148,
Visits: 6,914
People are split on this. Some like Windows since it keeps things centralized, less passwords for the user, etc.
Some like SQL auth since it divorces you from that link, you can go cross platform.
I think Windows auth is easier, but it's an opinion.
Post #794565
Michael G
Michael G
Posted Tuesday, September 29, 2009 3:26 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, November 05, 2009 5:54 AM
Points: 112,
Visits: 17
Great, thanks for the answers so far.
I'd like to move to Windows Auth as there's less admin involved with extra passwords, users getting locked out, password policies, etc.
The IT Department have a support desk for that kind of stuff whereas (typically) us DBAs and developers aren't (or are we) so lucky
Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
:D
Post #794981
Silverfox
Silverfox
Posted Tuesday, September 29, 2009 4:10 AM
SSCrazy
Group: General Forum Members
Last Login: Friday, November 27, 2009 5:32 AM
Points: 2,683,
Visits: 1,003
Depends on the company mostly, if you have third party apps, they tend to depend on using sql logins.
--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Post #794997
Seth Lynch
Seth Lynch
Posted Tuesday, September 29, 2009 4:21 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 1,091,
Visits: 481
I have to look after servers across dev and test domains as well as live etc - so I created sql logins on those machines and can compare them from one location. I don't have access to Active Directory to start setting up Trusted domains - not sure I'd know how to either.
If we were in one domain I would stick to Windows Authentication.
We also have 3rd party SQL Logins - many of which were SysAdmins before I arrived - none of which have any elevated roles now!
Post #794999
Andy Hogg
Andy Hogg
Posted Wednesday, September 30, 2009 11:01 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 4:17 AM
Points: 307,
Visits: 446
SQL Server authentication has one huge advantage over AD authentication as far as I am concerned.
If your AD is administered by a separate group of folks (as you find in most companies) and you are using AD groups to assign rights within SQL Server, then it's a very simple matter for someone to add themselves to the appropriate AD group, view \ change the data of interest, and then remove themself later.
True this will be logged (if you audit successful as well as failed logins), but how many DBAs read through every entry in the SQL Server logs for successful trusted logins?
I've actually seen this happen. An AD group purely for the SQL Server DBAs had a member added one day by someone with domain administrator rights who decided to promote themselves so that they could create a database themselves and not wait around for the DBA team to do it.
Post #795897
Steve Jones - Editor
Steve Jones - Editor
Posted Wednesday, September 30, 2009 11:28 AM
SSChampion
Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 23,148,
Visits: 6,914
The same thing can happen with a rouge admin in SQL. If you do not trust and audit your admins, you are in trouble.
Post #795909
GSquared
GSquared
Posted Wednesday, September 30, 2009 12:03 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 8,670,
Visits: 4,946
I prefer not to use mixed mode, because that means the sa login is active. Find that one password, and you own the server. Windows authentication, you have to find out the domain name, the user name, and the password.
Another advantage to just using Windows authentication is that it's easy to lock out someone's login that way. Using SQL authentication, applications will end up with username and password in the application somewhere. If you have to fire a DBA, you then have to go through and change the password everywhere it appears. Windows authentication, you generally just have to lock out the former DBA's login, and you'll be fine.
On the other hand, if you have even a single third party application that requires SQL logins, then you either have to choose to set up a separate instance/server just for that app's database, or you have to go with mixed mode.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #795925
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
