Security as a Fleeting Thought

Comments: 6 Comments
Published on: February 10, 2015

Today we have another installment in what is known as TSQL Tuesday.  This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter).

TSQL2sDay150x150Today, the invitation is for us to share our stories on how we like to manage security.  Or at least that is the request that was made by Kenneth.  I am going to take a bit of a twist on that request.  Instead of sharing how I like to manage security, I am going to share some interesting stories on how I have seen security managed.

Let’s just call this a short series on various case studies in how to manage your security in a very peculiar way.  Or as the blog title suggests, how to manage your security as an afterthought.

Case Study #1

dbsecurityWe have all dealt with the vendor that insists on the user account that will be used for their database and application be one of two things.  Either it needs to be sa or needs to be a member of the sysadmin fixed server role.  The ensuing discussion with those vendors is always a gem.  They insist the application will break, you as the diligent DBA prove otherwise, and then the senior manager sponsoring the application comes around with a mandate that you must provide the access the vendor is requesting.

Those are particularly fun times.  Sometimes, there is a mutual agreement in the middle on what security can be used and sometimes the DBA just loses.

But what about when it is not a vendor application that mandates such relaxed security for their application and database?  What if it happens to be the development group?  What if it happens to be a developer driven shop and you are the consultant coming in to help get things in order?

I have had the distinct pleasure of working in all of those scenarios.  My favorite was a client that hosted ~700 clients, each with their own database.  There were several thousand connections coming into the server and every single connection was coming in as ‘sa’.  Yes, that is correct.  There were no user logins other than the domain admins group on the server – which was also added to the sysadmin security role.  That is always a fun discussion to start and finish.  The look of color disappearing from the clients’ eyes as the realize the severity of the problem.

Please do not attempt this stunt at home.

Case Study #2

In a similar vain, another one that I have seen far too often is the desire to grant users dbo access within a database.  While this is less heinous than granting everybody sysadmin access – it is only a tad better.  Think about it in this way – does Joe from financing really need to be able to create and drop tables within the accounting database?  Does Marie from human resources need to be able to create or drop stored procedures from the HR database?  The answer to both should be ‘NO’.

In another environment, I was given the opportunity to perform a security audit.  Upon looking over things, it became very clear what the security was.  Somebody felt it necessary to add [Domain Users] to the dbo role on every database.  Yes, you read that correctly.  In addition to that, the same [Domain Users] group was added to the sysadmin server fixed security role.  HOLY COW!

In this particular case, they were constantly trying to figure out why permissions and objects were changing for all sorts of things within the database environment.  The answer was easy.  The fix is also easy – but not terribly easy to accept.

Please do not attempt this stunt at home.

Case Study #3

I have encountered vendor after vendor that has always insisted that they MUST have local admin and sysadmin rights on the box and instance (respectively).  For many this is a grey area because of the contracts derived between the client and the vendor.

For me, I have to ask why they need that level of access.  Does the vendor really need to be able to backup your databases and investigate system performance on your server?  Does that vendor need, or are they even engaged, to troubleshoot your system as a whole?  Or, do they just randomly sign in and apply application updates without your knowledge or perform other “routine” tasks unknown to you?

I have seen vendors change permissions and add back door accounts far too often.  They seldom if ever are capable of providing the level of support necessary when you are stuck with deadlocks by the second or blocking chains that tie up the entire server.  In addition, they are generally unavailable for immediate support when a production halting issue arises in their application – or at least not for a few hours.

This is specifically in regards to application vendors.  They are not your sysadmin and they are not your DBA.  If they must have RDP access or access to the database – put it under tight control.  Disable the account until they request access.  Then a request can be made and a note documented about why the access is needed.  Then the account can be enabled, monitored and disabled after a specified amount of time.

Please do not attempt this stunt at home.

This also changes when that vendor happens to be providing you IT functionality and is not specifically tied to an application.  Those relationships are a bit different and do require a little more trust to the person who is acting on your behalf as your IT staff.

Conclusion

I have shared three very dangerous stunts that are sometimes portrayed to be done by professionals.  Do not try this in your environment or at home.  It is dangerous to treat security with so little concern.  Security is not some stunt, and should be treated with a little more care and attention.

If you find yourself in any of these situations, an audit is your friend.  Create some audit process within SQL Server or on the Local server to track changes and accesses.  Find out what is going on and be prepared to act while you build your case and a plan for implementing tighter security.

SQLSat in DC – it’s a wrap

Categories: News, Professional, SSC
Comments: 2 Comments
Published on: December 8, 2014

sqlsatdc14
This past weekend I had the opportunity to go visit Washington DC.  It was just the second time I got to stay in the Nation’s capitol for more than just a few hours.  The previous opportunity came with last years event which I talked about here.  Sadly, my time was far too limited this trip and seeing the sites was far more limited.  Thus, I only saw them from the car or plane window in passing.  But that is far better than seeing them in photos or not at all.

The reason for the visit?  It was SQL Saturday 347.  Now, it is my chance to recap the event and what I learned.

sqlsat347_web

This year I really wanted to attend to see some of my friends and to help some friends as well.  An example is that one friend was looking for some good constructive feedback to his presentation.  That friend happens to be a person I met at the event from last year: Ayman El-Ghazali (blog).  I had a good conversation with Ayman in the speaker room following his session and between some of the sessions.  Ayman is a DBA that is working hard to learn and improve any way he can.  Those are traits that are important to have as a DBA these days.

Much like last year (and I even made the comment), it was warm again (definitely shorts weather) and it also rained again.  I really think there must be a trend with SQL Saturday in DC in that it brings rain.  There may be something there.

The event was well organized.  I think that is mostly due to Gigi Bell (twitter).  She is the wife of Chris Bell (twitter) and she whipped those boys into shape. ;)

There were some things that couldn’t be controlled necessarily.  But everybody came together and helped to make it work.

I had the great opportunity this year to present the very very very short session on Murder that I give as a precon along with Wayne Sheffield.  People walked away from the session expressing gratitude for the content and how much they learned.  Many were even curious to learn more of what we could present in the all day version.  Very COOL!

I also had a great time seeing SQLFamily.  Talking with friends and enjoying everybody’s company.  I did make it to a few sessions outside of mine.  And I got to chat with attendees while trying to answer their questions in the halls.

I am looking forward to this event again next year.  And I hope everybody that attended my sessions learned at least one thing.

One last thing.  Thanks to all of the attendees.  To say “the attendees were great,” at this event, would be a gross mis-understatement in my opinion.  The attendees were awake and engaged (even if they had never heard of “Clue” before the session).  They invested their time and effort and I think they helped to make the event top notch.

TSQL Tuesday #60: Something Learned This Way Comes

Comments: 4 Comments
Published on: November 11, 2014

TSQL2sDay150x150It is once again time to come together as a community and talk about a common theme.  This monthly gathering of the community has just reached it’s 5th anniversary.  Yes, that’s right.  We have been doing this for 60 months or five years at this point.  That is pretty cool.

This month Chris Yates (blog | twitter) has taken the helm to lead us in our venture to discuss all the wonderful things that we have learned.  Well, maybe not all the things we have learned, but at least to discuss something we have learned.

Here are some details from the actual invite that you can read here.

Why do we come to events, webinars, sessions, networking? The basic fundamental therein is to learn; community. With that said here is this month’s theme. You have to discuss one thing, few things, or many things on something new you’ve learned recently. It could be from a webinar, event, conference, or colleague. The idea is for seasoned vets to new beginners to name at least one thing; in doing so it might just help one of your fellow SQL friends within the community.

The topic is straight forward but can be a bit difficult at times.  This is a pretty good topic to try and discuss.  I know I have been struggling for content for the topic.  Which makes it that much better because it provides a prime example of how to think about and discuss some pretty important things, while trying to compile that into a recap of one’s personal progress.

Let’s think about the topic for a bit and the timing of the topic.  This comes to us right on the heels of PASS Summit 2014 and in the middle of SQL Intersections in Las Vegas.  We might as well throw in there all of the other things like SQL Saturdays that have been happening leading up to and following those major conferences.

There has been ample opportunity over the past few weeks to learn technical content.  When networking with people there are ample opportunities at these major conferences to also learn about other people and about one’s self.  A good example of that can be seen in a blog post I wrote while attending PASS Summit, which you can read here.

The biggest learning opportunity that evolved from PASS Summit 2014 for me was the constant prodding in various sessions to break out the debugger and become more familiar with what is happening in various cases.  I saw the debugger used in three of the sessions I attended.  There are some great opportunities to learn more about SQL Server by taking some trinket of information from a session and trying to put it to use in your development environment.  This is where learning becomes internalized and gives a deeper understanding.

I hope you have been able to pick up on some tidbit that can be used to your advantage to get a deeper understanding of SQL Server.

Summit 2014 – Next Impressions

Comments: 4 Comments
Published on: November 7, 2014

As Summit 2014 begins to wind down, it is time for some more impressions from the week.  The week has been good so far.  It has been very busy and also can be quite a drain mentally and physically from everything that has transpired.

If you are interested, I have written about some of my other impressions from the week, here.

Several years ago, I blogged about an incident with plagiarism with both an original post and a follow-up.  I bring that up, not to rehash the negative, but instead to discuss an impression from this week.  If you read the follow-up, you will see that I had a chat, at that time, with Steinar (twitter) about the problem and how to resolve it.  I met Steinar for the first time this week.  And to be honest, I had forgotten about the conflict and had removed the RSS feed since the original domain had gone down.

Anyway, Steinar and I had the chance to chat for the first time face to face this week.  Steinar, in my opinion, is a pretty cool guy that made a simple RSS mistake.  The impression is that he remembered me for how I treated him several years ago and was very appreciative of that.  How cool is that?  I really appreciate the opportunity to chat with him and that something I did left a positive impact on him.

Another opportunity is to be able to perform random acts of kindness or service while at Summit.  Much like helping Paul White learn how to use a smart phone, I had the even more rare opportunity to help Kalen Delaney (blog | twitter) out of a sticky situation.  It was a minor but frustrating thing that all of us run into from time to time.  The zipper on her Surface case had become stuck, so I helped her with that.  It’s a little thing but it is the type of thing that, if you are watching, you will see happening all over the place during the week of PASS Summit.

So, the next time you are at Summit, and while back in your local communities after the week has ended for Summit, keep an eye out for those little acts of kindness.  But at the same time, keep an eye out for those that might be watching you.  What kind of impression are you leaving for them or for the SQL Community?

Summit 2014 – Early Impressions

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: November 5, 2014

Summit 2014 is upon us.  Unless you are still under a rock, you probably know that.  And if you are under that rock, I am curious how you are reading this.

While it is early on in the week for the PASS Summit, things have really been going since Sunday for many.  A lot has happened.  A lot has already been learned.  And yes, some new people have already been met.  So far so good.

Now is a good time for me to just jot down some of my early impressions from the week.

It has been nice to receive a couple of compliments this week from a few people on various things.  It is waaaaay cool to hear things such as the following from community members.  Here are some samples.

“You taught me something.” Paul Randal (blog | twitter) in reference to a recent blog post that you can read here.  It is great to hear somebody learned something.  That is a primary driver for putting up content on the web and trying to help in the community where possible.

“Your index script has saved my bacon several times!” Stuart Ainsworth (blog | twitter) talking about my missing index script here.  Again, totally cool.  I am happy to hear about successes from code that I have put out there.

Those are two big impressions that would be great takeaways for the week.

But then we have to start throwing in the learning that is part of the week is.  I have attended a couple of sessions and found myself inspired by some of the content as well as hopeful by some of the other content.

By attending a session about Extended Events, I learned about an API that can expose some XE data via powershell to extend the possibilities and uses of XE.  Since the referenced blog does not contain any of the proposed material (slides or demos) it is hard to do much more with it just yet.  I will continue to check the referenced site as well as the session information on the Summit website.

By attending another session, I learned about a new feature called the Query Store.  It is basically a “Hammer that can make a lot of things look like nails!”  That session was presented by Conor Cunningham.  And while the Query Store has some extended events that are exposed in 2014, the XEs are useless and do nothing until SQL v.Next.  It would be totally awesome to have it back ported but that has no chance of happening.

And to top all of it off, it was great to sit down and get a couple of client issues fixed during the lunch break.

This is what Summit is, a huge chance to recharge, learn and to get excited about the technology and what is coming.  Oh and every now and again, one might get the chance to teach Paul White how to use a smart phone.

October 2014 Las Vegas UG Meeting

Comments: No Comments
Published on: October 8, 2014

The Las Vegas User Group is happy to announce our monthly meeting.  The meeting is available for in person and webinar style.  The start time is 6:30 PM Pacific and the details are listed in this post.  We hope to see you there!

invite_oct2014

This month we have a special treat brought to us by fellow MVP Argenis Fernandez (blog | twitter).  If you had a devilish little trick to upgrade SQL Server without an outage, would you do it?  Argenis will show you a good set of tricks to put your upgrade nightmares to rest.

You can read all about what Argenis is planning to present and read all about Argenis on our meetup page.

We hope to see you either in person or virtually for our monthly UG installment.

September 2014 Las Vegas UG Meeting

Comments: 2 Comments
Published on: September 10, 2014

Who is up for a little free learning this week? Besides the opulence and feast that was the 24 Hours of PASS (Summit Preview), we have more training in store for you from the people in Las Vegas. Let’s call this a preview for next week which happens to be DevConnections (which also happens to be in Vegas)!!

The Las Vegas User Group is happy to announce our monthly meeting.  The meeting is available for in person and webinar style.  The start time is 6:30 PM Pacific and the details are listed in this post.  We hope to see you there!

Capture

Abstract: PowerShell: The Way of the DBA Dragon

In this introduction to PowerShell, attendees will learn how to start from scratch with PowerShell 3.0 or newer, use the pipeline, run T-SQL against multiple instances, use transcripts, and be shown martial arts usage of one of the SQLPSX cmdlets.  Scripts will be provided.

BIO

Lars Rasmussen was born in Illinois, but considers Utah home.  He does not play video games, is learning to camp and hike, and is happy to have shared the summit of Mt. Timpanogos with two of his sons.  Lars’ wife and four children help him smile and laugh, and the family dog is teaching him patience.  Playing board games is one his favorite pastimes.  He considers SQL Server, PowerShell, and CMD.EXE some of his dearest frenemies.  Lars enjoys the company of SQL Server professionals and sysadmins – he used to be one of the latter, and is employed as a database administrator for HealthEquity.

LiveMeeting Info

Attendee URLhttps://www.livemeeting.com/cc/UserGroups/join?id=MR7C92&role=attend

Meeting ID: MR7C92

SQL Server UG in Vegas August 2014 Meeting

Comments: No Comments
Published on: August 13, 2014

evite

 

Another Great meeting and topic is coming to the folks in Las Vegas.  This month we have had the luck of finding Mike Fal (blog | twitter) step up and fill our speaker void.

Yes, it happens to be the second Thursday of the month already.  Being that time of month, the SQL Server UG of Las Vegas will be meeting at the Tahitti Village Resort and Spa to take in some great info on SQL Server and Powershell.

You can read the information about the meeting on our Meetup page here.  Or you can continue reading here.

Improving Database Restores with Powershell

Database restores are a key function of any database administrator’s job. However, it can be an extremely time consuming task to sort through your backups, find the right files, and then get your database up and running. In an emergency this will have a disastrous impact on your Recovery Time Objective(RTO) and lead to the dreaded angry-CTO-in-your-cube effect. By leveraging some easy-to-use Powershell scripts, you can avoid the second disaster and the pain that comes with it. By attending this session, you will understand how you can use the Powershell automation framework for database restores, see scripts that will let you restore faster, and learn techniques to extend these tools for migrating data and testing backups.

Michael Fal  

Mike Fal is a musician turned SQL Server DBA, with 10+ years of experience as a database administrator. He has worked for several different industries, including healthcare, software development, marketing, and manufacturing and has experience supporting databases from 1 GB to 4 TB in size. Mike received his a Bachelor’s Degree from the University of Colorado at Boulder in 1996 and has been caught playing trombone in public on more than one occasion.

LiveMeeting Info

Attendee URL:https://www.livemeeting.com/cc/UserGroups/join?id=4RD8NP&role=attend

Meeting ID: 4RD8NP

Whether you are in Vegas or you are somewhere else, you are welcome to join us.  We hope to see you Thursday evening.

Presenting at PSSUG

Comments: No Comments
Published on: August 4, 2014

pssug

 

Coming up this week, I have been given the opportunity to do something I enjoy doing.  I have been invited to present to the folks in Philadelphia.

Sebastian Meine (blog | twitter) approached me during SQL Saturday in Philadelphia and I was happy to help where I could.

The topic for this presentation will be Extended Events.  We are going to try a slightly different approach, but here is what was posted in the meeting invite.

Jason Brimhall SQL 2012 Extended Events
Extended Events were introduced in SQL Server 2008. With each edition since, we have seen a significant upgrade to this feature. Join me for a little adventure into defining this thing called Extended Events. We will discuss how to use Extended Events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of Extended Events.
Jason Brimhall

Jason Brimhall

 

As a Microsoft Certified Master/Microsoft Certified Solutions Master, I have 19 years’ experience in the technology industry, including more than 10 with SQL Server. I also earned a Bachelor’s Degree in Business Information Systems from Utah State University. One of the highlights of my career was co-authoring SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach .  I am a frequent presenter at SQL Server events worldwide, which includes SQLSaturdays and User Groups. I am also currently helping lead the Las Vegas SQL Users Group.

I am looking forward to this opportunity and to mingle with the group for a bit.  I hope to see you there.

Oh, and here is the link to the invite for the meeting.

The SQL Sac wrap!!

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: July 14, 2014

sqlsat312_web

Every SQL Saturday leaves a mark of some sort.  This time around, the folks in Sacramento have really helped leave a BIG mark.
That’s right, this last weekend was SQL Saturday in Sacramento Ca.  You might have seen my announcement about it here.

This event had a lot of Unique flair to it.  Besides having Jason Horner (blog | twitter) in attendance, the committee thought it wise to provide all of us with these little trinkets.

 

brand_inverted

 

That happens to be a branding iron.  I have inverted it in this picture for readability.  We received these speaker gifts at the speaker dinner the night before the event.  That is pretty normal.  What was different about this speaker dinner is that it was in a volunteers backyard and was a barbecue.  Yes!  There was fire!  Yes, we had implements of pain!  And yes, there were many jokes flung about during the evening.  If you were wondering, the first person to put his branding iron in the fire and to brand something was indeed Jason Horner.

Yes! This event left a BIG mark!

All the seriousness aside, there were some great presentations.  I was a bit disappointed to not be able to see the presentation about parameter sniffing by Benjamin Nevarez.  But I found my way into other presentations that made up for it.

If you haven’t already, congratulate the SQL SAC crew for their new youtube channel.  While you are at it, don’t forget to thank them for a great event.

For my first event traveling west, this was a memorable one.

«page 1 of 9






Calendar
April 2015
M T W T F S S
« Mar    
 12345
6789101112
13141516171819
20212223242526
27282930  
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, April 1, 2015