Configuring Windows Firewall on 2003 Server
-
I need to allow developers full access to SQL Server and all of its' facilities (reporting services, integration services, notification, etc.) but I still want to run the firewall to keep them from other stuff on the box. Most new features and those being phased out are off in the SAC utility, and if developers need something they will have to ask; but I cannot just block their access to everything. Turning on the https port gets them the reportserver and report manager; but that is not enough for everything else. Turning on the ports (1433 and 1434, or 433 and 434) should not do anything more than turning on the secure server option.
In order to get it out there I have added the following programs to the exception list:
- sqlservr.exe
- msmdsrv.exe
- ReportingServicesService.exe
- sqlbrowser
- msfteqsl
- msdtc.exe
- MsDtsSrvr.exe
- SQLAGENT90
- wmiprvse.exe
- w3wp.exe
But that first seems like overkill, and then incomplete. These services (or executables) are run by the network service account and appear in the task list as owned by that account; but there are two instances of svchost owned by the network service account - and five or six other owned by system or local service, and you cannot easily identify what they are running. There is no easy way to just add the two that are owned by the network service account and not the others - or any indication as to whether they are needed.
I would like to gradually crack down on the exceptions as developers use the box; but must not keep them from using what they need.
Any suggestions? Anybody out there have similar requirements and elected not to take the easy route and just disable the firewall?
Thanks in advance.
- sqlservr.exe
-
This was removed by the editor as SPAM
-
Well I like to secure SQL as much as the next guy, but I don't think you are approaching this the right way.
First off, if this is a live server (from your post I would think so) they should not have any access.
Development should happen elsewhere, and a second party (DBAs) should deploy their changes (subject to change control).
If, however, they do need access, you should be using permissions to restrict them, not firewall rules. SQL 2005 allows you to be extremely granular with securables, allow them access they need - why worry about how they do it?
-
Thanks for your response.
This is a "development" server... only "live" in R&D for developers.
The server was built to provide metadata for reports, test data for application development, and reporting and web services. No application code gets deployed here. We are using Sybase Direct Connect to replicate directly into the consolidated copy of the operational DB, and SSIS to do the ETL into the reporting database (star schema) and Application Services DB (aspnet_regsql).
I have pretty definite constraints as to what they may and may not do on the server, and there are probably reasons that I do not understand that I am being asked to configure this way; but I understand my bosses directive.
We are currently using Window Authentication and I would like to avoide mixed-mode; although I realise that on 2003 Server with SS 2005 that APIs to the OS allow all of the same restrictions as the OS.
This seems to me to be making work, for the lifetime of the product, for the DBA. In the real world, after a product is deployed, there is no DBA; but there will always be system admins. If Active Directory (AD) is already doing the work, then I would like to let AD continue to do the work and not make work for me.
-
Okay, just to define terms, 'Live' means an integral part of a live service, 'Development' is seperate and has no direct relation to the live servers.
"The server was built to provide metadata for reports, test data for application development, and reporting and web services."
This seems to be used for Live reporting, and Devlopment; so I'd split the functions between two servers.
"We are currently using Window Authentication and I would like to avoide mixed-mode"
I totally agree, SQL Logins remain a tool for when Windows Auth is not viable.
You're looking at blocking apps which control services, if you don't grant them permissions to the services, they won't be able to control them whatever app connects.
-
Symantecs. But that's okay as long as we're on the same page. In my world we have development, integration, test, QA and production servers (and any number of others, as required, e.g., application, web, database, etc. - but those defintions are in a different context). Any of them can be live at any specified time in the life cycle. The development server did not go live until it was setup and configured, etc. But, as long as we understand each other, I am not so concerned about the terms used - its' content I am looking for
The system in question is purely there for development purposes. There are many issues to be resolved between now and deployment. By your use of the word live, this server is not used for "live reporting". It is being used for the development of reports. All 400 existing reports are being migrated from their source hosts to the relational store in SQL replicated through Sybase Direct Connect. ETL is being accomplished on a subset of data - on elements that are not considered operational (and therefore, more appropriate to remain on an operational store) to move "reporting" data into a star schema. Reports are currently in a mix of Crystal and Hyperion Brio. This subset (those not considered "operational") will be re-written in SSRS.
Anyway, the point is, there are a lot of people with their hands in the "development" and they need access to facilities, services, and data. And there is a ton of data...
I am still struggling with how to give them access. We have built a trusted environment with SSL certificate on IIS (not on SQL though, as that will be part of the client's security realm), and I have only been able to connect from hosts in the trusted domain. non-domain machines remain an issue. I am looking into encryption of passwords through web.config, and if no clear-text passwords have to be passed, perhaps I will revert to mixed-mode authentication and allow SQL logins. The firewall allows me to specify IPs to let through, so I have some amount of flexibility there, if required. In the end it may take a mix of measures to provide the security I am trying to achieve.
If I cannot provide the means for the various people to connect, at some point, I will have to "listen" to those who are trying to tell me how to do my job. My boss fully supports what I am doing (not only supports; but provided the initial direction); but people who are on non-domain machines (even though the corporation has a written policy that says machines will be in the domain) are developing using their own database, data and services. When a push comes to a shove and they are forced to integrate... to build out of PerForce to an integrated environment, and it doesn't work... in a panic, rules will get relaxed, and the product security could be flawed - unless the "proper" solution can be provided at that time.
I really do not want to be a DBA in the traditional sense of the word. I have been in the business since 1966, and have been a DBA since 1982. I have other interests. But, likewise, I am trying not to create a situation in a product that will require DBA maintenance/administration over the 15 year life cycle of a product to be delivered. When products get in the field, maintainability becomes a big issue. There is always a "tech" in-house to support the product; but (s)he is not necessarily a DBA... smart enough to understand instructions and learn new things, yes; but if it can be designed not to require it, all the better.
If AD can be made to do what we need to do, well, that seems a lot easier to take care of; but the access has to be provided, and restricted, as required.
In the beginning that, ease of use, was what SQL Server was all about - there was the management perception that "a monkey can do it"... when the reality was, and today is more so, that it takes the same time, effort, skill and money to build a system in SQL Server as it does in, say, Oracle. There are all the same components regardless.
But the point remains that the software is getting better and doing more, and the skills of the DBA are not always as critical as they used to be... and more system admins will be doing database administration as the years go on. Please do not intrepret this as a slam against the skills of DBAs... there are some brilliant ones on this site deserving of awe and respect.
I remember the first network installed in an east coast government environment was sold by the county supervisors because the users would "take care of it..." and the first Oracle application I saw in the federal government would "allow the secretaries to build accounting systems..."
Both obviously erronious; but, still, things must be simplified, and I believe that keeping account administration out of the database and in Active Directory is an important piece to achieving this goal.
-
"By your use of the word live, this server is not used for "live reporting". It is being used for the development of reports"
Normally I wouldn't bother much with limiting dev access to dev servers; at the end of the day they'll only fsck themselves over if they screw up the server. As long as they're happy with a restore from backup, they should be okay.
Data security shouldn't be a problem, as long as they work with dummy data, or data of such an age that it's not considered relevant anymore.
However, I wouldn't grant OS permissions, such as local admin. Although a dev system, any system on the network has to conform to some standards, and I would continue to let the appropriate server support team control over the OS.
"I am still struggling with how to give them access. We have built a trusted environment with SSL certificate on IIS (not on SQL though, as that will be part of the client's security realm), and I have only been able to connect from hosts in the trusted domain. non-domain machines remain an issue"
Okay, as before, once you get into the realm of firewalls, you are more or less forced to allow SQL Logins, although in 2005 this isn't quite the vulnerability it used to be: enable policy on SQL Logins - transmission is now 512bit encrypted, rather than XORed. If it's going over the net, I would suggest a firewall to firewall VPN with your other networks.
Allowing windows auth traffic through firewalls is a complete no-no. SSL and IPSEC are good, but add a layer of complexity to the app. If you can handle it with a site to site VPN you're making support easier.
Regarding DBAs, I actually think they will have a lot more detailed work to do with SQL 2005 - the line between dev DBA and prod DBA is being blurred which is a good thing for them, but as a security consultant I feel a migraine coming on!
All in all, there seems to be quite a lot in the big picture of your problem, but feel free to post more!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply