The Midlands PASS Chapter hosts:
SQL Server MVP John Welch
July 17, 2008 at Training Concepts
The Midlands PASS chapter will hold a special meeting on Thursday, July 17, 2008, to host SQL Server MVP John Welch. John will be giving a presentation on SQL Server Integration Services. The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the presentation between 6:30 and 6:45 PM. I will send out an agenda next week.
Please feel free to forward this to anyone who you think would be interested in attending. If you plan on attending, please RSVP as soon as possible so we can ensure we have enough space and food. If you have time to help with setup, please email me and we’ll plug you in!
Abstract:
One of the common issues encountered with SSIS is deploying configurations for multiple environments. During this session, 2 patterns for handling configurations in SSIS will be covered. a simple pattern that handles most cases, to a very flexible pattern that covers most complex scenarios. Learning and using these patterns will allow SSIS developers to more easily deploy packages between environments, and leverage a single point of configuration in each environment.
Bio:
John Welch is Chief Architect with Mariner, a consulting firm specializing in enterprise reporting & analytics, data warehousing and performance management solutions. John has been working with business intelligence and data warehousing technologies for 6 years, with a focus on Microsoft products in heterogeneous environments. He is a Microsoft Most Valued Professional (MVP), an award given due to his commitment to sharing his knowledge with the IT community. John is an experienced speaker, having given presentations at Professional Association for SQL Server (PASS) conferences, Software Development West (SD West), Software Management Conference (ASM/SM), and others. John has also been published in DM Review, SQL Server Professional, and XML Developer.
I saw a blog post by Robert Hensing talking about Microsoft's new GPS product and its very uncreative name. This is related to a conversation some co-workers and I were having today. We were trying to think of when Microsoft marketing had a really big success on the product front. Vista and Zune, which Robert talks about, aren't exactly catchy names. If you look at the original packaging for Zune, it was a real eye sore which didn't convey what a Zune was (brown and orange box colors didn't help). The robot for Windows Server 2008 is okay, but it doesn't make me want to go get a copy or even really hit a web page about it.
I know Apple has received a lot of press because their products have had great advertising campaigns. Think about the iPod campaign where you could see the folks dancing and they were listening to iPods and how they changed back and forth with the media types. But they chose upbeat music, folks who were able to convey having a good time, etc. That carried over to say, "Get an iPod and join in on the fun." Mazda has ZoomZoom. You get the idea.
Then I thought about the new SQL Server 2008 logo. It's nice, but it's not a homerun by any stretch. I think MVP Darren Gosbell has a better take with his kookaburra version of the logo.
Every so often I see a post in the forums where someone has stated they've used a Domain Admin level account to run the SQL Server service. The implications are that anyone who is a member of the sysadmin fixed server role is effectively a domain admin. That means if a developer is a member of this role within SQL Server, the developer can use SQL Server to execute with these rights. The same is the case of a SQL Server DBA in production. Typically DBAs don't have domain admin rights unless it's a small shop. But if SQL Server is configured to run under a service account that has such rights, the DBA effectively is as well. Not good.
In all but the rarest cases this is absolutely unnecessary. Truth be told, SQL Server doesn't even need administrative privileges over the server it's running on. Therefore, it certainly doesn't need Domain Admin rights. In security there is the Principle of Least Privilege. It's a simple concept: give only the rights needed to do the job and no more. This just doesn't apply to people, it also applies to service accounts. When it comes to SQL Server, this principle should be applied to the SQL Server service account just like any other.
How can you determine if your SQL Server is running under a Domain Admin account? First, determine what service account SQL Server is using. This can be done through SQL Server 2005 Configuration Manager (Figure 1) or SQL Server 2000 Enterprise Manager. You can also use the Services applet under Administrative Tools (Figure 2).
Figure 1:
Figure 2:
Once you know what the account is, check with your system or directory services administrator. If it's named [Domain]\Administrator, chances are likely that it is. If you have access to Active Directory Users and Computers, you can check the groups the service account is a member of (read access is all that's necessary and that's typically granted to all authenticated users in Active Directory). If you find the service account is a member of the Domain Admins group, do the research as to why. If there's a legitimate, unavoidable reason (and this should be extremely rare), seek to change the account immediately. This also applies to the SQL Server Agent service account.
Note: My snapshots are from a development laptop which isn't on a domain, hence the use of LocalSystem. Generally, though, LocalSystem isn't recommended and actually strongly suggested against. If this laptop had been on a domain, these accounts would be running under a local user account.
I recently purchased a Dell XPS M1530 laptop for use both for professional work (consulting & presentations) and ministry (mostly presentations). Yesterday, as I was at home recovering from a back injury, I noticed that some files were closing a little slower than I remembered. So naturally I checked the event logs and in the System event log I found:
The file system structure on the disk is corrupt and unusable. Please run the chkdsk utility on the volume C:.
I'm wary, as would be expected, when I see this kind of error. Usually it's the first sign of a hard drive going bad. But apparently others have noticed it on a Dell and suspect it has to do with hibernation. Perhaps that's the case, as I've been using hibernation a lot lately when originally I didn't at all. If it is hibernation, not a big deal, because the system boots up just about as fast from a complete shutdown as it does from hibernation, so I'll stick with that. In any case, I opened up a support call with Dell and the first representative and we went through the SFC /SCANNOW and he instructed me on what they wanted to see to run on the pre-boot assessment diagnostics (which, if you hold down the Fn key at the Dell screen, the system will enter into). Because I would have to restart the system, and because of the size of memory and HD space I have, he suspended the ticket and I went about to run the checks. Several hours later, no errors reported on any of the hardware. The guy was professional, focused on the issue at hand, and gave the types of tips and reminders I would have expected (make sure you backup your important data frequently, etc.).
So I go back into chat and understandably get a different person. I re-explain the issue and he says he understands and then asks if he can use DellConnect to connect and remote in. I agree and in the process of starting it up realize it's just Citrix GoToAssist with a Dell interface slapped on top. No biggie. So I watch what he's doing. He immediately goes in and starts with trying to change the PageFile and the DEP settings and then goes to a web site where he was starting to download a Spyware Cleaner. I break in and ask basically, "What are you doing and what does this have to do with the main problem?" He replies back, "I wanted to increase your system performance." My response was, "Look, it says I have file system corruption, that's what the call was about, not about performance issues. The system performs just fine. Can we focus on the main issue?" The reply I get back is, "Ok, well, you can do this..." which is basically to reboot, hit F8, and select repair your computer. After that he terminated the call. I'm hoping I get a survey to fill out because I'll report that the second guy didn't focus on the issue at all. What a completely different response!
As far as fixing the issue, with no more expectations from Dell, I went into repair your computer and ran the CHKDSK C: /F and it had a few extended attributes that were corrupt (those aren't handled by the transactional processes of NTFS) and there were a few files that were orphaned from any indexes. The chkdsk fixed those and I immediately ran another chkdsk to see if there were any more errors that cropped up. With nothing else being reported I rebooted. I've kept an eye on things and I've even run a chkdsk in read-only mode this morning with no more issues. I typically keep everything backed up to 1 or more USB drives anyway, so if the system were to go south, I'd only lose what I was immediately working on. That's just a good practice, as Kimberly Tripp has blogged about. You never know when an unexpected failure is going to happen. So plan that it will at some point and take the precautions to ensure your loss is minimized.
The folks at attrition.org have been stalwarts in providing information to the security community for ages, it seems. I first discovered them via their defacement mirror, which they ceased maintaining long ago because site defacements became so common there was no point. About three years ago they began actively tracking data loss reports. Earlier this year they started to do a sign-off like they did with the defacement mirror, but the response from the community was so overwhelmingly positive towards the contribution attrition.org had made, they decided to keep it going.
If you've not checked out the mailing list, and you have anything to do with data security, you may want to. In addition to maintaining a mailing list, they also maintain an open source database of data loss incidents. It's a .CSV file, meaning it's easy to work with. When you look at who has been hit and the numbers, it's not pretty, but it is good information to know. It puts in perspective that anyone can be hit and therefore none of us should be complacent.
Andy Warren points to a TechNet article about Security by Obscurity and wanted me to post some notes. Let's start with the example they used.
Rename the Administrator account:
I agree with Roger's take. We intentionally rename the administrator account because it does stop the malware and scripts. We intentionally rename the administrator account because it allows us to alert easier. We see a hit against administrator, and we know 99% of the time it's not legitimate. That allows our reaction to be quicker.
This doesn't mitigate the need for strong passwords. They still must be there. And the argument that a GPO applies the same administrator rename is only partially true. If you segment your systems in different OUs or you use WMI filtering or you security groups to determine which computers can apply a GPO, you can have multiple GPOs with multiple renames.
What Security By Obscurity Gets You:
Security by obscurity gets you an advantage against automated scripts. Security by obscurity gets you a time delay against an attacker intending to break in. Depending on how the system is obscured (for instance, if you move the HTTP port, the attacker must do a port scan first... which, depending ont he environment, may be detectable). Security by obscurity can get you early notice. For instance, if you don't use administrator anywhere and you start getting audit failures against administrator, you know one of three possibilities is true:
-
You have someone legitimately trying to log on but who doesn't know the right account, such as a new system administrator.
-
You have an application or system configured wrong
-
You have a legitimate attack
Any of those three you want to know about.
Why It Isn't Where We Should Stop:
But security by obscurity doesn't absolve you of responsibility for taking all appropriate security measures. For instance, if you rename the administrator account but you still have a weak password, the account is still weak from anyone who can browse the system. Therefore, you still secure the password.
Let's apply this to SQL Server.
-
Block UDP/1434. This is the SQL Server listener service port. If you can't access this port, you can't automatically determine the port for named instances.
-
Move SQL Server off of 1433 for default instances.
A script or the worms we've seen will not be able to get to your SQL Server. But assume you have a blank sa password (a complete reliance on security by obscurity). You've stopped the easy stuff. But then you've got that one internal guy who knows the sa password is blank. Even if he doesn't know the port on SQL Server, if he can access the server, he can try a port scan, such as with Nmap. Most of these tools (Nmap falls into this list) allow a very slow rate of fire, meaning they won't get picked up on alerting. Once he finds the port, he can get in. And if this server contains data that's worth some money, he can afford to wait until he finds the port. And then the guy gets in, gets the data, and likely you have no audit trail. Not good.
Therefore, certainly hide your systems; make 'em harder to find. But don't neglect the other aspects of security.
A few colleagues of mine, Andy Leonard, Jason Massie, and Chuck Boyce, are all on Twitter. As a matter of fact, Jason blogged on some folks he follows on Twitter with regards to SQL Server. I've also read Scoble talking about Twitter and decided to give it a look. Of course, I would pick a time to start when they are experiencing back-end issues. Maybe they should take Andy's advice.
Brian's Twitter Page
I have a CTP of SSRS 2008 deployed to one of my servers. Today I built a couple of reports and from one of my systems, they all tested fine. However, this system, because it is a testing server, doesn't receive the Group Policy Object (GPO) controlling IE security settings that our standard systems do. Now the IE GPO contains what's necessary to do Windows integrated authentication against our Intranet-based web servers, to include Kerberos delegation. But for whatever reason, the systems which receive the GPO kept prompting users to log in.
One of the issues with troubleshooting is the fact that SSRS 2008 is no longer a web application under IIS. As a result, the places to look for logs, for security settings, and the like are all different. One thing that isn't is that since Windows authentication is involved, the audit failures should still be logged on against the Security event log for the OS. They were. I didn't see any Kerberos pre-authentication errors. The only errors I saw was when the domain/realm picked up the name of the server instead of the name of the Active Directory name. However, there weren't anywhere near the # of audit failures as people were getting prompted. It almost seemed like every time SSRS retrieved a new file (such as an image file) it forced a re-authentication.
Since I needed the reports out for others to use, I rebuilt the reports for SQL Server 2005. This, not surprisingly, meant I had to drop back to Business Intelligence Development Studio (BIDS) for SQL Server 2005. The Data Source was able to publish, but not the reports themselves. The reports were simple queries displayed in tabular form, so it took just minutes to rebuild them, but that wasn't something interesting to see... if BIDS for SQL Server 2008 could publish to a SSRS 2005 server just by switching the Reporting Services target. It wasn't. There may be a way to force it... but I'll wait until next week to look at that in more detail. I'll also troubleshoot what IE settings broke the authentication in SSRS 2008 but which don't break SSRS 2005.
Last night the Midlands PASS Chapter had the pleasure of hosting SQL Server MVP Brian Knight. I was able to capture a few pictures from last night though they aren't the best quality.
Our next meeting is scheduled for July 10 and we are hosting John Welch, SQL Server MVP, who will be speaking on managing SSIS between different environments.
Tuesday, May 27, 2008
Speaker: SQL Server MVP Brian Knight
The Midlands PASS chapter will hold a special meeting on Tuesday, May 27, 2008, to host SQL Server MVP Brian Knight. Brian will be giving a presentation on Data Mining using SQL Server. The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the presentation at 6:45 PM. The meeting is sponsored by AgFirst Farm Credit Bank
Please feel free to forward this to anyone who you think would be interested in attending. If you plan on attending, please RSVP via SQLServerCentral.com Private Mail as soon as possible so we can ensure we have enough space and food. If you have time to help with setup, please email me and we’ll plug you in!
Brian’s Bio:
Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of SQLServerCentral.com and JumpstartTV.com. He runs the local SQL Server users group in Jacksonville (JSSUG) and was on the Board of Directors of the Professional Association for SQL Server (PASS). Brian is a contributing columnist for SQL Server Standard and also maintains a regular column for the database website SQLServerCentral.com and does regular webcasts at Jumpstart TV. He has co-authored and authored more than 9 SQL Server books including Admin911: SQL Server (Osborne/McGraw-Hill Publishing), Professional SQL Server DTS, Expert SSIS, Professional SQL Server 2005 Administration and Professional SQL Server 2005 SSIS (Wrox Press). Brian has spoken at conferences like PASS, SQL Connections and TechEd and many Code Camps. Brian spends most of his time trying to think about how to use the word onomatopoeia in every day sentences.
The dates for Microsoft's TechEd 2008 are fast approaching here in North America. Sandwiched between the two weeks of this year's TechEd is the SQL Saturday Tweener, which will also be held at the Orange County Convention Center. Unfortunately, I'm not headed to TechEd this year, but if I were, I would certainly be at the SQL Saturday as well. I should note that SQL Saturday is but one of the events going on in parallel, as Joe Healy of Microsoft worked to get all of the technical communities in Florida access to the convention center over the weekend.
Having been to one of the SQL Saturdays, when I presented down in Jacksonville, I'm a believer that this kind of setup works. It's inexpensive to the attendees, it provides excellent professional development opportunities to the speakers, and it's a great and varied training day for everyone (speakers included). We'll be working to try to bring one up here in Columbia, SC. So if you've not been to one and can make it to Orlando, I'm sure it will be well worth your time!
SQL Server MVP Frank Kalis has posted a short review on How to Cheat at Securing SQL Server 2005, a book I was able to contribute two chapters to last year. The chapters I focused on were related to Authentication and DDL Triggers. It was a great experience and I am humbled by the positive review from Frank. I first met Frank on the forums at SQLServerCentral.com and he is one of the most knowledgeable and helpful people I have had the pleasure to interact with. You can find his review at SQL-Server-Performance.com:
Review: How to Cheat at Securing SQL Server 2005
For those who understand German, Frank's original review in German can be found here:
InsideSQL.org: How to Cheat at Securing SQL Server 2005
There are a few podcasts I tend to listen to as I have time. Since I work with a wide range of technologies, I've tried to group them together into a semblance of order. There are a few others I am evaluating, but since I haven't listened to a large enough body of work, I'll refrain from listing them at this time. If there's one you think is particularly valuable or interesting that I don't have listed, please leave it in the comments.
.NET
.NET Rocks - http://www.dotnetrocks.com/
This is one of the best done podcasts out there and they cover anything and everything related to Microsoft .NET. That's a broad brush of most anything that interacts with Microsoft technologies. This one runs twice a week and is about an hour each podcast, but if you can spare the time, it's worth the listen.
Plumbers @ Work - http://plumbersatwork.com/
These guys from Canada talk about a lot of different things, but most of it relates to .NET. They were quite for a while but then popped up two episodes in February and March. Another one I'm hoping gets more active again soon.
Architecture
ARCast - http://channel9.msdn.com/shows/ARCast.TV
ARCast used to be hosted by Ron Jacobs and covers architecture. While the focus is mostly on application architecture, there were some times when infrastructure architecture was covered. Unfortunately, this podcast has been on hiatus since the end of the year when Ron Jacobs moved on to another opportunity in Microsoft.
General Technology (Microsoft)
Behind the Code - http://channel9.msdn.com/shows/Behind_The_Code
Behind the Code talks to the people behind the technologies, and it's extremely interesting to hear the folks who have developed the things we use every day explain their challenges and ideas in their own words.
Going Deep - http://channel9.msdn.com/shows/Going_Deep
As the name implies, Going Deep is where folks do a deep dive on the technologies they are/were involved in. If you are interested in the Why? question, this is a great podcast.
RunAs Radio - http://www.runasradio.com/
As .NET Rocks is for the developer, RunAs Radio is for the infrastructure folks. It was spun off from .NET Rocks and is a weekly, half-hour show. Same quality as .NET Rocks and it shares some of the same people. If you work in IT infrastructure (in Microsoft or related technologies), this podcast should be right up your alley.
TechNet Radio - http://technet.microsoft.com/en-us/bb510143.aspx
Not sure why the folks at Microsoft don't have a more friendly link, but "it is what it is." This weekly podcast covers some aspect of Microsoft technology, usually whatever is "new" and "fresh."
MySQL
OurSQL - http://www.technocation.org/category/areas/podcasts
A podcast which focuses on MySQL. This one has also been quiet for a few months, but hopefully it'll pick back up again soon.
SQL Server
SQL Down Under - http://www.sqldownunder.com/
As the name implies, a podcast for SQL Server out of Australia. It's hosted by Regional Director and MVP Greg Low and the episodes typically have an extensive interview with a luminary in the SQL Server or general database category. It's not all technology stuff, either. For instance, the podcast with Kevin Kline talked a bit about family and the balance between life and work.
The Voice of the DBA - http://sqlservercentral.mevio.com/
This one is done by SQL Server MVP Steve Jones and it comes out every weekday. These generally tend to be short, they're not always on SQL Server, but as is standard Steve Jones' style, they make you think. This one is an easy one to stay up to date with and it's well worth the few minutes every day spent watching.
As a president of a local PASS chapter, one of the things I've struggled with is getting "locals" to give presentations. We have a couple of guys who have done so, but part of the reason local chapters exist is to go folks an opportunity to develop their professional skills in a safe and friendly environment. At the last SQL Saturday, Andy Warren and I talked about this problem, as he's had a bit more success getting folks involved. However, he has seen it as a problem, too, and has developed a course to help those who want to learn how to do presentations. It's a great start. If you really want to work on your speaking skills and you're not just concerned about technical speaking, Toastmasters International is a great organization. I joined about a year ago and it is a friendly, safe, and encouraging place for me to work on my presenting skills. It also helps developing listening skills as well as as the ability to think on one's feet. I cannot recommend it enough.
But speaking ability alone doesn't make for a great presentation. Scott Hanselman has a great post about achieving a successful technical presentation. #3, about when to move, is something I have to be conscious of, because I tend to like to walk as I talk. Another area that's related is my hands. When I think about my hands, I do a good job of using them to aid the presentation. When I don't, they can be distracting. #4, font size, is extremely important, too. At the Midlands PASS chapter we had one speaker who had font sizes so small you couldn't read anything on the screen. She didn't do anything to fix the issue and this was remarked on privately after the presentation. At the last SQL Saturday I asked what was viewable, but I should have already had my fonts set, as Scott recommends. I'm filing that away for next time. #6. knowing the presentation completely, is another one I saw as a problem with that presentation. The presenter had great information, but when asked specific questions, she couldn't respond. This didn't go over very well with the folks who were interested in her subject. And finally, I love his #11, care. I present on SQL Server security because I care about SQL Server and I care about security. Both are passions for me professionally. So when I get to mix the two, oh boy! But it is hard to give a presentation on something you aren't personally interested in. Folks will know. I was stuck in that situation in college when I gave another guy's presentation of his physics research. It was required for our undergrad requirements but at the last moment he couldn't be there. He gave the presentation privately to our physics instructors and I gave the public one because he was already on the schedule. I tried my best, but it was an area that I wasn't very interested in. I don't know how well I did, because everyone knew what was going on, but I know I couldn't carry it with the same passion as my own research presentations.
I ran across this a week or so ago. There were a couple of SQL Servers running named instances that we wanted to setup Kerberos authentication against (in the event we would use Kerberos delegation). Here is how the ports were set according to SQL Server Configuration Manager:
The problem here is that the TCP port is set under the TCP Dynamic Ports field. This is the default when dealing with named instances. Default instances are automatically configured to listen statically on TCP port 1433.
When SQL Server is set to use dynamic ports, it will check to see if the port it last used is available. Most of the time it is. But if it isn't, it will find the next available port. If you're not using Kerberos authentication, this isn't that big a deal unless you do things like lock down via IPSEC policy, ACLs on network equipment, etc. However, when it comes to Kerberos authenication, it will be a big deal if that port ever changes. The reason I cite this is because while I haven't had an issue on the Kerberos side, years back I did have an issue when the port did change when SQL Server restarted. We had a web application which faced the Internet and it was hardened so it could only talk to SQL Server on the particular port SQL Server was configured to listen on. Since the SQL Server was a named instance and even if it wasn't we wanted a different port, this became a problem when SQL Server started listening on the "wrong" port. For whatever reason, when it restarted, the port it had been listening on was in use. And since we had not configured it for a static port assignment, it chose a different port. Ugh. We ended up finding the offending process, stopping it, making the change in the server network utility (this was SQL Server 2000) and restarting SQL Server. Then the web application began working again. In SQL Server 2005 it's easy to ensure that the port is static:
Note that the entry is now on TCP Port. This will ensure SQL Server will only try to listen on that one port. Now, you may be thinking, "What if they port is in use?" Obviously, SQL Server won't listen on it. However, since Kerberos is specific to the port, Kerberos authentication would fail anyway if SQL Server was listening on a different port. You still have the ability to connect in via Shared Memory local to the server or to use Named Pipes, if that is configured (some apps still require it, for some reason). Or, you can find the offending process that's listening on your port by running netstat -ano from the command prompt on the server and comparing in Task Scheduler to figure out PID and therefore what process is listening on your port. Then deal with the offending process and restart SQL Server.
More Posts
Next page »