September 28, 2009 at 8:43 am
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
There are 10 types of people in the world, those who understand binary and those that don't. 😀
September 28, 2009 at 8:53 am
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.
September 28, 2009 at 9:03 am
We always stick to mixed mode authentication, it's a company policy.
September 28, 2009 at 9:31 am
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.
September 29, 2009 at 3:26 am
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
There are 10 types of people in the world, those who understand binary and those that don't. 😀
September 29, 2009 at 4:10 am
Depends on the company mostly, if you have third party apps, they tend to depend on using sql logins.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 29, 2009 at 4:21 am
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!
September 30, 2009 at 11:01 am
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.
September 30, 2009 at 11:28 am
The same thing can happen with a rouge admin in SQL. If you do not trust and audit your admins, you are in trouble.
September 30, 2009 at 12:03 pm
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2009 at 4:36 pm
Steve Jones - Editor (9/30/2009)
The same thing can happen with a rouge admin in SQL. If you do not trust and audit your admins, you are in trouble.
Yes that is very true. You do hope that you can trust your AD admins but...
I suppose if you keep your SQL SysAdmin level logins using SQL authentication, then at least if you're going to have a rogue element granting or removing rights, then that rogue element will be on the same DBA team as you.
With the AD admins, you might not know these guys, they might not even be in the same building as you. But they effectively have that power over your SQL Servers and databases.
October 1, 2009 at 3:54 am
Using Mixed mode is our company policy, simply because we want to be able to administrate our SQL servers even if the Domain Controller is offline. We make sure that the SA accounts are only known to 3 persons in our company.
October 1, 2009 at 4:36 am
It is probably poor practice, but though we keep our complex sa passwords secure, we tend to connect as sa to do a lot of DB Admin. It seems convenient to have an anonymous general-purpose owner of databases and jobs, rather than the hassle of person-specific Windows accounts that may need to be disabled, with knock-on effect on objects they own. But I probably need to do more work on understanding and making better use of SQL Server security.
Andy Hogg (9/30/2009)
With the AD admins, you might not know these guys, they might not even be in the same building as you. But they effectively have that power over your SQL Servers and databases.
I agree. We have procedures for granting access to applications that require verification that someone has been trained to use them, but contractors working at AD/domain level who will just drop accounts into relevant access groups. Needing a SQL login is my second line of defence to ensure the proper process is followed.
October 1, 2009 at 4:54 am
One question i have regarding those that use a purely windows environment, is how do you keep your users from using tools like access/excel etc to look at your data?
I can only think of 2 ways
1) use SP's all data reads in the application(s). what a lovely world that would be, but in most workplaces that just doesnt happen
2) using application roles and apply the permissions to the required objects.
ok maybe there is a 3rd route looking at encrypting data but i wont count that as one.
Just interested to see what people do out there.
At my place it is (and always has been before me) a mixed auth mode where all the apps etc use sql logins (sa no less - but lets not start on that one 🙂 )
October 1, 2009 at 6:55 am
You can use V-LAN to secure the DB. Put the DB in one V-LAN, the application that has to connect to the DB in another V-LAN and the normal user in a seperate V-LAN. Allow access to the DB V-LAN from only the Application V-LAN and the WebServers. The user V-LAN, restrict their access only to the Application V-LAN. make sure you do not have tools that can be used to connect to Databases in the application V-LAN. Then the normal users will not be able to connect to the DB V-LAN with their excel and access.
-Roy
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply