Tuesday, April 14, 2015

One of many reasons to translate VBScript to PowerShell

I'm wrapping up the deployment of a cross-platform automation tool for our distributed databases(DB2 LUW, SQL Server, Oracle). 

One of our major items to convert is a VBScript that does several post-installation tasks for SQL Server. 

I've decided to take this opportunity to retire as much VBScript as possible. 

Why might you ask? 

Here's my first example...

To check the version of Windows in our VBScript requires twelve lines of code


PowerShell only needs one. 


Well, we'll need two lines to store the Caption and CSDVersion in variables for later use.

I expect I'll have several other great examples to show from this effort.

Get stuff done faster with less code. Use PowerShell.




Thursday, February 12, 2015

SQL Saturday #371 Tampa - I'm speaking!

I'll be speaking at SQL Saturday #371 in Tampa on February 28, 2015.

If you are interested in minimizing or possibly preventing the type of breach that happened at Anthem Inc, you will likely find my session "Real World SQL Server Database Administration with just a bit of sysadmin" very interesting. 

It is becoming increasingly difficult to allow SQL Server database administrators to retain perpetual sysadmin access on production servers due to IT Security, Audit, and Compliance concerns. 

will review the fundamentals needed to define a configurable permission model currently in use at a large insurance company that allows database administrators to do routine work without having unfettered access to business data. Several demonstrations will show that many DBA tasks can be done without sysadmin access. Attendees will also learn how to deploy a set of permissions that allows DBAs to do routine work, elevate DBA permissions quickly to respond to production emergencies and how to grant sysadmin permissions during disaster recovery scenarios. Scripts will be reviewed and demonstrated that secure the database server, undo the permission model in case of unforeseen circumstances and discover which servers remain to be locked down. Attendees will leave this session with the realization that DBAs need to be sysadmin only when required.

SQL Saturday is a FREE training event for SQL Server professionals and those wanting to learn about SQL Server. SQL Saturdays are possible because of PASS, our sponsors, and the many volunteer speakers and staff that run the event. I encourage you to check the schedule

I guarantee you will learn something new by the end of the day!

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