September 21, 2010 at 1:55 pm
The SQL Server DBA group at my company is telling me that they want to remove all the sample databases from my servers (production and development) because they think they are a security risk. Is there any truth to this? It just sounds like yet another centralized IT policy designed to make my job harder. Some new policy they dreamed up.
I understand that I probably don't need the sample databases on a production server, although my databases and apps are small and the servers are not anywhere near capacity. But the sample dbs are very helpful in a dev environment, especially for learning (all the books refer to examples based on Northwind). I have a small group, and we have to do a little of everything, with no SQL experts to fall back on.
Just wanted a few opinions from the security gurus. Thanks.
Steve Brokaw
September 21, 2010 at 3:13 pm
I'm with you here. There's no point to have them on prod servers but there's definitely a possible need for them on dev server (especially if you're learning BI).
As for security risk, it's the server you need to lock down, not those databases.
If the logins you give out are SA, then there's nothing extra dangerous in having those dbs around as the user is already God.
I can't think of anything a user could do with those db that he couldn't do with some other DB with security issues. I'd just make sure than only dev. have access to those dbs and that should do it.
You could also propose to host them on a single dev machine where all devs have access rather than having them spread around 12s of times... and then make sure nothing else is hosted there so that takes cares of any "security issues".
September 21, 2010 at 3:21 pm
Sounds like someone else read an article and freaked. The question is, is this a hill worth fighting for? I'm dealing with that a bit myself at the moment. "SSIS isn't allowed to look at, or even open, a windows file browser for file names". Errr, oooookay.
I agree with Ninja above. It's probably easier just to get them to put up a shared instance somewhere with these databases on it as a sandbox for your dev teams and just not give it an external connection (websites, etc). That may be a reasonable compromise for them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 3:25 pm
If they won't give you a dev server with these databases you can always use sql express on your local machine. If that is locked down you can always do it at home if the goal is to advance your own knowledge, call it "Prep work" for your next job. 😉
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
September 21, 2010 at 3:46 pm
Thanks for the replies. All good points. I will likely push a little bit for leaving it on one dev machine, and making sure the access is limited.
Steve Brokaw
September 21, 2010 at 4:00 pm
Ask them to explain exactly how those databases are a security risk.
They may just be taking some old-wives tale at face value.
I think I heard the same thing from our security team, and all I got was that it was a best practice, but they were never able to give me an actual reason.
September 22, 2010 at 9:34 am
More info from my SQL DBA group: They claim that the guest account has access to the sample databases, and that can't be taken away. This gives anyone an entry into the instance. From there, they can link to other databases (can't remember the exact term he used for this.) Not sure about this. Seems that you could remove or restrict guest from the regular databases.
Anyway, it appears that they will let me keep the sample databases on one dev server (for now). How nice of them.
Their next new policy is to try to eliminate all SQL (non-domain) logins. Yet they won't give me a windows domain login with a non-expiring password to use for running applications. I was successful in saying NO to them for this, after providing some brief documentation on it. I'm sure there are many aspects and opinions on these logins, don't really want to go there and it's off topic. Again, my applications and databases are fairly simple and resources are limited.
Guess I could get all this done if I quit complaining here and got back to work... 😀
Steve Brokaw
September 22, 2010 at 9:50 am
Steve Brokaw (9/22/2010)
More info from my SQL DBA group: They claim that the guest account has access to the sample databases, and that can't be taken away. This gives anyone an entry into the instance. From there, they can link to other databases (can't remember the exact term he used for this.) Not sure about this. Seems that you could remove or restrict guest from the regular databases.Anyway, it appears that they will let me keep the sample databases on one dev server (for now). How nice of them.
Their next new policy is to try to eliminate all SQL (non-domain) logins. Yet they won't give me a windows domain login with a non-expiring password to use for running applications. I was successful in saying NO to them for this, after providing some brief documentation on it. I'm sure there are many aspects and opinions on these logins, don't really want to go there and it's off topic. Again, my applications and databases are fairly simple and resources are limited.
Guess I could get all this done if I quit complaining here and got back to work... 😀
Where the heck did they read that?
If the user cannot access a prod DB, it doesn't matter from where he tries to access it, he won't have access.
Try this out. Create login Jo, give access to northwind and deny to all else and then try to access other dbs with 3 part naming convention and you won't be able to.
The only exception to that is building a sp with "execute as dbo" option and then let them know it exists.... but then again it's not giving full control, it's giving access to one object!
September 22, 2010 at 3:27 pm
Steve Brokaw (9/22/2010)
More info from my SQL DBA group: They claim that the guest account has access to the sample databases, and that can't be taken away...
They certainly can remove guest access to those databases.
However, no one can access those databases, even with guest access enabled, who doesn't have a login on the server.
September 22, 2010 at 3:43 pm
Sounds like maybe the SOX compliance team has been in. These are exactly the type of carte blanche recommendations that are made.
If a reasoned argument can be provided as to why there should be a deviance to what the security manual says is best practice, that can be documented and everyone moves on. Just painful getting there.
edit: as a nasty DBA I would not want the sample databases on my prod servers
---------------------------------------------------------------------
September 22, 2010 at 3:54 pm
It's not a lot of fun to deal with someone who has a misconception but speaks with an air of authority. The sample databases are like any other databases. nothing special, you can restrict access the same way you'd restrict access to any database and to any database instance.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
September 22, 2010 at 4:20 pm
If the above post was aimed at me sample databases should not be present on a production server because people should not be testing\learning on production servers.
Dev server, play to your hearts content.
---------------------------------------------------------------------
September 22, 2010 at 4:34 pm
george sibbald (9/22/2010)
If the above post was aimed at me sample databases should not be present on a production server because people should not be testing\learning on production servers.Dev server, play to your hearts content.
Absolutely not aimed at you. More at the people at the OP's company who are telling the OP that these databases are a security risk because they allow the guest account to access them and cannot be removed.
I whole heartedly agree that these databases should only be on dev databases. With all the options of sql server dev edition, sql server express etc I cannot see a reason for them to be on a production server.
What I disagree with is the reason they gave that the databases shouldn't be there is for security purposes. The reason they shouldn't be there is because because they are test/play/sample/dev/sandbox databases. Production servers should strickly have production databases IMHO.
Edit: clarified my grammer... Also, I'm a nasty DBA that says pretty much the same thing George.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
September 22, 2010 at 4:59 pm
Trey, I apologise in that case. Thought I had to reply just in case there was a misunderstanding where I was coming from.
the dangers of written only communication.
---------------------------------------------------------------------
September 22, 2010 at 5:16 pm
I agree, dev server only. Dev sample databases should not be on a production box. If I find them on prod, I remove them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply