Search results matching tag 'Compliance'

Is Microsoft SQL Server Supported By ...?

KKline — Tue, 19 Mar 2013 14:50:00 GMT

One of the types of question you get after speaking at a conference about virtualization, like I did at the 2012 PASS Summit with my buddy David Klee (Twitter | Blog), might go down like this:

"Is SQL Server version X supported on hypervisor platform Q?" or something even more specific like "Is SQL Server 2012 supported on VMWare vSphere ESX 4.1 Update 2? Or do I have to upgrade to ESX 5.0?".

Now, when I'm asked a question like this, I usually drool and act like an ape, hoping the the questioner will flee in terror. If they insist on hanging around to hear a real answer, I now refer them to the Windows Server Catalog site thanks to a tip from my NASCAR buddy and Microsoft MVP, Geoff Hiten (Twitter | Blog). For some reason, this very useful site is unknown to most - but it provides the most up-to-date and comprehensive information on what Microsoft supports. Once you've determined which hypervisor you want to check for support, you can simply search the site for your area of interest, say “Microsoft Server Virtualization Validation”.

Since Microsoft certifies OS's for virtualization platforms, you can by extension be assured that any supported application for that OS is also supported on that virtualization platform. So, to answer the early question about VMWare VSphere ESX, you'll find the entry for VMWare VSphere ESX 4.1 (Update 2) at http://www.windowsservercatalog.com/item.aspx?idItem=698bb582-b1ca-7124-05e4-256558d39e68&bCatID=1521.

Make sense?

Now, you know the rest of the story about why I drool and make monkey sounds at some conferences. That's my story, and I'm sticking to it.

-Kevin

Follow me on Twitter!

Let's Talk Licensing and Virtualization for SQL Server

KKline — Thu, 13 Dec 2012 19:23:00 GMT

I have two new articles up on Database Trends & Applications magazine. I'd love to get your thoughts and feedback!

Welcome to the Weird, Wild World of SQL Server Licensing

Not long in the past, SQL Server licensing was an easy and straightforward process. You used to take one of a few paths to get your SQL Server licenses. The first and easiest path was to buy your SQL Server license with your hardware. Want to buy a HP Proliant DL380 for a SQL Server application? Why not get your SQL Server Enterprise Edition license with it at the same time? Just pay the hardware vendor for the whole stack, from the bare metal all the way through to the Microsoft OS and SQL Server....

DBTA E-Edition - December 2012 Issue

Virtualization Conquers the Database

I was privileged to deliver a session entitled Managing SQL Server in a Virtual World at the PASS Summit 2012, the largest annual conference for Microsoft SQL Server. It was a packed house, literally at standing-room-only capacity. I delivered the session with my friend David Klee and we were swarmed by attendees after the session wrapped up. With almost 600 people in the room, we conducted one of those informal polls that speakers like to do along the lines of "Raise your hands if …" and the informal findings were very telling. Probably around 90% of the attendees used VMware and SQL Server in some capacity and at least 60% used it in production environments. Another important fact was that only 10% of the attendees were actually able to get information on the performance of the actual VMs themselves. Most had to get all of their information and support from the VM / System administration staff....

DBTA E-Edition - November E-Edition Issue

Follow me on Twitter!

Microsoft Document Watch for Operational Excellence

KKline — Mon, 08 Aug 2011 19:59:00 GMT

Back when my day-to-day duties included database administration work and enterprise architecture, I became rather obsessed with the idea of operational excellence. I read everything I could on the topic. I made a list of favorites, which became somewhat shabby over time, as I dog-eared important pages and scribbled notes in the margins. (Perhaps that list of favorites might, in and of itself, make a good blog post). Fast-forward a decade and I'm still mightily interested in operational excellence for IT organizations. It's just that so much good material is available for free on the web. Here's a run-down of several useful documents and downloads to improve overall operation performance for those of you in a Microsoft-centric IT organization:

Microsoft Operations Framework

Microsoft Operations Framework (MOF) version 4.0 guide is practical guidance for IT organizations. With the release of version 4.0, MOF now reflects a single, comprehensive IT service lifecycle—it helps IT professionals connect service management principles to everyday IT tasks and activities and ensures alignment between IT and the business.

Infrastructure Planning and Design

The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.

Microsoft Baseline Security Analyzer 2.2 (for IT Professionals)

The Microsoft Baseline Security Analyzer provides a streamlined method to identify missing security updates and common security misconfigurations. MBSA 2.2 is a minor upgrade correct minor issues and add optional catalog support.

Security Compliance Manager

The Microsoft Security Compliance Manager provides centralized security baseline management features, a baseline portfolio, customization capabilities, and security baseline export flexibility to accelerate your organization’s ability to efficiently manage the security and compliance process for the most widely used Microsoft technologies.

SQL Server Separation of Duties Whitepaper Released

Lara Rubbelke — Fri, 06 May 2011 16:17:00 GMT

Hot off the virtual press, the SQL Server Separation of Duties Whitepaper is now available! SQL Server 2008 R2 provides the full breadth of tools to support restrictive compliance and security requirements. This whitepaper will associate the features and options available in SQL Server 2008 R2 to meet the varying separation of duties requirements to enable the DBA to be productive and responsive while protecting applications and sensitive data.

Key concepts in the whitepaper are:

Applying Security Principles to the DBA (ie the DBA is NOT sysadmin!)
Protecting Database and Data Access
Authorizing Elevated Privileges for Specific DBA Task

I hope you enjoy the paper! :-)

You want to grant someone permissions to do WHAT?!?!

Lara Rubbelke — Mon, 24 Jan 2011 02:32:00 GMT

Have you ever heard of these types of requests? True story! I have had each of these and many more:

A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER.
A customer wanted to grant a user the rights to update a job – just one job – without any other changes to the job.
There was the case where a customer wanted to give a set of junior admins the rights to unlock a set of logins – without granting any additional rights to alter logins.
And of course, there are many, many customers who are facing internal and external regulations that dictate the DBAs should not have rights to view sensitive data. Period.

Managing security is never easy, and these additional requirements can cause a lot of distress to those who are trying to provide the right level of security while protecting their data, databases, and server infrastructure. Grant too many privileges, and you open up your environment to a host of potential issues. Grant too few privileges, and the users and administrators are unable to do their jobs.

Enter the Separation of Duties Framework. The Separation of Duties Framework was originally designed to address the separation of DBA from sysadmin, but this framework may also be used to temporarily grant users elevation of privileges in a controlled and auditable environment. The SQL Server Separation of Duties Framework will ease the process of setting up a restrictive environment while providing a predefined set of processes a DBA may use to manage restricted instances and sensitive databases. The Separation of Duties Framework is designed to empower the DBA team (or users) to be productive and responsive with processes that are auditable, secure, and extensible while being easy to implement and manage.

The Separation of Duties Framework was originally released in November 2010. Brian Davis (blog and twitter) and I just released v2.0 of the framework. The framework will create database roles, signed stored procedures, and the securables needed to support the environment. The framework is set up in the following steps:

Define the roles and tasks. Each organization will have different regulations that stipulate the security boundaries for individuals and groups. Prior to installing the Separation of Duties Framework, it is necessary to define the types of roles that will engage with SQL Server and the tasks that each role is permitted to execute.
Create folders to represent the defined roles. Create folders in a Procedures directory that will mimic the security roles you identified in the previous step. Remember that these folders are hierarchical, and each folder level will inherit the privileges of the parent folders. The Separation of Duties Framework will create roles based on the folder structure under the Procedures directory.
Add stored procedures sql files to the folders created in the previous step. Create procedures or use existing example procedures available in the framework that represent the tasks each role is allowed to execute. Place these in the appropriate folder which represents the users who are permitted to execute the task. The Separation of Duties Framework install script will create each procedure, sign the procedures with a certificate, and grant EXECUTE permissions to the appropriate roles.
Execute the PowerShell install script.
Place the appropriate users and groups into the newly created Database Roles.

More details on the installation process are available with the download. Brian Davis and I will also be following up with some additional blogs with details on the framework over the next few weeks.

Automating SQL Server 2005/2000 Policy Evaluation

Lara Rubbelke — Sat, 13 Jun 2009 16:11:00 GMT

The Enterprise Policy Management Framework version 3.0, a new version of the framework to support policy automated policy evaluation for SQL Server 2000 and 2005, has been posted to codeplex.

For those who are not familiar with the tool, the Enterprise Policy Management Framework is a reporting solution on the state of the SQL Server enterprise against a desired state defined in a policy. The key capabilities are to extend Policy-Based Management to all SQL Server instances in the enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will automate a scheduled evaluation of a set of policies against a group of servers, and provide reports for DBAs to understand where they have instances and database objects which are not complying with an organization’s defined standards.

The new 3.0 release includes the following enhancements:

Supports nested server groups in the Central Management Server

The previous versions did not support Central Management Server groups that were nested in parent groups. This restriction has been removed and you may now design CMS groups to fit your organization, and leverage these groups for the EPM Framework.

A new parameterized PowerShell execution

The PowerShell script has been updated with parameters. This enhancement will significantly ease how you may deploy the solution, so you only have a single script to manage. The previous versions would have required multiple versions of the PowerShell script you were to design the execution strategy by server group and policy category.

Policy results are stored in a table format

The new version 3.0 will shred the policy result XML document to a PolicyHistoryDetail table during the evaluation. The previous version only stored the XML data and issued queries against XML results stored in a SQL Server table named PolicyHistory. This update will greatly improve performance during reporting and provides a better platform for the community to build customized views and reports. This could also improve storage – you can purge the data in the PolicyHistory table if you do not require the XML results.

New Report Parameters

Based on feedback from the community, the new version includes parameters in the reports to support filtering by Central Management Server group. This will be a very important criteria for large organizations who would like to focus on specific groups of instances.

Fixes to error reporting logic

Not much to say, other than the logic that identifies errors stored in the tables is fixed.

Updated Documentation

The documentation has been updated, and should be much easier to follow when setting up the framework.

