October 3, 2008 at 7:30 am
I've started a new position, and now that I've got the backups straightened out, I need to tackle security. We have a mix of 2000 and 2005 environments, and security is pretty lax.
There are applications that connect using 'sa'. I don't know what type of connection string they use.
There are several developers with sysadmin rights, who create databases as needed. They need databases frequently for individual clients that we take on work for. Later they will get archived & removed.
There are no standards or audit trails for pushing code to production and making DB changes.
Practically all logins are created individually on each SQL server (mostly windows accts, a few SQL logins), but virtually no AD Groups for SQL.
So, I think I know where I'd like to be, but wonderinging if anyone has any suggestions about what should be accomplished, and how to do it carefully without breaking any existing applications, or stepping on toes too much. TIA
October 6, 2008 at 8:52 am
Hi
Is that any chance that you are working on the same company as mine :D. First don't panic, this is not a day's job, it takes several weeks to completed it. I have followed the steps to achieve what u mentioned.
I am assuming that you are in the process of migration to 2005
* speak to developers, don't take any rights from them or from applicaitons
* create a usename for application and ask them to change it, initially give admin rights to the username that you are creating,
* ASk developers to change the logins.
* Once completed run a trace per application and then check what the exact process that is going on with the application. once you are clear then grant exact rights to that login used for that application.
For users it is more difficult to implement
* Change their login first and ask them to use the new ones
* once they started to use the new ones, say to them that you cant create a new table or do administering tasks with this logins, but don't deny rights, give them the administrator rights
* Check for table modification and creation for that login for a week.
* Then deny rights, make them to ask you for any table creation and modification
This is Quite a patience process, but will yeilds results
Hope this helps
Cheers
🙂
October 6, 2008 at 9:24 am
First thing you need to to get management's buy-in for the tightening of security. The people who currently have privileged access aren't going to want to give it up, and they will complain if it gets taken away. You need to be sure that management will side with you
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
October 6, 2008 at 9:44 am
I agree with what has already been said. One thing I'd like to know is if there are separate production and development servers? If there are, you can leave the devs with admin rights on the dev server(s) while restricting rights in production. You can also make the application changes in dev and go through a test process before making the changes in production. If there are not separate environments that is the first thing I would work on getting.
As Gail said, management buy-in for anything you want to do is crucial. So the first step would be documenting what you have found, what you believe needs to be and why, then present that to management.
I don't know what data you are storing, but the 'sa' access by applications is scary.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 6, 2008 at 10:39 am
Thanks for the replies. My manager has made it pretty clear that he wants me to straighten things out (security, backups, performance...), and I think he's let the developers know.
We don't have developer servers. instead we have dev, test & prod databases on the same server.
We handle some credit card data that's encrypted, so we need to get PCI compliant, and obviously having 'sa' in use is not goof for that !
We also have linked servers using sa, so I'm wondering the best way to fix that hole.
October 6, 2008 at 12:11 pm
Less on the security and more on the performance, see if you can secure a separate server for dev/test. Letting developers develop on the prod box is asking for an accidental runaway query in dev to bring down the production system
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
October 6, 2008 at 12:38 pm
GilaMonster (10/6/2008)
Less on the security and more on the performance, see if you can secure a separate server for dev/test. Letting developers develop on the prod box is asking for an accidental runaway query in dev to bring down the production system
Yes .... I'm a few steps backwards compared to my last position ... !
I've instituted backups of all databases, not just some ... what a concept. But that was easy .... changing the security model without causing problems makes me move a bit more slowly, and it's not as obvious what to do.
October 6, 2008 at 12:43 pm
homebrew01 (10/6/2008)
Yes .... I'm a few steps backwards compared to my last position ... !
Look at it as an opportunity. 😉
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
October 6, 2008 at 12:54 pm
True ... I like fixing things, so it's a good challenge.
October 7, 2008 at 8:20 am
Oh Boy. PCI. Ok to meet PCI compliance, or at least get started on it see if management will agree to separate dev and test systems or a dev/test system. Production should be on it's own. Also PCI prefers Windows accounts over SQL accounts. Start by laying out the plans for Service accounts that will run processes and on a test enviornment test with least access rights. Never change production until all testing of a new account is done.
go to this site and review SQL Server 2005 Security best practices.
http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx
Normally you cannot do everything that is recommended there, but you can start moving closer to tighter security. What is nice is this will have your "back" should the developers squeak and they will. Good luck
October 7, 2008 at 8:32 am
I completely agree with splitting prod from dev/test. We're building out a new data center soon, and probably going to use VM for the servers. The 2 prod SQL servers will be on their own VM boxes, and this may be a good opportunity to split the dev/test databases onto another box. I'm not sure how much reconfiguring will have to happen at the application level for the apps to point to a different machine.
October 7, 2008 at 9:10 am
homebrew01 (10/7/2008)
I completely agree with splitting prod from dev/test. We're building out a new data center soon, and probably going to use VM for the servers. The 2 prod SQL servers will be on their own VM boxes, and this may be a good opportunity to split the dev/test databases onto another box. I'm not sure how much reconfiguring will have to happen at the application level for the apps to point to a different machine.
If you're moving to a new data center, new hardware etc., more than likely the server names will change anyhow... this would be a good time to implement DNS names for the application connections instead of using the actual server name, that way they can be changed at will whenever you need to. This can also help with disaster recovery and such.
-Luke.
October 7, 2008 at 2:02 pm
homebrew01 (10/3/2008)
There are applications that connect using 'sa'.
no application user account should need to connect using 'sa', especially a web user account. Assigning 'sa', this is all too common unfortunately
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply