November 19, 2009 at 8:55 pm
Comments posted to this topic are about the item Just SQL Auth
November 19, 2009 at 10:12 pm
I seem to remember in the long long ago that there were three options in sql 6.0 and 6.5 where you could chose windows, sql, or mixed..
I don't really see a reason to remove windows security, instead I would advocate that for the vast majority of instances you should be using windows security over sql security.
CEWII
November 20, 2009 at 2:28 am
Recommended or not. This is what the auditors want in a corporate world. To prevent network administrator access. I have not upgraded from SQL2000 yet. Busy planning and playing with SQL2008, but as far as I understand, it is possible to do just that?
5ilverFox
Consulting DBA / Developer
South Africa
November 20, 2009 at 2:45 am
Don't think I'd like just sql Authentication... If I were to have a third option I'd go with requiring both sql and windows authentication (not sql or windows)...
November 20, 2009 at 3:27 am
Ok I'm confusing myself here between authentication and authorisation... which I always do... What I mean is that I'd like to see sql server require both windows and sql authorisation. (obviously you can't do this without both sql and windows authentication)... But basically I don't want anyone connecting to my data who isn't on my domain and hasn't put a password in. Is this too much to ask?
November 20, 2009 at 4:05 am
Japie Botma (11/20/2009)
Recommended or not. This is what the auditors want in a corporate world. To prevent network administrator access.
I agree, and I must admit there are one or two instances where I'd like it too. I know it's possible to amend the security of a SQL server so the built in Administrators group doesn't automatically have God rights, but it's still a mighty big assumption that any of your AD admins should have access to do anything with corporate databases by default. In my experience, few people with the skills to administer an Active Directory domain also have the skills necessary to be an effective DBA.
Semper in excretia, suus solum profundum variat
November 20, 2009 at 4:07 am
I think you are missing the scenario where you have three (or more) equal partners: one company hosting the database, a second owning the data, and a third owning the software.
Obviously, the owner of the hardware may require access at the OS level, but the owner of the data may not want their host to be able to read that content, and the software vendor will not want anyone else having access to their code, so the simplest solution is to turn off access via Windows passwords - it is hard to think of any non-Microsoft software product that makes Windows authentication mandatory.
November 20, 2009 at 4:47 am
majorbloodnock (11/20/2009)
Japie Botma (11/20/2009)
Recommended or not. This is what the auditors want in a corporate world. To prevent network administrator access.I agree, and I must admit there are one or two instances where I'd like it too. I know it's possible to amend the security of a SQL server so the built in Administrators group doesn't automatically have God rights, but it's still a mighty big assumption that any of your AD admins should have access to do anything with corporate databases by default. In my experience, few people with the skills to administer an Active Directory domain also have the skills necessary to be an effective DBA.
I think you are missing the poiint... Windows authentication is just that... authentication. It tells sql server that you are one of a group of people authorised to work on a network... Thus it is not just possible to amemd the security of a sql server it is crucial... You don't control access to your data via authentication you do it through the various security roles schemas etc that you set up within your server which authorise access...
The point however is that if you remove windows authentication you remove the possibility of an extra layer of security because if a user just has a sql password how do you know they are an authenticated user on your network? That may not matter in all instances but why would you really want to remove it?
November 20, 2009 at 5:09 am
Maybe I'm smoking something, but isn't SQL Server--and many things it now does--so tightly integrated into the OS that if Windows Authentication were removeable, it would break most of SQL Server?
Think about it. Now IIS is required for SQL Server. Now we have CLR assemblies, Service Broker, and the new SSIS. Not to mention the .Net Framework stuff. What would turning off Windows Authentication do to those?
Now to Steve's comment about being worried that an ISV didn't know about this issue. This question comes up every couple of months. It's so pervasive that ISTR it being on one of the DBA exams somewhere (probably where the original poster got the idea). And a lot of ISVs are guilty of using the SA account for their programs without verifying what permissions are actually needed for their stuff to run. In fact, ISVs have caused no end of problems for DBAs by insisting they need the highest level of available security.
So why are we surprised that they don't understand the authentication methodology if they don't even understand how basic security works?
That's not to say all ISVs are evil or bad. I used to work for one. You have ISVs owned by people like Brian Knight. That boy don't kid around. He knows his SQL Server and is not very likely to force some poor unsuspecting client to give him SA access "just because." Then you have ISVs like the one I used to work for one. As they lived off the profits of maintenance agreements and designer upgrades, they couldn't exactly afford the best of the best. In some areas of SQL Server, I knew more than they did. And I was just a rookie at the time.
Before I agree to want functionality like this, I want a list from Microsoft of everything that would be affected when Windows Auth is "turned off." Then I'll decide if I really want the capability or not.
November 20, 2009 at 5:12 am
The responses so far are, typically, M$-centric...as if nothing exists outside of the AD domain where the SQL server resides that would ever require access to data in the database. Really folks, you need to take the blinders off once in a while.
November 20, 2009 at 5:14 am
Ben Leighton (11/20/2009)
majorbloodnock (11/20/2009)
Japie Botma (11/20/2009)
Recommended or not. This is what the auditors want in a corporate world. To prevent network administrator access.I agree, and I must admit there are one or two instances where I'd like it too. I know it's possible to amend the security of a SQL server so the built in Administrators group doesn't automatically have God rights, but it's still a mighty big assumption that any of your AD admins should have access to do anything with corporate databases by default. In my experience, few people with the skills to administer an Active Directory domain also have the skills necessary to be an effective DBA.
I think you are missing the poiint... Windows authentication is just that... authentication. It tells sql server that you are one of a group of people authorised to work on a network... Thus it is not just possible to amemd the security of a sql server it is crucial... You don't control access to your data via authentication you do it through the various security roles schemas etc that you set up within your server which authorise access...
The point however is that if you remove windows authentication you remove the possibility of an extra layer of security because if a user just has a sql password how do you know they are an authenticated user on your network? That may not matter in all instances but why would you really want to remove it?
I know what you mean, but Windows Authentication in SQL terms is NOT just authentication; it's authentication PLUS a default set of security options that are far from sensible in many instances. And just because those defaults have been chosen at point of installation, many software vendors and developers write applications that assume those defaults will remain in place (in exactly the same way we've all seen plenty of dodgy apps hardcode the use of sa). That all has the knock-on effect of frequently forcing a DBA to accept a security model that would otherwise be seen as entirely unacceptable.
As for why one would want to remove it, I'd say the answer is back to the good old big fat "it depends". In most cases, I wouldn't want to remove it. However, I'd like the flexibility of having the option to do so if a certain set of circumstances arose that made it more sensible. Do we see Oracle as inherently insecure just because it often doesn't integrate its security with AD? No, so why not have the option available in SQL Server?
Semper in excretia, suus solum profundum variat
November 20, 2009 at 5:19 am
BackupGuy (11/20/2009)
The responses so far are, typically, M$-centric...as if nothing exists outside of the AD domain where the SQL server resides that would ever require access to data in the database. Really folks, you need to take the blinders off once in a while.
You have SQL Server installed on a box without a Windows OS?
Or are you just trolling because you don't have anything useful to add to the discussion? I ask because you say "take the blinders off" but do not offer anything that might help us understand whatever POV you seem to be coming from.
So please, enlighten us with a real world example if you have one. I really would like to see one since I've only ever seen SQL Server installed on a Windows system.
BTW, my Server has 4 different NON-Microsoft data source & destination systems that I have to integrate with, none of which actually require direct access to the server, so the SQL only Auth wouldn't help me.
November 20, 2009 at 5:25 am
BackupGuy (11/20/2009)
The responses so far are, typically, M$-centric...as if nothing exists outside of the AD domain where the SQL server resides that would ever require access to data in the database. Really folks, you need to take the blinders off once in a while.
You are joking aren't you?
November 20, 2009 at 5:56 am
Apparently, I've raised the hair on the back of a few necks this morning. Brandie, I apologize for being a bit flippant. You've obviously worked very hard adding a lot of Microsoft letters to your name and should be congratulated for your professionalism.
Two small points...I understand that this is a MSSQL forum. I'm the guy responsible for backing up the (sometimes messy) SQL databases that both internal developers and ISVs create...not always an easy thing. Reality is that there are several other SQL engines that run just fine on the Windows platform and don't necessarily require AD domain authentication, although, most support it.
Secondarily, I think this discussion is probably moot as I don't see Microsoft ever allowing access without authentication as it holds the potential for disrupting a revenue stream.
Have a great weekend.
November 20, 2009 at 6:50 am
Perhaps I'm missing the point here but what about a public facing commercial web site where OS authentication is not the preferred option? IIS authentication is set to Anonymous and asp.net authentication is Custom or Forms. I wouldn't want John Public able to access anything on my network and I wouldn't want to manage AD accounts for potentially thousands of users. Purchase/Product info is stored behind the firewall in a backend SQL Server database and uses SQL Authentication and is accessed/updated via parameterized stored procedures.
M
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply