Ozzie.eu

A quick and easy read to get a feel of managing a SQL Server instance using powershell.

While securing a Microsoft SQL Server instance, there are many issues you should look after. According to the official documentation,
it can be viewed as a series of steps, involving four areas: the
platform, authentication, objects (including data), and applications
that access the system. On environments where SQL Server is used on a
more “Swiss Army Knife” fashion, there is often difficulty on disabling
or, at least, restricting access to the general extended stored procedures, like xp_cmdshell.
These stored procedures provide an interface from an instance of SQL
Server to external programs for various maintenance activities. But they
also represent a security liability. The alternative suggested is if
possible to use SQL CLR Integration
to perform these tasks. It can be done and it works, but maybe it isn’t
a desired task by the regular DBA to learn to program a .NET Framework
language.
With this in mind, as far as SQL Server Agent jobs are concerned, I’d suggest creating a Powershell job step.

Bear in mind this requires a specific kind of error handling. By default the ErrorActionPreference
is set to Continue, and this has implications on how errors bubble up
to the SQL Server Job Server. If you run a Windows PowerShell command as
a SQL Server Agent job and there are no syntax errors but the command
produces an error, the SQL Server Agent job will report success. If you
want an error condition to halt execution of a SQL Server Agent job or
to produce an error, you’ll need to add some error handling. To bubble
up Windows PowerShell errors to SQL Server Agent, you’ll need to set
your $ErrorActionPreference = “Stop”. You can check that out here.
For the processing task which are a hybrid of data manipulation and
other external system related tasks I’d suggest the development of
Powershell scripts that use SQL Server Management Objects (SMO) which is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.
Sure, you might say that this suggestion doesn’t help from learning to
code, but this is an inevitable goal every SQL Server DBA must have as
the future might have projects where the projects might have a server core platform. What then?
So why not kill two rabbits with one stone and learn your Powershell and SMO, it quite seems  the way to go. For starters, this might help.

On the same subject as this post, this editorial from SQLServer Central came out.

Happy coding!

Absent fellow DBAs, be jealous :-)

Microsoft is giving away a one year FREE subscription for an Office 365 Development
instance. Sign up and you’ll receive the link via email to setup
your subscription.

The Office 365 Developer Program is a vibrant and growing community of
developers, veteran and newbie, who are developing solutions on Office
365. 

You can go to the sign up form from this page.

Microsoft chose to remove SQL Server 2014 Service Pack 1 (SP1) from the Microsoft
Download Center due to a significant installation issue discovered
shortly after release.

When applying the patch, any SQL Server instance with SSISDB catalog enabled runs into an installation error during SQL Server 2014 SP1 upgrade. The
instance is then in an invalid and unusable state.

• In the 11 hours of availability, there were 270 downloads from the Download Center. No other distribution channels were affected.
• The original release was version 12.0.4050.0, the new release will have a new KB and build number.
• When SP1 is released again, Microsoft will recommend that 12.0.4050.0 is uninstalled prior to applying the new SP1 update. This includes any “Side by Side” instance scenarios.
• Microsoft is targeting release in a few weeks and will share updates as we go.

You can follow the updates on the official release page. If you have installed the update, there are also steps for a workaround.

Don’t blame the OS, blame the users. Reading this news about how Russian cyber attackers used security flaws to hack diplomatic targets in the US, I was particularly  fond of the statement:

The Microsoft problem by itself is less dangerous, since it
involves enhanced powers on a computer from those of an ordinary user.

This is really about all those IT expert wannabes that make the machines go faster by disabling all security enhancements, such as the User Access Control.

Imagine a Linux machine with no password for sudo/su commands. Or a Mac that doesn’t prompt you for the password when something is trying to change the system.

That’s pretty much the same.