The EPM Framework leverages the Central Management Server, PowerShell, Reporting Services 2008, and Policy-Based Management. You will need at least one instance of SQL Server 2008 and an instance of SQL Server 2008 Reporting Services to support the framework. I will dive deeper into installation and configuration of the framework in subsequent blogs.

Please let me know if you are using the framework, and if you have suggestions for future enhancements. I am going to be integrating SQL Server 2008 Policy History centralization into the framework in the next version.

Achieving PCI Compliance Resources

Lara Rubbelke — Sun, 26 Apr 2009 14:36:56 GMT

This past week I was delivering some events for customers on supporting Mission Critical databases with SQL Server 2008. During the compliance conversation I mentioned that there were a couple of new resources available related specifically to PCI compliance.

Parente Randolph published a whitepaper on how to leverage SQL Server 2008 features to meet PCI compliance.

Furthermore, these auditors presented a TechNet webcast on the same subject.

I would also highly recommend the SQL Server 2008 Compliance Guide which will complement the above resources with technical implementation examples.

Another EKM Vendor Announced Support

Lara Rubbelke — Thu, 23 Apr 2009 03:05:00 GMT

This week Thales announced that their nCipher product line now integrates with SQL Server 2008 EKM. This announcement follows SafeNet as our second vendor to support EKM.

Which to use: "<>" or "!="?

AaronBertrand — Thu, 20 Mar 2008 10:51:00 GMT

For some, the answer is easy. Since Tibor reminded me several months ago that <> == standard and != <> standard (and yes, I used that notation on purpose), I have been making a conscious effort to use only <> going forward. Similarly, I have tried to avoid GETDATE() in favor of CURRENT_TIMESTAMP. (Of course this doesn't work in most of my systems where we use UTC, and occasionally get bitten by some sys admin accidentally switching a server to EST/EDT or to observe daylight savings time under GMT -- so there I always use GETUTCDATE()). While I have no intention of going back into old code just to change it, I have been making little updates here and there when I have been in there for other reasons...

In general, I am not immediately concerned about portability. Companies don't just change their database platforms overnight, and when they do decide to move, it's going to require a lot of changes. Certainly code is easier to change than data types and table structure, but little syntax things like this are still going to remain a small part of the puzzle for the majority of database applications I have observed.

I have no problem using IDENTITY (sorry Celko), UPDATE FROM (sorry Hugo), and little things like RAND() and NEWID() (I have no idea who to apologize to here). These offer me things that the standard can't... for example, in order to convert most of my UPDATE FROMs to standards-compliant UPDATEs, I would need to use a cursor instead of a set-based solution, and in several situations the performance would not be acceptable.

I also love some of the new syntax that is not supported elsewhere (e.g. DECLARE @foo INT = 5;) and MERGE (which has rough equivalents in some other vendors, and which adheres to the standard, but has extensions that don't)... I will not be afraid to use these when we move to SQL Server 2008. Others have expressed opposition to anything that deviates from the standard, but I believe that if the standard is limiting, and a vendor introduces something that makes our lives easier, why should I avoid it based on principle? Those concerned with portability can just make it a priority to avoid non-standard syntax... however, in any vendor implementation, all but the simplest database applications will undoubtedly have to deviate from the standard to some degree. In fact, I would wager that it is impossible to develop a real-world application of any complexity whatsoever, on a popular RDBMS (meaning Oracle, MySQL, SQL Server, DB2, etc), and adhere solely to ANSI-92. Part of the problem is that no vendor is 100% compliant, but even more so, all vendors have attractive and tempting alternatives that improve development, maintenance, performance, or all three.

Are there Microsoft deviations that don't make sense? Of course. The prime example that comes to mind is that Microsoft's implementation of TOP is horrible. And because they have stale tools that would take too much effort to change / replace, they are *still* inadvertently force-feeding this mythical idea that in order to save a view definition with an ORDER BY clause, all you need to do is add TOP 100 PERCENT to the SELECT list. And people are still surprised / angry that selecting from such a view without an order by clause on the outer query does not always return the rows in the order they expect. The OVER() clause is a better alternative to TOP in certain scenarios, but it is not complete (and won't be in SQL Server 2008, either).

We even start people off with the general idea that following the standard is not important. Look at MS Access, the starter database for most people in the Microsoft realm. It has very little ANSI compliance, and supports all kinds of Microsoft-specific extensions like FIRST(), VBA, macros, etc. And when moving to SQL Server, a database platform made by the same vendor, the level of effort required will often exceed that of a SQL Server -> Oracle or MySQL -> DB2 conversion. So it is not surprising to me that people generally have little concern for adhering to the standards, not just because in reality they rarely *need* to do so, but also because they've been "brought up" that way, so to speak.

Yes, best practice should dictate to follow the standard. But often it just doesn't seem worth it.

Anyway, back to the title. I am using <> now, to help ease the pain down the road, in the odd event that I write something that lasts longer than the company's commitment to the Microsoft platform. Though, deep down, I prefer != ... mostly because it seems less Mickey Mouse-ish. And by Mickey Mouse, I mean, of course, pre-.NET Visual Basic. :-)