November 20, 2009 at 7:06 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.
Typical anti-Microsoft mindless blather. Got sick of this junk on TechRepublic way back when.
- 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
November 20, 2009 at 7:10 am
BackupGuy (11/20/2009)
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.
Still unclear on what you mean here. If you have SQL Server configured with mixed mode authentication then non-Windows clients can connect using SQL credentials, as far as I know--it is not necessary for Windows authentication to take place. And what does Microsoft's revenue stream have to do with it?
As to the original question, so long as you're able to use Windows authentication, I think it's better to do so; it provides a much better audit trail, after all. The argument that domain admins shouldn't have full rights on the SQL databases is a bit of a side issue, IMHO, because a domain admin with malicious intent could stop the SQL services, delete the database files, and kill all your backups without requiring SQL access in any form--you just have to trust the people you have in that position of authority.
November 20, 2009 at 7:15 am
BackupGuy (11/20/2009)
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.
"Flippant" doesn't mean what you think it means. Deliberately and eggregiously insulting is not "flippant".
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.
You imply that non-MS databases are always unmessy. Ignorant, incompetent devs aren't limited to Microsoft platforms.
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.
While that's potentially true, please keep in mind that security through username/password per user (in the database, either per database or per server) is less secure than AD security. If I find that a DBA has created "backdoors" on the server in a pure AD environment, I can kill his access in a matter of seconds. If I find the same in one where SQL auth is used, I may very well not be able to kill access without recoding applications.
- 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
November 20, 2009 at 7:25 am
paul.knibbs (11/20/2009)
The argument that domain admins shouldn't have full rights on the SQL databases is a bit of a side issue, IMHO, because a domain admin with malicious intent could stop the SQL services, delete the database files, and kill all your backups without requiring SQL access in any form--you just have to trust the people you have in that position of authority.
I understand what you mean, but security isn't just about keeping out people you don't trust; it's about protection; protecting your data, yes, but also providing your users with protection. As an example, if a network admin doesn't have access to a database, (s)he can't be blamed if data gets leaked. Yes, you have to trust people to do the jobs they're hired to do, but a network administrator is not generally hired to manage SQL databases or SQL servers, so shouldn't be equipped with the SQL equivalent of a big red button labelled "do not press this", but trusted not to press it.
Our network admins aren't hampered in their work by their inability to access our Oracle databases, so why should it not be the same for SQL Server. No reason. That's why we do go through the process of amending the security defaults afforded to the Administrators group, but I just wish the default was far more limited in the first place.
Semper in excretia, suus solum profundum variat
November 20, 2009 at 8:00 am
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.
November 20, 2009 at 8:58 am
Removing Window Authentication is exactly the opposite of securing the instance. 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. 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! If the user is to be able to use wizards or ssis packages, they must also be in the new private role on the msdb. Only add users to the private role on databases where they are supposed to have access. You never place them in any predefined role other than the now empty public role and their new private role. You may create multiple private roles if groups of users have different levels of access, eg read-only, read-write, etc.
Further, at the windows level, you can create windows security groups for groups of users, defining the groups as the only users in the instance. Place the group user in the appropriate role(s). One of the groups could be a DBA group that has its group user placed in the appropriate sysadm role(s). Simply removing a user from a security group automatically removes them from db access as well, since they are not individually identified in the db instance. Any user or group-user placed in the sysadm role does not need to be, and should not be, made a member of any of the private roles. As sysadm they already have full access privileges.
Using the windows security group approach, each user will retain his individual identity in the logs under windows authentication. When using SQL Server authentication, once a password has been compromised, security is wide open. There are numerous password hacker tools out there that can be used to gain access. Simply speaking, SQL Server authentication is not very secure, although using very strong passwords can help a lot (15 digits, mixed uppercase, lower case, numbers and special characters, expiring regularly, and non-repeatable for at least 12 iterations.) Passwords, where absolutely necessary, must be enforced by a rigid password policy at the OS level.
November 20, 2009 at 10:05 am
I thought it was easy to remove Windows authentication. All that Windows authentication does is include the Windows Adminstrators group in the sysadmin server role. So all you need to is remove the Windows group from the sysadmin role and presto only SQL logins can be used.
(I might have misinderstood the original question in which case I apologize in advance!)
November 20, 2009 at 10:23 am
William Rayer (11/20/2009)
I thought it was easy to remove Windows authentication. All that Windows authentication does is include the Windows Adminstrators group in the sysadmin server role. So all you need to is remove the Windows group from the sysadmin role and presto only SQL logins can be used.(I might have misinderstood the original question in which case I apologize in advance!)
I'm thinking they want to enable public access in the DBs. So you connect with your windows credentials and while you may not have access explicitly granted you have whatever public access has been made available.
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.
-DW
November 20, 2009 at 10:25 am
Windows authentication is way more than that. You can specify a user as SQL Server authenticated and give them a unique ID and password just for that SQL Server instance, or you can just place their AD network ID in the instance as a user under Windows authentication. Under windows authentication, if they are listed in the db as a user, they do not have to enter a password, because they have already been authenticated by Windows.
November 20, 2009 at 10:27 am
I thought the original question was "how do I only allow SQL logins". If so this should be straightforward - remove the Windows groups and users from the sysadmin role. I tried actually doing that on a development system during my SQL Server 2000 training and it worked fine (eg you could only login using SQL authentication).
November 20, 2009 at 10:29 am
SQL Server is fully integrated with Windows for security purposes and as you know, only runs on a Windows system. You cannot remove Windows authentication without completely destroying the instance.
November 20, 2009 at 10:31 am
You can certainly define only SQL Server IDs, but not remove the capability to define Windows Authenticated users.
November 20, 2009 at 10:33 am
There are numerous password hacker tools out there that can be used to gain access. Simply speaking, SQL Server authentication is not very secure, although using very strong passwords can help a lot (15 digits, mixed uppercase, lower case, numbers and special characters, expiring regularly, and non-repeatable for at least 12 iterations.) Passwords, where absolutely necessary, must be enforced by a rigid password policy at the OS level.
What you have posted is crude security implementation that may not create a secure SQL Server but rather waste resources because SQL Server SSIS data automation with other RDBMS is not working and application users capacity is very limited. The OS uses ACL(access list) while SQL Server being RDBMS uses DCL(data control langauge) to make both to work with SQL authentication use the context account which defines what the account can do in SQL Server. Changing user role in the master is not needed in banks that work with Microsoft the DBAs in the data team still need a manager or managers signatures to edit the master.
That also tells me DOD is wasting resources without low level security implementation experts which also explains why Microsoft which paid for most security you need decided not the provide security for their Cloud customers because it takes a different set of skills to implement and understand security boundaries. The boundaries is where AD context ends and SQL Server is in control or an application runtime like Asp.net is in control. This goes to the question how many Windows system admins know how the Windows permissions are propagated and the issues with the component that manages the ACLs in a Windows domain.
I don't think Oracle security is better it is not in the hands of people who don't know anything about RDBMS data control language.
Kind regards,
Gift Peddie
November 20, 2009 at 10:45 am
DoD is not wasting anything here. I did not go into all steps to secure, only those that are easy. By the way, when creating a new login in SQL Server, if you check the enforce pasword expiry and/or policy, both SQL Server internal security rules/policy and Windows security rules/policies are enforced. If you don't believ e me, set a Windows password policy to expire a password after 24 hrs and set a SQL Server login password to not expire. Set the login to enforce password policy and after 24 hrs, your SQL Server login ID will have an expired password. I know this because it has happened to me numerous times, where the SQs have set different policy from the DBAs.
Placing a private role on the master db to carry the permissions normally carried by the public role is absolutely necessary to a secure instance. SQL Server is a very insecure environment as installed originally. There are a host of permissions granted to the public role (in which everyone who connects is a member) to do all kinds of highly undesirable things, like modify the registry entries for SQL Server. If you do not restrict these permissions to a private role, anyone who hacks in will get those permissions by default.
Finally, the most basic security built into SQL Server is inherited from Windows and has NOTHING to do with DCL.
November 20, 2009 at 10:55 am
DoD is not wasting anything here. I did not go into all steps to secure, only those that are easy. By the way, when creating a new login in SQL Server, if you check the enforce pasword expiry and/or policy, both SQL Server internal security rules/policy and Windows security rules/policies are enforced. If you don't believ e me, set a Windows password policy to expire a password after 24 hrs and set a SQL Server login password to not expire
That just shows that is ACL implementation in SQL Server going back to implementing ACL over DCL which is what is in the none standardized BI stack as SSAS. DOD is wasting money because of fear from 9/11 without low level implementation experts because most of security experts are security admin experts who wants automation. In the real world there is very little that can be automated.
Finally, the most basic security built into SQL Server is inherited from Windows and has NOTHING to do with DCL.
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
Kind regards,
Gift Peddie
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply