Thursday, December 4, 2014

Getting SQLPSX to work with SQL Server 2012

A few years ago, I wrote PowerShell scripts to assist our IT Risk department with auditing and ID management. With the deployment of SQL Server 2012 in our environment those scripts are starting to show their age. 

The scripts were designed to run from a central server and they use SQL Server PowerShell Extensions version 1.6.1. 

The scripts are throwing errors when trying to connect to SQL Server 2012 instances.

The SMO assemblies are loaded with the following code by LibrarySmo.ps1:



If I run this bit of code on our central server it returns the Version 10 SMO assemblies that allow it to connect to SQL Server 2008 R2 and below.

Running this code on my laptop or server that has the SQL Server 2012 tools installed returns the Version 11 SMO assemblies which allow the scripts to run on SQL Server 2012.

In addition, if the scripts are copied to a server with SQL Server 2012 installed, they run successfully because the code above is loading the later version of the SMO assemblies.

So, the fix for our central server is to either install the SQL Server 2012 client tools or just the compatible SMO assemblies. We now know we need to keep the central server up to date with the latest assemblies to keep these scripts working.

Helpful references:
http://sqlpsx.codeplex.com/discussions/353631
http://sqlserverpowershell.com/2012/07/24/sqlpsx-sql-server-2012/

Monday, November 10, 2014

Veteran's Day Trivia - Name That Aircraft

Feeding a wild pig



WORKING CONDITIONS: Works indoors and outdoors, in inclement weather, and on icy, wet, or slippery ramps and aircraft surfaces in temperature and humidity extremes. Subject to dangers from exposure to toxic fumes, high-pressure air and fluids, fast actuating aircraft surfaces such as landing gear, speed brakes, and flaps.  Dangers also include engine noise, jet blast, jet intakes, explosive munitions, electrical voltage cartridge actuated devices, liquid oxygen, fire, aircraft fuels, lubricants, paints, hydrazine, and solvents.

Working in IT is a piece of cake.

That airman on the left is me!
I salute all veterans on Veteran's Day!

Bonus points if you can name the aircraft I'm working on.

Friday, October 31, 2014

Training on the T's - SQL Server Security Easy Button Q&A

On October 7, 2014, I spoke for Pragmatic Works' Training on the T's webinar series. 
Here's the rest of the questions I didn't get to answer. 

Q: Where can I get the slide deck and presentation?
A: Orlando SQL Saturday , Tampa SQL Saturday , Jacksonville SQL Saturday

Q: All of our database tables are under the dbo schema. How can I set up programmers to be able to modify stored procedures but not give them the dbo schema permission with which they can modify tables?
A: I think the only possible way to do this is to split the stored procedures and tables into different schema. I do not see a way in the permission models to grant permission to modify stored procedures without also granting the same permission to modify tables.  Granting ALTER ON OBJECT gives access to multiple object types in the database. See longtime SQL Server MVP Erland Sommarskog's comment here on this question. See the next question for additional information. 

Q: Where i can get the SQL Server permissions PDF?
A. Google "sql server permissions poster" or go to this link.

Q: Good morning, Where I can find the current session recording? I missed half of the beginning part.
A: Consider yourself lucky. You missed most of my 'ums' in the first half. ;-)
You can find it here.

Q: Would you recommend using this security script along with C2 auditing?
A: Yes, but I would recommend using the Common Criteria Compliance option instead. C2 audit mode is deprecated and will be removed from a future version of SQL Server.

Q: The password vault you keep referring to is the windows credential manager found under user accounts in control panel?
A. No, it's called the Cyber-Ark Enterprise Password Vault

Q:You mentioned a couple of names of people we should know and I missed the name of the first guy.

Q: How does the DBA sign out a user id with sysadmin rights?
A: In our environment, they log in to Cyber-Ark and provide a valid change or incident ticket.

I apologize for too many 'ums' and completely forgetting to use Zoomit while reviewing the script.
Thank you for attending! 

Friday, September 19, 2014

MagicPASS September meeting

I spoke at the MagicPASS September User Group meeting in Celebration, FL.

Kendal Van Dyke (b|t) does a great job running this group.

Home-cooked meals for attendees! Need I say more? But, I will. 

The Disney Vacation Club provides an excellent facility.

I liked the MagicPASS meeting format. They have a pre-meeting and a main meeting. I spoke during the main meeting at 7:30 PM. The meal is served between the two meetings. Baked ziti and salad. Nom, nom, nom.

During the pre-meeting, the presentation was a recording from the PASS Performance Virtual Chapter. This is a great idea because it makes attendees aware that virtual chapters exist and they get a chance to see a presentation that maybe they didn't get to see. I had seen this one. Oh, well.

Another informative agenda item in the pre-meeting is "Industry News".  Kendal talked about upcoming service packs, cumulative updates, and critical patches. He asked if anyone was using Ola Hallengren's scripts. (YES!) Then, he described an issue he had with the scripts caused by a memory leak in one of the SQL Server components that is fixed by a recent CU. Kendal also shared his experience on a recent trip to VMWare. I learned DBAs should have READ access to vSphere ( I do.) and that if you turn the correct "nerd knobs", SQL Server runs pretty well on VMWare. This is WHY I go to user group meetings! I always learn something I didn't know.

My take-away from this meeting is that I need to have a clear view of my notes in Presenter view. My laptop was on a table instead of on a podium and I think this caused too many hesitations in my talk at the beginning because I would pause to bend over and check my notes.  I like to stand when presenting instead of sitting. I'll need to expand the font size of the notes or commit more to memory.

The next day, I figured out how to increase the font size of the notes in Presenter View. 

It takes just over an hour to drive to the MagicPASS meeting location from my house so I'd be willing to do it again for the "Happiest SQL User Group on Earth". 
Thank you Kendal and MagicPASS for having me! 

I'll be there the next time you have tacos. ;-)

Wednesday, August 20, 2014

My upcoming speaking calendar

If you work in an organization that is finding it increasingly difficult to allow SQL Server database administrators unfettered sysadmin access to your Production servers, then you have four opportunities to hear me speak about how to control this type of access.

PASS Healthcare Virtual Chapter
August 21, 2014
http://healthcare.sqlpass.org/

MagicPASS 
September 17, 2014
http://magicpass.sqlpass.org/

SQL Saturday #318 in Orlando, FL 
September 27, 2014
http://www.sqlsaturday.com/viewsession.aspx?sat=318&sessionid=21152

Training on the T's
October 7, 2014
http://www.pragmaticworks.com/LearningCenter/FreeTrainingWebinars/FutureWebinars.aspx?ResourceId=692


Sunday, August 3, 2014

How do I get started with PowerShell?

My co-workers recently asked me how to get started with PowerShell.  
Many of them are still writing VBScript and I am VERY willing to help them stop writing VBScript.

So, I sent the following to them. Most of which is FREE.

Windows PowerShell Scripting


FREE E-books:


The VBScript-to-Windows PowerShell Conversion Guide

Don Jones’ "Learn 'Something' in a Month of Lunches" books are very good. 
I have this one.

For real world examples, I recommended:
Finally, The Stairway to SQL PowerShell looks like one of the easiest ways to start.
This is not meant to be a complete list because I didn't want to overwhelm them. You can always find more with your favorite search engine.
I'm hoping this post will help someone else get started with PowerShell. 

Tuesday, July 29, 2014

Checking for SQL Server AlwaysOn patches for Windows Server 2008R2...

Time moves slower in a big company's data centers.

Meaning, you might not get to run SQL Server AlwaysOn Availability groups on Windows Server 2012. If not, you'll have to check several patches are applied for it to work correctly on Windows Server 2008 R2.

This excellent resource provides a list of the needed patches in the FREE AlwaysOn Setup Checklist PDF.

I think the easiest way to determine if all the required patches are installed is by using the PowerShell cmdlet get-hotfix with the -computername and -id parameters.

Beware, the example below is a single line.

get-hotfix -computername YourServerNameHere -id KB2494036, KB2494162, KB2520235, KB2524478, KB2531907, KB2550886, KB2552040, KB2578103, KB2578113, KB2582281, KB2616514, KB2654347, KB2687741, KB976097, KB980915


If any of the patches are found, a five column result set is returned. 

If all the patches are found, you'll see fifteen rows. 

If none of the patches are found, the command throws an error and you'll see red.
White beats red any day.
While writing this post, Windows Server 2012 was approved for PRODUCTION deployment at my big company. 

Does that mean time moves faster when you blog? ;-D


Thursday, July 3, 2014

My PASS Summit 2014 Abstract Feedback

I've just received my feedback from the review committee for the 2014 PASS Summit.  

I'm thrilled with this feedback even though my session was not accepted. 



This is the kind of feedback all potential speakers need going forward.

Thank you to the PASS leadership and the #sqlpass community for making this happen.


Friday, June 27, 2014

Tell me more...

I submitted one session for the 2014 PASS Summit.
I knew it was a long shot because...

It was a "Security" topic. 
I am not an MVP.
I am not a Consultant.
I am not an MCM.
I don't travel to SQL Saturdays outside of Florida. 

The only feedback I got was...

Session not accepted - allocated number for track filled based on session rating and topic coverage

Do I need to stay at a Holiday Inn Express to get accepted?

I did stay at a Holiday Inn Express when I spoke at SQL Saturday #298 in Jacksonville, FL.

Seriously, I've spoken at THIRTEEN SQL Saturdays so far and to my local user group a handful of times.

I have a regular job as a Lead DBA at an insurance company. Insurance companies don't get to the latest stuff as fast as other companies. I've talked about my own real world experience regarding automation and security hoping that it can help people in similar situations.

It's a good feeling to say you've submitted for PASS Summit.

The response when you are not accepted needs to provide more information. 

How did I score? 
Where did I rank among the other abstracts? 
Did I just miss?

I've played sports all my life. Sometimes, I've tried out for teams and didn't make the cut. It's a character building experience when you don't make the team but without additional feedback I've got nothing to build on.

I simply want to know how I scored and what the cutoff was.

Congratulations to all of you who did get accepted!








Tuesday, April 15, 2014

SQL Saturday #298 - I'm speaking!

I'll be speaking at SQL Saturday #298 in Jacksonville, FL on May 10, 2014.

If you work in an organization that is finding it increasingly difficult to allow SQL Server DBAs 24x7x365 sysadmin access to your Production servers, then I think you will find my session "SQL Server Security Easy Button" very interesting.

Attendees will learn how to define a permission set with a single script that allows database administrators to do routine work without sysadmin permission and how to elevate permissions quickly to respond to production emergencies. 

Attendees will leave this session with a complete script that provides the minimum necessary access database administrators require to maintain the database server without granting access to business data. 
In addition, I’ll talk about our real world experience with reduced permissions.

Hope to see you there!

Thursday, February 27, 2014

SQL Saturday #273 Recap

I attended and spoke at SQL Saturday #273 on February 22, 2014.


Selfie in @aboutsqlserver session

My presentation "SQL Server Security Easy Button" went off without a hitch. My presentation files can be found here.

This was the first time the event was held at the University of South Florida's College of Business Building on the main campus in Tampa, FL.  It was an excellent facility for a SQL Saturday. No crowding, no bottlenecks, great classrooms. The atrium in this building provided the perfect place for vendors to setup and for people to network. As usual, an excellent lunch was provided by Latam's. A college campus is the perfect venue for a SQL Saturday. They have well-equipped classrooms and it gives students a chance to interact with people who are working the real jobs for which they are studying. 

Our event provided another public service. We were able to thaw out several people from the Great White North. Tom LaRock, Allen White, Grant Fritchey to name only a few were successfully thawed. Thank you for coming. ;-)

The sessions I attended were:
"Eternal story on temporal objects" by Dmitri Korotkevitch.  Dmitri reviewed all the possible temporal objects that SQL Server used and demonstrated the best way to use them. Afterwards, Dmitri told me that his Twitter account gained 25 followers after myself and Grant Fritchey tweeted during his session. He said, he now has more followers than tweets! I told him that he had to be capable of one epic tweet per day. The pictures he used to illustrate tempdb amusingly demonstrated his talking points.





Allen White's "Managing SQL Server Efficiently with PowerShell Remoting". Allen did an excellent job demonstrating how remoting makes it much easier to manage multiple machines and describing the maturation of PowerShell. It's much easier to build and configure a new database instance on Windows Server 2012 with the V4 cmdlets.




Chris Skorlinkski's "Your Best Interview Ever"
I was familiar with most of the material that Chris covered. The main concept I took away from this session was to have an "elevator speech" ready to describe yourself to potential employers. I loved the example he used for it. It accomplishes all the goals of an elevator speech. Identify yourself, describe what you can do, and what your objective is.




Thank you to Pam Shaw, Leigh Freijo, sponsors, speakers, and volunteers for putting on a great event! I especially appreciated that the volunteers printed and cut up the SpeedPass for the speakers!

Also, thank you to Brian Mitchell and Jonathan Keyahias for answering some questions I had related to projects I have in progress at work.

Finally, the best juggler at SQL Saturday #273 was Eric Wisdahl.


video








Saturday, February 8, 2014

SQL Saturday #273 Tampa - I'm speaking!








I'll be speaking at SQL Saturday #273 in Tampa on February 22, 2014.

If you work in an organization that is finding it increasingly difficult to allow SQL Server DBAs 24x7x365 sysadmin access to your Production servers, then I think you will find my session "SQL Server Security Easy Button" very interesting.

Attendees will learn how to define a permission set with a single script that allows database administrators to do routine work without sysadmin permission and how to elevate permissions quickly to respond to production emergencies.
Attendees will leave this session with a complete script that provides the minimum necessary access database administrators require to maintain the database server without granting access to business data.
In addition, I’ll talk about our real world experience with reduced permissions.

I'm looking forward to SQL Saturday #273 in Tampa.

Hope to see you there!


Saturday, January 25, 2014

Troubleshooting requires...

In my humble opinion, troubleshooting requires fundamental knowledge, persistence, patience, attention to detail, and a willingness to learn continuously.

My Father-in-Law passed away this past week.  After the funeral, we came back to the house to spend time together. That evening, everything in the Family Room connected to electricity started acting very weird. Lights flickering, TV going on and off, etc.  Honestly, my first thought was his ghost had come back to haunt us. 

Then, we began to smell the odor of something electrical going bad.

Let the troubleshooting begin!  

After unplugging everything, I inspected each outlet.

The last outlet I checked behind a bookcase and one that we never use looked liked this...






















The discoloration on the face of the outlet is what caught my eye. 
The picture above is after I removed the cover plate and pulled it from the wall. Don't forget to switch off the correct circuit breaker before doing any of this type of work! 

After fully removing it, I found the full extent of the damage.















Somehow, one black wire came loose on this outlet and it started arcing.

It only cost a $1.15 to repair but it took a couple of iterations of rewiring and re-installing before it started working again.  I broke one of the black wires after the first attempt while pushing it back into the box in the wall. So, that is when the patience and persistence came into play.

Attention to detail and my fundamental knowledge of electricity is the main reason I found the problem. (Thank you, United States Air Force.)

While doing the repair, I learned how multiple outlets are wired together.

The point of this post is that troubleshooting skills are transferable across many situations. I've used my skills to do database administration, aircraft maintenance and fix things at home.

My troubleshooting skills saved a call to an electrician and the TV in the Family Room is ready for the Super Bowl. They've also helped avoid much grief at the day job. ;-)