July 26, 2009 at 4:53 am
I work in a retail chain with 21 branches across Africa. We use SQL server 2000 as the backend database to our retail applications. We've had a few incidents where data is modified without the appropriate authorization. In view of this I implemented passwords on each sql instance accross the outlets only to find that you can bypass this security feature by using windows authentication.
How do I prevent users from logging in to sql server 2000/2005 using the windows authentication and provide access only to users who have the SA password?
July 26, 2009 at 5:14 am
Only those whose windows logins have been granted access to the SQL instance can log in using windows authentication. Make sure no windows accounts have been added and that no one unauthorised is a member of the local administrators group on the SQL Server.
Also, no one should be using the sa password. Make sure that the sa password is unknown (you can disable it on SQL 2005) and give the administrators personalised accounts (preferably windows authentication as it's harder to log in as someone else)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2009 at 5:18 pm
I believe you can remove all windows groups/logins from the server, and this would prevent any windows user from by passing the logins. Also, I too must STRONGLY advise against anybody but your DBA from having the SA account. As a matter of fact, in the organization that I work for, the sa account has a password that is crazy strong and is NEVER used. We create an SA2 account for use.
CEWII
July 26, 2009 at 5:38 pm
I believe you can remove all windows groups/logins from the server
I would not remove the local Windows group, especially Administrators (local group), from SQL Server unless you have a sysadmin account setup within SQL. If you don't you will have a real hard time getting into the SQL instance' :-D.
If your application requires sql logins there are scripts out there that can randomly pick some big long password you can set your SQL server sa account to. This will appease the auditor for PCI DSS. I have not been able to find the script a speaker did at the SQL Saturday event in Birmingham, AL but I don't think the download site is still available. I have the file on another computer I can upload if you need it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 26, 2009 at 7:11 pm
Melton (7/26/2009)
I would not remove the local Windows group, especially Administrators (local group), from SQL Server unless you have a sysadmin account setup within SQL. If you don't you will have a real hard time getting into the SQL instance' :-D.
Ok, I guess I assumed that they would have a user with sysadmin.. But this is about the only way to have only SQL logins accepted. But I still recommend removing ALL windows groups once they do. Then only SQL Logins will have access.
Coincidently, in SQL 2008 only users and groups that are added to SQL explicitly have access to SQL, so unlike 2000 and 2005 members of the administrators group does not have sysadmin access unless you set it up..
CEWII
July 26, 2009 at 7:52 pm
This is not really about whether the users connect using Windows or SQL Server authentication (although I'd support Microsoft's recommended best pracrtice in using Windows authentication only if possible) but rather what a user has rights to do after they've connected.
There's only 2 accounts/groups that must have SysAdmin rights:
1. The service account under which the SQL Server service is running (easiest if SQLAgent uses the same account). If this is a domain account you can remove the BUILTIN\Administrators account within SQL Server, and if that service account is denied "Log on Locally" then it's very difficult for anyone to use that account to get into the databases.
2. The sa account.
Of course, your DBAs will need SysAdmin rights if anything is to change, or to be able to provide support. They shouldn't be using the sa account (noone should be using that account), but rather a separate account/group.
After that, each account/group should be given only the access that is needed to perform the function they do. Your application accounts will need read, write and/or execute on the relevant application database(s) depending on how each application works. The level of access that your support staff have needs to be determined, but generally that should be no more than read, with some sort of change management process if they require elevated rights to perform changes.
Removing the rights of the BUILTIN\Administrators group is a very quick way of eliminating the risks associated with "pseudo-DBAs", but it can only be done if you are using a domain account for the SQL Server services.
July 26, 2009 at 8:12 pm
Glenn Dorling (7/26/2009)
This is not really about whether the users connect using Windows or SQL Server authentication (although I'd support Microsoft's recommended best pracrtice in using Windows authentication only if possible) but rather what a user has rights to do after they've connected.SNIP
Removing the rights of the BUILTIN\Administrators group is a very quick way of eliminating the risks associated with "pseudo-DBAs", but it can only be done if you are using a domain account for the SQL Server services.
Glenn,
I generally agree, windows authentication is the best, if available. The removal of groups/users that have sysadmin rights needs to be understood and mitigated. The user that is used for the SQL Server and SQL Agent needs high level rights, and is usually a sysadmin. Besides that only DBA's generally need sysadmin.
Short answer, understand the groups that have sysadmin and who is in them, once you know that you can add/remove users/groups and have a grasp of who has what rights.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply