November 20, 2009 at 11:44 am
mzak (11/20/2009)
I'm not sure if anyone else has played around with the extensibility model provided with IIS 7 but it is really cool and could be a method MSFT could use to implement the ability to control authentication and authorization within the SQL Server environment.That being said, I believe that is a very scary thing to be "mucking" around with.
I was a little bit hesitant in using it in IIS 7 on one of our deployments but doing so allowed my web site and ftp site to use the same membership store which makes it easier for our users because they only have 1 username/password to remember for both services. It also makes it easier for us to administer.
It is not IIS 7 dependent just comes pre defined in IIS7 and IIS7.5 it is framework 3.5 dependent it can be manually added into the Web.Config in IIS6 and IIS 5.5 but not IIS5 because the feature is .NET 3.5 which cannot be installed in Window 2000. The feature is called client application services which means one Web.Config is used to resolve more than one application security.
http://msdn.microsoft.com/en-us/library/bb546195(VS.100).aspx
http://aspalliance.com/1597_Client_Application_Services__Part_3.all
Kind regards,
Gift Peddie
November 20, 2009 at 11:49 am
BackupGuy (11/20/2009)
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.
I won't disagree with that. But the ISVs apparently were discussing SQL Server because they are trying to make revenue off of Microsoft's stuff.
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.
Actually, decisions of the CEO aside, I think the biggest "NO!" will come from the actual developers of the product itself. Looking at it from a peon POV, there's a lot of code here that they would have to hack & slash through (and possibly rebuild from scratch) to get a SQL Auth Only security feature in the system.
That's how things tend to work in the places that I've been in, anyway.
Not sure how this feature would disrupt the revenue stream precisely, but I'm not an business person nor have I ever worked for Microsoft directly. So I don't know what's in their heads for revenue streams.
November 20, 2009 at 11:54 am
Darren Wallace (11/20/2009)
I think you could effectively turn off Windows Authentication by explicitly denying BUILTIN\Everyone but I haven't tried it as I don't have a server handy that I want to bork. I'm sure there would be implications with the SQL Server Agent and other bits and pieces that would stop working, but it sounds like this may be an acceptable option.
Last time I checked (and I might be wrong about this, but it works this way on shares), if you explictly DENY access to the Everyone account, you literally deny it to everyone. Everyone is the "parent" to every other single login account whether you see it like that or not.
Deny trumps all. So denying Everyone = Bad Idea.
If you decide to test this, make sure it's on a sandbox server and not your production box. Otherwise you may be proofreading your resume in short order.
November 20, 2009 at 12:02 pm
Irish Flyer (11/20/2009)
DoD only allows SQL Server authentication for those cases where it is mandatory, such as service accounts (the accounts under which you run the SQL Server Services). The way you can easily lock down SQL Server is to script out the settings for all public permissions (securables) on the master, msdb and user databases.
I disagree that this is easy or more secure. Using AD accounts for Service Accounts is actually much more secure and reliable for inter-office functionality AFAIK.
Irish Flyer (11/20/2009)
Remove all securables from the public role on every db, including master and msdb. Create a private role for each db in the instance placing the appropriate public permissions you previously scripted on those roles. Assign your user accounts to those roles. All valid users other than sysadms must be in the new private role on master, or they cannot do ANYTHING!
Ouch. You must have quite the maintenance headaches for doing this. There are so many ways this can go wrong.
EDIT (I misread your post initially): Actually, you can assign Window groups to private roles a lot easier than individual User accounts. Then all you have to do is add or remove users from the group as they come and go with a minimum of hassle and still maintain security.
Glad I don't work where you do. I'd be tearing my hair out by the roots if I had to administer that nightmare.
November 20, 2009 at 12:16 pm
Unless you go to a situation where Windows services like SQL Server Agent can use SQL Server-based logins, Windows authentication is going to be required. Sorry, that's the way it is. What do you gain if you go to SQL Server only authentication, BTW? And if your auditors and security folks think that by taking rights away from your local server administrators you've successfully prevented them from accessing your SQL Server, they're wrong.
First, in 2005 and 2008 there's this wonderful backdoor called single-user mode. If I'm a local admin on the box and I start SQL Server up in single-user mode, SQL Server treats my login as if it were a sysadmin user. The reason being that folks removed all access and effectively locked themselves out of their SQL Servers. Which meant a recovery process. Now, you just stop SQL Server, restart it, connect, and repair your security so your DBAs can get back in. Which means this can also be abused to gain access...
Second, they have debug programs rights. Meaning if you slip up in just one place, they can see the username/passwords in memory. If Microsoft can commit this mistake (and it got some press), so can other software companies. So that means they need to target the right system, run a debugger, and they're in.
Third, if you're not with Enterprise Edition (2008) and TDE, then an admin need only stop your SQL Server (like during patching), copy your database files, attach them to his or her own personal SQL Server, and they have the data. BTW, consider backups that aren't encrypted by SQL Server (TDE) or by a 3rd party backup product capable of encryption, they can get those and restore them.
Those are the most common attack vectors. There are others with the current version, like seizing the SQL Server service account and password if you're not on Windows Server 2008 using a free tool which does a DLL injection attack. So all of these types of things would have to be worked on in the next version if you went to a SQL Server only authentication mode.
K. Brian Kelley
@kbriankelley
November 20, 2009 at 1:06 pm
I can see why SQL Server itself will always require Windows Authentication. Whay I think people are actually looking for is SQL Server only authentication from the client to database server. With Windows Authentication to SQL Server, every user has to have a CAL for network access/authentication (AD) and CAL for access to SQL Server. If you allowed applications to use SQL Server authentication, then only a CAL for SQL Server is required.
November 20, 2009 at 1:08 pm
You want to explain why there are no roles in Windows and there is roles in SQL Server from the time I have been using it which is 7.0 and up
[/quote]
Exactly what do you think the windows security groups are?? (Administrator, Superuser, User, etc.) These are roles with members. If you are in the Administrator group, you have the privileges of an administrator, etc. The roles carry the privileges, not the users.
November 20, 2009 at 1:13 pm
Irish Flyer (11/20/2009)
You want to explain why there are no roles in Windows and there is roles in SQL Server from the time I have been using it which is 7.0 and upExactly what do you think the windows security groups are?? (Administrator, Superuser, User, etc.) These are roles with members. If you are in the Administrator group, you have the privileges of an administrator, etc. The roles carry the privileges, not the users.
That is an insult to the late Jim Grey whose work built SQL Server, helped standardize ANSI SQL before that and added Security because the customers needed it. These are some of the reasons I have kept his last public interview in a safe place.
Kind regards,
Gift Peddie
November 20, 2009 at 1:22 pm
Irish Flyer (11/20/2009)
You want to explain why there are no roles in Windows and there is roles in SQL Server from the time I have been using it which is 7.0 and up
Exactly what do you think the windows security groups are?? (Administrator, Superuser, User, etc.) These are roles with members. If you are in the Administrator group, you have the privileges of an administrator, etc. The roles carry the privileges, not the users.[/quote]
Which is why using Windows Authentication for internal access makes so much sense. You create the security groups you need inside SQL Server in AD, assign those groups the appropriate rights in SQL Server. As people come, go, change positions, you only have to make appropriate changes in one place.
The real problem starts showing up when you need to give access to potentially the world when your database starts supporting an Internet facing web-site/service. You don't want to have license everyone.
November 20, 2009 at 1:27 pm
That is exactly why you never let the public internet crowd access a DB server. The DB server remains inside the firewall while the web server resides in the DMZ and buffers the access through the firewall, limiting it to a single port and application account.
November 20, 2009 at 1:29 pm
The real problem starts showing up when you need to give access to potentially the world when your database starts supporting an Internet facing web-site/service. You don't want to have license everyone.
That is the reason Asp.net and SharePoint AD authentication uses either the standardized LDAP or IIS and AD comes later to resolve the permissions long after the users have finished with SQL Server.
Kind regards,
Gift Peddie
November 20, 2009 at 2:03 pm
I noticed a post from an ISV awhile back asking if there was a way to remove Windows authentication. Most of us know that we can't remove it, and it concerned me a bit that an ISV didn't know this. The poster wanted to know if we could make the database more secure, potentially limiting the ability of a client to muck around with the database.
Whether you think that' s a good idea or not, I wonder if there is value in changing the security model for SQL Server.
It does not make the application more secure but it allows the application to scale because more than 2000 users in a Windows authentication Http application is pushing it. I think most application level security is there companies just don't pay for it. I have an idea of how much Cloud security will cost per implementation because most Cloud is used with other Enterprise applications but nobody knows future Cloud revenues.
Kind regards,
Gift Peddie
November 21, 2009 at 4:38 pm
Two points:
One: The initial question was to keep ISV customers from mucking up the database and then blaming the ISV for the problems. In this case we are not talking about the problem agents having inappropriate read access to the data. So worries about getting copies during maintenance or single user start up don't apply here.
Two: When you install SQL Server 2008, you have to specify the Widows accounts that you want to have initial administrator access. If you specify none, then you have no Windows authentication, even though the server happens to be configured for Windows authentication. I think that should cover the scenario on which this discussion is based.
In our company we use a mixture of Windows and SQL authentication/authorization for our applications. When the application first starts, it looks at the current Windows user name. Then, using an encrypted SQL username/password pair, the database security class looks up what mode, if any, the current user is authorized for in the application. Then the application looks up a run-time encrypted SQL username/password pair specific to that mode in that application for use by the main application code. That way, we ensure the right people are using the applications, and they do not have any backend access to the databases. For databases where the requirements include backend access, we set up a set of objects designed for end-user access and do allow network access for those. We do have a relatively fine-grained set of AD groups for our users to control such access.
Sincerely,
Daniel
November 23, 2009 at 5:28 am
danjam (11/21/2009)
When you install SQL Server 2008, you have to specify the Widows accounts that you want to have initial administrator access. If you specify none, then you have no Windows authentication, even though the server happens to be configured for Windows authentication. I think that should cover the scenario on which this discussion is based.
I'm not sure that's quite true. When you install SQL 2000 & 2005, you have to choose which account you want the initial administrator to use also. You get a choice of BuiltIn\Network or a Windows Account. So, but by chosing the former, you don't get Windows access either, but the former leaves so many security holes on the local server that it's not even funny.
So how is what 2008 (which I admittedly know little about) different in this respect?
November 23, 2009 at 9:02 pm
For the most part I'm a big fan of integrated (Windows) auth, but I have worked on a site where one of the network guys fancied himself as a dba (with no knowledge or experience) and a large part of keeping my databases up and performing properly was trying to keep this clown out. SQL only auth would have helped a lot in that case...
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply