Introduction Archives - Business Intelligence Info

Tag Archives: Introduction

Dynamics, Mixed Reality & HoloLens Part 1 – Introduction

January 11, 2020 Microsoft Dynamics CRM

Introduction

Welcome to the first of a multi-part series on Dynamics, Mixed Reality, and HoloLens. This series is aimed at current Dynamics 365 users that may not be familiar with Mixed Reality or how it can be used with Dynamics. In this post we’ll cover what Mixed Reality and HoloLens are, and introduce the Dynamics Mixed Reality apps. In later posts we will dive into each application in more detail and discuss uses cases for each. Let’s get started.

If you’re like me, you were first introduced to virtual reality many years ago through movies like the Lawnmower Man. The idea of immersing yourself in a totally different world was fascinating and easy to imagine. A few years later I started hearing the terms Mixed Reality and Augmented Reality, and like a lot of people I just lumped them in with Virtual Reality.

Then, Augmented Reality glasses came along that projected some 2-dimensional contextual information on to your real field of view, and I thought that’s what Mixed and Augmented Reality must be.

A few years later I heard about this thing called the HoloLens and I assumed it was Microsoft’s version of Google Glass. There was talk of holograms, but that had to be a gimmick right? Holograms are only in movies and cool stickers right? It wasn’t until I had the opportunity to try a HoloLens that it all clicked. In an instant I knew I was experiencing a completely new and transformative technology. I also now understood the true meaning of all three reality terms, which I’ll get to.

At that time I had recently started working at Microsoft as a Dynamics 365 Premier Field Engineer (great job for a great company). It seemed worlds apart from HoloLens, and I couldn’t exactly run to Best Buy and pick one up, so I waited. Then finally, about a year ago, those two worlds started to merge. Microsoft released its first set of Dynamics applications for Mixed Reality and HoloLens. I was able to acquire a HoloLens 1 (there’s a 2 now) and began exploring.

So why am I telling you all this? I’m hoping you can relate to my journey to Mixed Reality enlightenment and that it might help you gain some clarity as well.

What are all these realities?

If you find the various reality terms confusing, you’re not alone. Let’s quickly examine the three main terms in this space – Virtual Reality (VR), Augmented Reality (AR), and Mixed Reality (MR).

VR is fully immersive, meaning you can’t see anything in the real world. This is great for gaming or taking a tour of Venice, but not so great if you need to walk around or work in a real space.
AR overlays text and images on your real world environment, but is not spatially aware and therefore cannot “mix” with or “anchor” to the space you’re in. I like to think of it like a heads up display. Something like what the Terminator or a fighter pilot might see.
MR, which is often confused with AR, takes things to another level. It “mixes” virtual 3-dimensional objects (holograms) with objects in the real world allowing the two to coexist and interact. It’s spatially aware and allows you to walk around and interact with these objects as though they were really there. Place a model on your floor, walk out the front door, come in the back door, and your model stays right where you left it.

Mixed Reality and Augmented Reality are often used interchangeably so it’s easy to get confused, but they are not the same thing.

On a side note, recently one of my kids’ cereal boxes had a cardboard cut out on the back to make “Actual Reality” glasses, which gave us a good laugh.

What is HoloLens?

HoloLens is a self-contained, untethered MR headset with a holographic processor that melds 3-dimensional holograms seamlessly into the real world. No phones, additional computing, or cords required. The lens is clear so you can see everything around you, and by using hand gestures and/or voice commands you can open applications, anchor browser windows and documents on walls, place 2-way video calls, and place 3-dimensional, holographic objects on tables or floors and interact with them. Think of Tony Stark in his lab modeling new elements and Iron Man suits.

4863.pastedimage1578085670981v1 Dynamics, Mixed Reality & HoloLens Part 1 Introduction

There’s no substitute for trying one, but a video is the next best thing. This introductory video should help connect the dots and leave you wanting to see more.

[embedded content]

So What’s The Big deal?

Who cares if you can see things that aren’t really there? Well that’s exactly the point. Now you can work with people and objects as if they really were there. It literally adds a new dimension to computing by moving things off your screen and fusing them with the real world. We can learn, teach, design, build, repair and play in entirely new ways. This technology has the potential to transform how we do business. To get your creative juices flowing, here are some practical examples of what you can do with MR:

Medical – Imagine practicing medical procedures on detailed, life-size, 3-dimensional models of the human anatomy with interactive guidance from a doctor, without an actual human subject.
Assembly and maintenance of equipment – A field tech can refer to documentation, view step by step instructions with animations, see markers on the actual equipment, and receive remote guidance, while keeping their hands free throughout.
Construction & Architecture – Imagine being able to walk through a detailed, life-sized model of a building on the actual jobsite before construction even starts. Imagine walking through a building in progress with the ability to see what’s right, what’s wrong, and what’s left to be done while your team watches and comments remotely.
Design & Layout – Imagine walking through your new warehouse or factory (or that of a customer) and being able to view and rearrange life sized fixtures and equipment in the actual space. Imagine seeing how 10 different couches look in your living room, without leaving your living room.

You can start to see the possibilities. Use of MR in training & education, field service, construction, logistics, Inspections, sales, and other areas has the potential to reduce costs, boost productivity, and improve customer service.

Dynamics, Mixed Reality & HoloLens Part 1 Introduction

What Does This Have to do with Dynamics?

When you consider the use cases cited above and the fact that Dynamics sits at the core of sales, customer service, and field service operations for many companies, it is the ideal launch pad for commercial applications of MR. The introduction of a set of MR applications for Dynamics has made it easy for companies to start taking advantage of MR now, without custom development or a huge investment.

With a little bit of setup, resources in the field can access their service calendar, view products and equipment, launch interactive guides, and get expert remote assistance. There are currently four Mixed Reality Applications for Dynamics:

Remote Assist

Through integrations with Dynamics and Microsoft Teams, resources in the field can view their service calendar and place 2-way video calls for remote assistance. Off-site experts can see what the onsite resource sees, and even place anchored holographic arrows and other markers into the workspace to help diagnose and repair issues. You can place your best techs anywhere in the world in an instant, reduce travel costs, and improve customer service.

https://www.youtube.com/watch?v=J-C6GE2gFYw

Guides

Administrators can create step by step guides for resources in the field, without custom code, to walk them through procedures. Guides can contain text, videos, and holograms for each step and can be linked directly to services in Dynamics. Using HoloLens a field resource can select a service appointment from their calendar, open the associated guide, and step through the procedure, keeping their hands free the whole time. Train your team and complete service calls in less time by keeping their eyes and hands on task.

https://www.youtube.com/watch?v=V8c3pDKdHEc

Layout

Layout allows you to pull life-size 3-diemsional objects into your space, move them around, measure distances, and then actually walk through the space. You can use predefined layouts or let the HoloLens map the space you’re in.

https://www.youtube.com/watch?v=9viR6U-D2Co

Product Visualize

Product Visualize allows you to see life-size, 3-dimensional objects in your real space using a phone or tablet running iOS. It does not use HoloLens, but is a useful sales tool that provides a good introduction to MR using devices that many field resources already have.

https://www.youtube.com/watch?v=lk2HzxZfkW4

For more details on these apps you can visit https://dynamics.microsoft.com/en-us/mixed-reality/overview/

Summary

One of the early challenges with Mixed Reality is being able to apply it in practical ways without spending an arm and a leg on custom development. With the introduction of these tools, Microsoft has done a great job of packaging up the core value proposition in a way that makes it accessible to everyone. Do these applications meet every advanced MR need? Hardly. We are only just scratching the surface. But if you’re a Dynamics customer with resources in the field, these solutions deserve serious consideration.

If you’d like to hear more, follow this blog and watch for part two of the series where I’ll cover Remote Assist. Feel free to post comments and questions below as well. Thanks for reading. Now back to reality.

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Introduction to DevOps: 10 Guidelines for Implementing DevOps

November 19, 2019 BI News and Info

The series so far:

Throughout this series, I’ve touched upon various aspects of the DevOps application delivery model, covering such topics as the delivery pipeline, security as an integrated process, and the importance of incorporating database deployments into application delivery. This article ties up the series by providing ten important guidelines for implementing DevOps in your data center. Although each DevOps environment is unique to an organization—addressing its specific needs and circumstances—these guidelines can offer a starting point for planning and implementing your DevOps strategy, discussing many of the considerations to take into account along the way.

1. Build an effective DevOps culture.

You might be tempted to view this first guideline as nothing more than introductory fluff to round out the article, and certainly many teams implement DevOps before fully appreciating the value of an effective culture. Failure to establish and encourage the right culture will inevitably translate to an excruciating and inefficient DevOps process or, worse still, a complete operations meltdown.

Implementing DevOps in any organization requires a shift in thinking, a new mindset that values communication and collaboration over inflexible roles and siloed teams that can’t see beyond their own borders. Those who participate in the DevOps process must be willing to work together and recognize that they’re accountable for application delivery from beginning to end and that they have a stake in the outcome.

To establish such a culture, you need a firm commitment from management and other leadership that clearly demonstrates a willingness to dedicate the time and resources necessary to establish and encourage transparent communications, information sharing, cross-team collaboration, and a general attitude that application delivery is everyone’s responsibility.

2. Take baby steps when getting started.

DevOps is not an all-or-nothing proposition. You do not need to revamp your entire operation overnight. Forget about the massive implementation scheme and instead take small steps toward achieving your goals. A DevOps implementation requires thorough planning and careful rollout while taking into account business needs that can evolve over time. For this, you need plenty of leeway.

You should be thinking long-term toward full implementation and not try to accomplish everything in a couple of months, especially for a large-scale operation. Rushing a DevOps implementation can be as big a mistake as failing to establish the right culture. You need time to assess requirements, train participants, choose the right tools, and deploy the infrastructure. Trying to implement DevOps before you’re prepared can result in buggy applications, compromised data, and a lot of wasted time and money.

When planning your DevOps implementation, it’s better to start small than risk the entire operation. For example, you don’t need to automate every task at once or move all your applications to DevOps at the same time. You can start by automating one or two processes or by developing smaller, less critical apps. After you’ve succeeded with one phase, you can then move on to the next.

3. Plan and document your development projects.

For any DevOps project, your development process should start with a thorough planning phase to ensure that development efforts run efficiently, remain on schedule, and come in under budget. All teams involved with application delivery—including development, testing, and operations—should participate in the planning process.

As part of this process, you should set realistic milestones, taking into account the time necessary to implement new tools and infrastructure and to allow those who are new to DevOps to adjust to a different way of working. Development efforts should focus on small, incremental changes, with more frequent release cycles. This approach can lead to releases that are more reliable and predictable while helping to avoid issues that can complicate and disrupt the application delivery process.

In addition, DevOps teams should document all necessary information throughout the planning and application delivery processes. Proper documentation is crucial to establishing a culture of collaboration and communication. It helps team members understand the systems, what has changed, what caused specific issues, and how to resolve those issues. Detailed documentation can also help improve subsequent release cycles for the current project and better streamline operations for future projects.

4. Take a security-first approach to DevOps.

Security, compliance, and privacy should be factored into your DevOps processes from the beginning and continue through all phases of application delivery, whether planning your applications, setting up infrastructure, writing code, or deploying to production. Security should not be treated as an afterthought, or a segregated phase squeezed into the application delivery process right before deployment, or worse still after the application goes live. Security must be integrated into all phases, implemented continuously, and treated as a priority by all team members.

As with application development, automated testing can be integral to ensuring that data remains secure and protected, with checks performed during each release cycle. If a test exposes a potential issue, it can be tagged for a security review, and the issue addressed quickly by developers before that application has an opportunity to find its way into production. In addition, peer code reviews should look for potential security and compliance issues, along with application-specific concerns.

DevOps teams should also take the steps necessary to secure the DevOps environment and processes themselves, such as storing all code in a secure source control repository, isolating the DevOps systems in a secure network, verifying all third-party code, or adhering to the principles of least privilege. There are, in fact, several best practices an organization should follow to implement continuous DevOps security, and teams must commit to ensuring those practices are always being followed.

5. Implement a code management strategy.

All DevOps teams should be using a source control solution that versions and protects files. The solution should provide a single source of truth for all files and ensure that they have a record of who changed what while providing easy access to any specific version of the application. But version control alone is not enough. You must also ensure that you check in all relevant files—not only application code, but also configuration and change scripts, test scripts, database deployment scripts, reference data, and any other files relevant to application delivery.

A code management strategy should also address how to handle branching, which lets developers work on different features simultaneously, without stepping all over each other. You’ll have to determine the best approach to branching based on your organization’s requirements. Just be sure that the source control solution you choose contains branching tools that are robust and sophisticated enough to ensure that branching works in your favor, rather than complicating operations.

You should also take into account other considerations when planning your code management strategy. For example, you might want to implement a policy that requires developers to check in their code at least once a day, or more often if necessary. Infrequent check-ins can complicate operations unnecessarily and slow down the development effort. Also, be sure that source code and other critical files are not being managed directly on local workstations or network shares. Again, everything should be in source control.

6. Automate, automate, automate.

For DevOps to be effective, you must automate as many operations as possible and practical. The more operations that can be automated—especially the mundane, time-consuming, repetitive ones—the more efficient the overall process and the fewer the risks. Where possible, avoid manual one-off tasks in favor of repeatable operations that can be automated (keeping in mind the second guideline about taking baby steps).

You won’t be able to eliminate all manual processes, but try to make them the exception, rather than the rule. Manual processes can slow development efforts or bring them to a halt, such as when a developer stops working on code to set up a virtual machine or implement a database environment.

Most standardized DevOps operations can now be automated, helping to improve efficiency and speed up application delivery. An automated operation can be used multiple times by multiple team members in multiple circumstances. In addition, the operation can be altered and improved to address changing business requirements. Test automation is a good example of this. You can write unit tests that kick off automatically when updated code is checked into source control. The tests can run as often as necessary, and they can be updated as needed to accommodate new requirements.

7. Think continuous everything.

In the world of DevOps, application delivery is not a one-time operation but rather an ongoing process that makes it possible to continuously update and improve the application until it reaches the end of its lifecycle. As with automation, continuous practices must be ongoing and deeply integrated into the DevOps environment. DevOps is not so much a linear process as it is a continuous flow of consecutive iterations that last until the application is no longer being actively developed or maintained.

Discussions about the continuous nature of DevOps often focus on continuous integration, delivery, and deployment because of the pivotal roles they play in defining the DevOps pipeline. Continuous integration, for example, makes it possible for developers to check in frequent code changes and know that those changes are automatically verified so they can be immediately incorporated into the codebase.

But continuous integration does not operate in a vacuum. It goes hand-in-hand with continuous testing, which validates the code by running automated tests that have been predefined to look for specific issues, making it possible to identify problems early in the development process, when they’re much easier to address. Also important to continuous integration—and the DevOps environment in general—are continuous security, monitoring, and feedback, which ensure that projects stay on track, DevOps processes work efficiently, and sensitive data is not put at risk.

8. Make quality assurance a priority.

One of the foundations of effective QA is automated, continuous testing that’s integrated into the DevOps pipeline. The software should be tested at each phase of the application development process, with development and testing done in tandem, beginning with the first code check-in. In addition, the testing strategy should incorporate the environment in which the application will be running, such as verifying that the correct software versions are installed or that environmental variables are properly configured.

Other factors critical to effective QA are continuous monitoring and feedback. You must be able to track the application’s health in order to identify any issues with the application or the environment in which it runs, even as the application scales. You should also be tracking the DevOps infrastructure itself in order to optimize application delivery and alert the team to any issues in the delivery process.

DevOps teams should consider using key performance indicators (KPIs) that measure such metrics as failure rates, time to resolution, completed operations, incomplete tasks, milestones accomplished, or any other factors that can help the team understand and improve operations and the application. Teams might also consider automatic dashboards that provide real-time insights into the environment and development efforts. When planning your DevOps infrastructure and projects, be sure you include QA experts to ensure that no aspect of QA is being overlooked.

9. Manage your environments.

Application planning must take into account the environments in which the application will be developed, tested, staged, and deployed to production. For example, you’ll likely need separate environments for developing an application so developers can work on different parts of the application without conflicting with each other. The same goes for testing. Different environments are usually needed to ensure the accuracy of the testing processes. Then there are the environments needed for staging and deploying the application, which can vary depending on the deployment model.

To address environmental requirements, many DevOps teams are now implementing infrastructure as code (IaC), a process of automating infrastructure creation. To incorporate IaC into your DevOps processes, you start by writing scripts that define the infrastructure necessary to support your application. For example, a script might provision a virtual machine, configure its operating system, install a database management system, apply security updates, and carry out several other operations.

With IaC, the application code and configuration scripts are linked together, rather than the application being tied to a single machine or cluster. The configuration scripts run automatically whenever the application is deployed. IaC ensures that the application environment is always the same, no matter where that environment runs while eliminating the need to set up environments manually. IaC also allows you to create as many temporary environments as necessary to support the application development process while ensuring that everyone is working in the same environment.

10. Choose the right tools and technologies.

To implement a successful DevOps environment, you need tools that increase efficiency and simplify tasks. But keep in mind the second guideline about taking baby steps. You don’t need every DevOps tool out there, and you don’t need to implement every chosen tool at once. Select your tools carefully, looking for those that integrate easily with other systems, facilitate automation, foster communication and collaboration, and provide visibility into your DevOps environment and processes.

A DevOps environment can require a wide range of tools. A source control solution is, of course, a given, but you’ll also need tools for automating infrastructure, monitoring systems and applications, integrating security, tracking tasks and development cycles, managing database releases, and carrying out several other processes. Fortunately, many vendors now offer solutions that support DevOps application delivery, but they differ, so you’ll need to evaluate them carefully.

As part of this process, you should take into account the architectures and technologies you’ll be employing. I’ve already pointed to IaC as an essential strategy, but there are other technologies that can also be effective in supporting DevOps, such as containers or microservices, all of which require their own set of tools. In addition, you should evaluate the extent to which you might be using cloud technologies to augment or host your DevOps operations and how to integrate systems between platforms.

Tailor-made DevOps

There are, of course, other considerations than what I’ve discussed here, and those that I have discussed could easily justify articles of their own. Even so, what I’ve covered should help you get started in planning your DevOps strategy and provide an overview of some of the factors to take into account as part of that process.

Keep in mind, however, that each DevOps implementation is unique and should be tailored to your organization’s specific needs and circumstances. Although DevOps has proven a valuable strategy for many organizations, there is no one-size-fits-all approach to implementing DevOps, and even if there were, there would be no guarantee that each implementation would work exactly the same in all circumstances. A DevOps environment should be designed to fit your organization’s requirements and improve application delivery, not make the process more difficult for everyone involved.

Let’s block ads! (Why?)

SQL – Simple Talk

Introduction to DevOps: Security, Privacy, and Compliance

September 22, 2019 BI News and Info

The series so far:

A DevOps approach to application delivery offers a number of advantages over traditional methodologies, most notably faster and more efficient deployments, yet there’s nothing inherent in DevOps that promises more secure delivery or better protection for the applications and their data. In fact, DevOps can potentially lead to greater risks if security is not integrated into the development process right from the start. In today’s word of escalating cyberattacks and increased privacy regulations, security should be as much a part of application delivery as continuous testing and ongoing integration. Anything less could put your entire organization at risk.

DevOps Security Challenges

Cyberattacks are on the rise, and they’re becoming more sophisticated every day. If data is compromised, the consequences can have far-reaching implications, not only to the bottom line, but also to an organization’s long-term reputation.

Hackers know where to find security holes, how to exploit them, and what rewards they might be able to reap. They also recognize the opportunities that can result from accelerating software delivery without taking special care to protect applications and their data. Development teams that implement DevOps methodologies need to be particularly vigilant to guard against the many security challenges that come with delivering applications at a high velocity.

One of the biggest hurdles to security in a DevOps environment is the failure to factor in security right from the start—when planning and designing the application. Instead, security is treated as an afterthought, a clean-up operation, a reactive process separate from the primary development effort, resulting in slower application delivery or applications being released before they’ve been fully vetted.

Part of the reason for this disconnect is that traditional methods for ensuring application and data security do not fit easily within DevOps processes and, in fact, can inhibit operations. In addition, development and security teams often assume that, even with DevOps, it’s business as usual when it came to security, with the two teams operating independently of each other, often without coordinating efforts. The DevOps team will deliver the application, and the security team will identify and address any security issues, kicking the application back to the developers if it becomes necessary.

Unlike development and operations, where efforts merge together in a more organic fashion, development efforts can often seem at odds with the time-consuming processes that come with ensuring application security. On some DevOps teams, there can be a cultural resistance to security for fear it will slow down application delivery, an attitude often coupled with a general resistance to changing the way security is traditionally handled.

At the same time, DevOps teams are under incredible pressure to deliver application updates as fast and as frequently as possible, with upper management demanding that nothing slow down operations. Many believe that addressing security issues early in the development process will throw a roadblock in the way of their efforts.

In some organizations, there’s also uncertainty around who’s responsible for which aspects of security, especially if there’s no established security team. At the same time, some DevOps team members might lack adequate training in security, leaving them ill-equipped to deal with today’s increasingly sophisticated threats.

On top of everything else, a team might utilize risky application technologies without fully addressing potential risks. For example, developers might incorporate newly released open-source code, expose application programming interfaces (APIs) that are not fully secure, or deploy containers without proper controls. They might also introduce risks into the code through poor practices or human error. Without formal security checks in place, those risks can go unnoticed until well into the development process or until the deployed application comes under attack.

Compliance and privacy

Discussions around application security must inevitably include issues related to compliance and privacy. Stricter regulations, increased liabilities, and heightened public awareness mean that protecting personally identifiable information (PII) is no longer a luxury, but rather a requirement and priority. If your application lacks the security necessary to protect PII, your organization risks steep fines, costly litigation, and a violation of public trust, from which you might never recover.

To make matters worse, regulations can vary significantly from one region to the next, adding to the complexity of ensuring compliance throughout the data’s lifetime. And some of those regulations can have far reaching implications. For example, the General Data Protection Regulation (GDPR) went into effect in the European Union (EU) in May 2018, and one year later, the industry is only now starting to feel its full impact.

In that time, there’s been a massive increase in the number of reported data breaches. Prior to that, the EU had no single mechanism to support notifications, and it’s believed that many breaches went unacknowledged. But much has changed with the GDPR, which requires organizations to report breaches within 72 hours of being discovered. In addition, the regulation more clearly defines what constitutes protected data, provides individual users with greater control over their data, and regulates how organizations can use that data.

Organizations that fail to comply with the GDPR can face stiff penalties—up to €20 million or 4% of their worldwide annual revenue of the prior financial year, whichever is higher. Google, for example, was fined €50 million in January 2019 for GDPR violations, representing the majority of fines collected in the first year of GDPR enactment.

Compliance laws, particularly the GDPR, can also cause a butterfly effect, which refers to the law’s wider impact across the globe. One way this is being felt is in the enactment of other regional laws that take their cue from the GDPR in defining user protections and corporate responsibilities. The GDPR can also impact organizations outside the EU that do business either directly or indirectly with the EU, affecting their operations, workloads, and data protection strategies, wherever that data resides.

Organizations that face compliance laws in multiple regions might choose to adhere to the strictest requirements for all their data in order to ensure compliance, so if the GDPR represents the severest of the laws, the organization might follow those standards for all PII, wherever it resides. For development teams, this means ensuring that data is protected during all phases of the DevOps process, and the best way to do that is to build security into the application delivery pipeline right from the start.

Incorporating security into DevOps

A secure DevOps environment incorporates the tools and processes necessary to protect the application and its data at every stage of the operation. Security starts with the planning and design phases and continues through the entire application lifecycle, baked into the continuous integration and delivery processes. In this sense, security merges with development and operations to create a cohesive three-pronged approach to application delivery, as shown in Figure 1.

word image 8 Introduction to DevOps: Security, Privacy, and Compliance

Figure 1. Incorporating security into the DevOps process

Secure DevOps calls for a shift left in thinking that pushes security to the beginning of the application delivery pipeline, rather than dangling it out at the end, where it can be the most disruptive to application delivery. Instead, security is built into the planning, development, testing, deployment, and operation phases.

As with other DevOps processes, the goal is to automate security wherever possible, using testing and monitoring to ensure the proper implementation of comprehensive protection policies. In this way, potential risks can be better identified and mitigated early in the development process, while ensuring the continued velocity of the DevOps process.

Implementing secure DevOps starts with a change in mindset toward security and its role in application delivery. Critical to this attitude are support and buy-in from upper management and other stakeholders. Secure DevOps calls for open collaboration, greater transparency, and a shared set of objectives, along with the appropriate use of automation, proper tooling, and advanced application technologies such as infrastructure-as-code.

Secure DevOps, sometimes referred to as DevSecOps, is as much a process as a philosophy, one that sees security as a shared responsibility by everyone involved in application delivery. DevSecOps bridges the gap between development and security teams, helping to reduce the contention that can sometimes occur with traditional approaches to security.

By embedding security processes directly into the application delivery pipeline, DevSecOps offers early and continuous risk management through testing and monitoring, which provide in-depth insights into possible vulnerabilities.

Secure DevOps can also promote a culture of security, encouraging team members to be mindful of the importance of data and application protection at all times. With DevSecOps, developers are more likely to consider security when planning and building applications, QA professionals are more likely to take security risks into account when testing applications, and operation specialists are more likely to adhere to security best practices when deploying application infrastructures.

DevSecOps also leads to more secure and compliant applications because continuous testing and monitoring are integrated into the pipeline at a granular level, making the process more efficient at identifying and limiting threats and compliance violations. With DevSecOps, security becomes a natural part of the application design and fully integrated into the entire delivery process.

Secure DevOps can also make application delivery faster and, as a result, cheaper. Security flaws identified early in the design and development cycles can be addressed much more efficiently than if identified later in the process. As with any bugs, the sooner you find security issues, the quicker and easier they are to fix.

The DevSecOps approach also makes it possible for teams to respond more quickly to changing requirements and circumstances because security is baked in from the start, rather than ladled on top of the application at the end. This integration can also translate to a better user experience, in part because of security is built in, but also because feature updates and application fixes get to users faster.

Implementing secure DevOps

The logistics of integrating security in DevOps will vary from one team to the next, but most teams will follow several basic practices, some of which they might have already incorporated into their infrastructures. For example, in a DevSecOps environment, all source code should be stored in a secure version control repository, leveraging such technologies as encryption and digital signing.

The secure DevOps team should also practice the principle of least privilege so that only authorized individuals can access system resources, with access specific to their job roles. In addition, developers should work with code in small chunks so it’s easier to identify vulnerabilities, and all code should undergo peer reviews, including any scripts used to build infrastructure or deploy applications. The code reviews should look specifically for security risks, compliance violations, and adherence to best security practices.

As with DevOps itself, automation is key to successfully implementing DevSecOps, which requires tools that can seamlessly integrate into the continuous integration and delivery processes. For example, an organization might use Puppet for its automation framework and use Splunk in conjunction with Puppet to provide the analysis necessary to determine when a configuration goes out of compliance. To provide masked copies of databases for development and testing, organizations might use an automated solution like SQL Provision.

An organization might also use a monitoring tool such as WhiteSource, which detects open source components in the code and alerts team members to security risks and software bugs. There are plenty of other tools out there as well, which can help address security throughout all phases of application delivery—and more tools are emerging every day.

Continuous testing is a critical component of automating security, and there are many types of tests that can be performed. For example, DevOps teams might use static application security testing (SAST) to automatically analyze code for security vulnerabilities, without running the application. They might also use dynamic application security testing (DAST) to analyze the application for vulnerabilities when it’s running. Teams might also utilize penetration tests, threat investigations, vulnerability assessments, data flow analytics, and numerous other strategies to help ensure security and compliance.

Just as important as testing is continuous monitoring, which automatically tracks security-related metrics throughout the application delivery lifecycle. The collected information should reflect a wide range of information for identifying security issues. It should also include a system for alerting team members when issues are discovered, such as when code fails a compliance test. In addition, the monitoring solution should offer systemwide information that provides team members with a security overview as well as specifics that might be required for a security or compliance audit.

Moving from DevOps to DevSecOps

Although security might seem at odds with DevOps methodologies at first glance, incorporating security into the DevOps process is an inevitable outcome of today’s sophisticated cyberattacks and expanding privacy regulations.

Organizations that fail to incorporate a DevSecOps mindset are likely to lose ground when trying to implement safe applications that meet the necessary security and compliance standards. For most teams, incorporating security in the early stages of application development is the only viable option in a world of increasing threats, stiffer penalties, and greater risks to the organization’s reputation and the public’s trust.

Let’s block ads! (Why?)

SQL – Simple Talk

Introduction to DevOps: DevOps and the Database

August 10, 2019 BI News and Info

The series so far:

DevOps has been gaining steady ground in the world of application development, with ongoing improvements in delivery processes and the tools needed to support them. For the most part, however, DevOps has focused primarily on the application itself, with databases left to their own devices, despite the integral role they play in supporting many of today’s data-driven workloads.

According to the 2018 Accelerate: State of DevOps report, “database changes are often a major source of risk and delay when performing deployments.” But the report then states that integrating databases into the application delivery process could have a positive impact on continuous delivery. The key, according to the report, is good communication and comprehensive configuration management that incorporates databases. In other words, database changes should be managed in the same way as application changes.

Redgate’s 2019 State of Database DevOps report adds to these findings with their own survey results. According to the report, 77% of the respondents claim that their developers work across both databases and applications, and 75% say that developers build the database deployment scripts. However, 63% acknowledge that DBAs are responsible for deploying database changes to production.

The report also states that the two most significant challenges to integrating database deployments into the DevOps process are “synchronizing application and database changes” and “overcoming different approaches to application and database development.” In contrast, the two biggest reasons for integrating databases into DevOps are “to increase speed of delivery of database changes” and “to free up developers’ time for more added value work.” More importantly, 61% believe that such a move would have a positive impact on regulatory and compliance requirements.

Clearly, integrating database deployments into the DevOps process remains a challenge for many organisations, yet there’s a growing recognition that databases need to be brought into the DevOps fold. And in fact, this is beginning to happen, as more organisations incorporate database deployments into their integration and delivery processes. Not only does this promise to help improve application delivery, but it can also benefit database development itself.

The DevOps Promise

When application and database development efforts are more tightly integrated—with teams working in parallel and sharing a common process—they can release builds that are more consistent and stable, while reducing unnecessary risks and delays. The teams use the same infrastructure to develop and deploy application and database code, making it easier to standardise processes and coordinate efforts.

In a more traditional development environment, database changes can hold up application delivery, impacting productivity and costs. By incorporating databases into DevOps, database deployments can benefit from the established processes, while more tightly integrating database and application development.

A DevOps approach to database development also makes the process more agile and adaptable. Teams implement smaller, more frequent changes as part of a coordinated effort while receiving continuous feedback on the delivery processes and application components. In this way, database development and deployment are no longer disconnected from the larger delivery workflow but become integrated into the process throughout the application lifecycle.

The DevOps Challenge

As good as database DevOps might sound, there’s a good reason that organisations have been slow to jump on board. Transitioning from a siloed-database model to a DevOps-database approach is no small effort.

To begin with, databases were never part of the original DevOps vision. Processes and tools were developed for application code and application deployments, not for the peculiarities of database management. It’s only been recently that databases have been incorporated into DevOps processes in a meaningful way.

Database tools and operations are very specific to database development and management and the environment in which those databases reside. In the early days of DevOps, many viewed the database as existing in a separate world, and both sides of the aisle preferred to keep it that way.

One of the significant differences between application and database deployments comes down to data persistence. With applications, it’s much easier to update or replace code because there’s no data to manage along the way. With databases, however, you can’t simply overwrite schemas or copy changes from one environment to another without pulling the data along with you. Careful consideration must be given to how changes impact existing data and how to preserve that data when changes are made. Otherwise, you risk data loss, integrity issues, or privacy and compliance concerns.

Databases also bring with them size and scalability issues that differ from application code. Implementing schema changes on a massive database in a production environment can be a significant undertaking and lead to performance issues and degraded services. Database changes become even more complicated if the database is being accessed by multiple applications, especially if no single application has exclusive ownership.

Perhaps the biggest hurdle to implementing database DevOps has been a siloed mindset that makes it difficult for various players to come together in a way that makes integration possible. According to the Redgate report, only 22% of the respondents said that their developers and DBAs were “great” at working together effectively as a team, and without a team-focused attitude, an effective DevOps strategy is nearly impossible to implement.

Despite these challenges, however, database DevOps has made important inroads in recent years. DevOps tools now better accommodate databases, and database tools better integrate with DevOps systems. Attitudes, too, are beginning to change as more participants come to understand the value that incorporating databases can offer. Although database DevOps is still a challenge, many teams have now demonstrated that application and database delivery can indeed be a unified effort.

Moving Ahead with Database DevOps

To make DevOps work, the database team must adopt the same principles and technologies as those used by the application team when delivering software. Figure 1 provides an overview of what the database DevOps process might look like when integrated into the application delivery pipeline.

word image 41 Introduction to DevOps: DevOps and the Database

Figure 1. Integrating database deployment into the DevOps process

The first step is to store all database code in source control (also referred to as version control). A source control solution provides the foundation on which all other DevOps operations are based.

With source control, every developer works from the one source of truth, resulting in fewer errors and code conflicts. Source control also tracks who made what changes and when those changes were made. In addition, source control maintains a version history of the files, making it possible to roll back changes to previous versions. Source control also makes it easier to repeatedly build and update a database in different environments, including QA and development.

Database teams should store all their database code in source control. This includes the scripts used to build the databases as well as the migration (change) scripts that modify the database or data. For example, source control should be used for scripts that create, alter, or drop database objects—such as tables, views, roles indexes, functions, or stored procedures—as well as any scripts that select, insert, update, or delete data. The teams should also store static data, such as data used to populate lookup tables, as well as configuration data, when appropriate.

Once all the code and data are in source control, the database deployment operations can be incorporated into the same DevOps continuous integration (CI) and continuous delivery processes used for application deployments. When a developer checks in database code, it triggers an automated build operation that runs alongside the application release process, making it easier to coordinate database and application deployments.

When code is checked into the source control repository, the CI service kicks in and runs a series of commands that carry out such tasks as compiling source code and running unit tests. If a database is part of the deployment, the CI service tests and updates the database—or alerts developers to errors. If problems are discovered, developers can fix them and resubmit the code, which relaunches the CI process.

To incorporate databases into CI, a team might turn to a tool such as Redgate’s SQL Change Automation, which can integrate with any CI server that supports PowerShell. Redgate also provides extensions for CI products such as Jenkins, TeamCity, or Azure DevOps for enabling database integration.

When the CI service processes the SQL code, it produces an artifact that includes the deployment script necessary to update the applicable database objects and static data. The artifact also contains details about the deployment process, including a diff report that shows what has changed in the database. The artifact represents a specific, validated version that can be used as a starting point for the database release process.

At this point, the artifact is deployed against a staging database that is ideally an exact copy of the production database. Here DBAs can verify and confirm the changes to ensure the staging database is production-ready. If necessary, they can also make additional changes. Out of this process, a final deployment script is generated, which can then be passed down the pipeline for deployment against the production database.

Not surprisingly, the exact approach to database delivery is much more involved than what I’ve described here, especially when it comes to migration script versioning, and that process can vary significantly depending on the tools being used and how those tools are configured.

What this does demonstrate is that database DevOps is indeed doable, providing database teams with a process that automates repetitive deployment and testing operations. With DevOps, teams can deliver smaller releases that are easier and faster to deploy, while having in place a consistent, steady mechanism for ongoing database deployments that work in conjunction with application delivery.

Testing and Monitoring

One of the most important aspects of the application delivery pipeline is ongoing testing. Changes to the database should undergo rigorous testing before the release reaches the staging environment. In this way, developers learn of problems quickly and early in the development process. When issues are discovered, they’re immediately alerted so they can check fixes into source control and keep the development effort moving forward.

Developers and DBAs should invest the time necessary to write comprehensive tests that provide for as many scenarios as possible. Many of these tests will be based on frameworks specific to database testing. For example, tSQLt is a popular open-source framework for SQL Server that lets you write unit tests in T-SQL. Regardless of the framework, the goal is the same: to identify as many issues as possible before database changes make it to production.

Unit tests offer your first line of defence against problem SQL code. Because they run when the code changes are checked into source control, they provide the fastest response to possible issues. A unit test is a quick verification of specific functionality or data, such as ensuring that a user-defined function returns the expected result. A unit test should be concise and run quickly, producing a single binary value—either pass or fail.

Also important to the application delivery pipeline is ongoing monitoring, which needs to be as comprehensive as the testing processes themselves. Monitoring can be divided into two broad categories: the feedback loop and system monitoring.

A DevOps application delivery pipeline includes a continuous feedback loop that provides participants with ongoing details about the delivery process. For example, when the CI service notifies developers about problems in their code, those alerts can be considered as part of the feedback loop. Development teams (application and database) should have complete visibility across the entire pipeline. Not only does this help identify issues with the application and database earlier in the delivery cycle, but it also helps to monitor the delivery process itself to find ways to improve and streamline operations.

System monitoring is more typical of what you’d expect of database operations. Once the changes have been deployed to production, you must closely monitor all related systems to check for unexpected issues and ensure that systems are running as they should, before users or data are seriously impacted. To this end, DBAs should monitor database systems for performance and compliance, taking into account regional differences and regulatory requirements. Developers and DBAs alike should have a real-time understanding of issues that might need immediate attention or those that should be addressed in the foreseeable future.

Testing and monitoring also play a critical role in ensuring a database’s overall security. Developers, IT administrators and DBAs must take into account security considerations throughout the entire application lifecycle, addressing issues that range from SQL coding to server security in order to ensure that data is protected at every stage of the application delivery process.

Proper testing and ongoing monitoring can go a long way in helping to enforce these protections. By using source control and automating the delivery process, organisations have a process that is more predictable and easier to manage, while providing an audit trail for tracking potential issues. Database teams still need to ensure proper configurations and secure environments, but an effective DevOps pipeline can also play an important role in your security strategy.

The New Era of Database DevOps

Database DevOps offers the promise of quicker, easier and more secure deployments while bringing application and database development efforts in line with one another. But DevOps is not a one-size-fits-all solution to application delivery, nor is it meant to be a developer-first strategy that leaves DBAs and IT administrators behind.

Developers, DBAs, and operation professionals must work together to implement an application delivery strategy that takes into account the needs of all participants, with the goal of delivering the best applications possible as efficiently as possible in the shortest amount of time. Anything less and your database DevOps efforts are destined to fail.

Let’s block ads! (Why?)

SQL – Simple Talk

Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines – Part 1.5

May 25, 2019 Microsoft Dynamics CRM

In our last blog we learned why it is important to version control our solutions and explored how to pack a solution from a repository for deployment to downstream environments. Now it’s time to update our script a bit to take advantage of some cool new features and get ready for our next blog. During the Microsoft Build 2019 developer conference, multi-stage pipelines were announced enabling us to create a full CI/CD pipeline in YAML, which will allow us to properly perform continuous deployment on our solutions.

For a deeper look into changes announced during the Build conference check out the What’s new with Azure Pipelines blog and the announcement for YAML Release in Azure Pipelines session at Build.

Rather than re-hash the basics and everything else we’ve done to this point I will urge you to check out the first blog in the series. In this blog, we will look at the new pipeline features, add stages and complete a solution deployment using multi-stage YAML pipelines. The goal, for now, being to automate solution deployment from our source control management (SCM) system as highlighted in the flow diagram below. In the future, we will look at adding more automation and explore different concepts.

Contents

Getting started

If multi-stage pipelines are not enabled by default when you read this you will first need to enable the preview feature. This can be accomplished using the following steps.

Navigate to your Azure DevOps instance (e.g. https://dev.azure.com/example/project)
Click your profile icon in the top right of the page.
In the drop-down click Preview features
In the Preview feature pane toggle Multi-stage pipelines
Close the pane and you are all set to go.

Updating our build script for a multi-stage setup

Below you will find the complete script from the first blog that enables us to deploy any unpacked solution stored in version control. The difference is that this time the script has some new syntax to enable the code to be used in multiple stages. The notable changes in the section are the addition of the stages and job schema. Stages are collections of jobs that will allow us to logically divide our pipeline between various continuous integration and continuous deployment processes. Jobs are collections of steps that will help us logically divide work within stages. In both cases, we can set dependencies and conditions on other processes or run in parallel.

If you are following along from the first blog I have called out the changes that have been made. Otherwise, follow the steps below to implement the updated solution deployment script using a multi-stage pipeline. If you haven’t unpacked your solution and checked it into version control go back to the first blog for more information. Alternately, you can fork the tutorial repository on GitHub and use the Lesson-1.5 folder for setup.

If you are starting from scratch or have forked the tutorial repository follow the steps below to create a new pipeline.

Navigate to your Azure DevOps project repository. For example, https://dev.azure.com/{username}/D365-CE-DevOps-Tutorial
Hover over Pipelines, then click Pipelines in the fly-out menu.
Click New, then click New Pipeline
On the next screen, we will select the location of our unpacked solution: Azure Repos, GitHub, or another service. Note that choosing GitHub or other services requires that you authorize Azure DevOps to access the repository, the behavior otherwise is the same in our build pipelines.
Select the repository containing the unpacked solution files.
Configure pipeline using the Starter pipeline option and copy/paste the script below replacing the starter script. If you have forked the repo on GitHub choose the existing YAML script in the Lesson-1.5 directory.
Click Save and run
Enter a commit message and click Save and run again
Click Cancel to stop the build
Click the ellipsis (…) in the top right
In the drop-down, click edit pipeline. We will need to add some variables to make things function correctly. follow the steps to create pipeline variables below to continue.

name: $  (BuildDefinitionName)-$  (Date:yyyyMMdd).$  (Rev:.r)

trigger:
- master

stages:

 - stage: Build

   jobs:

    - job:
      displayName: "Pack Solution from repository"

      pool:
        vmImage: 'vs2017-win2016'

      steps:
      - script: md tools
        displayName: 'Create tools directory'

      - powershell: |
          Invoke-WebRequest `
            -Uri https://dist.nuget.org/win-x86-commandline/latest/nuget.exe `
            -OutFile tools\nuget.exe
        displayName: 'Download nuget.exe'

      - powershell: |
          tools\nuget.exe install Microsoft.CrmSdk.CoreTools -O tools
          md "tools\CoreTools"
          $  coreToolsFolder = Get-ChildItem tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
          move "tools\$  coreToolsFolder\content\bin\coretools\*.*" "tools\CoreTools"
          Remove-Item "tools\$  coreToolsFolder" -Force -Recurse
        displayName: 'Install CoreTools'

      # Don't forget to add the env variables to your pipeline settings
      - powershell: |
          Start-Process tools/CoreTools/SolutionPackager.exe `
          -ArgumentList `
            "/action: Pack", `
            "/zipfile: $  (Build.ArtifactStagingDirectory)\packedSolution$  env:SolutionName.zip", `
            "/folder: $  env:SolutionPath", `
            "/packagetype: Both" `
          -Wait `
          -NoNewWindow
        env:
          SolutionPath: $  (solution.path)
          SolutionName: $  (solution.name)
        displayName: 'Solution Packager: pack solution'          

      - task: PublishBuildArtifacts@1
        inputs:
          pathtoPublish: $  (Build.ArtifactStagingDirectory)
          artifactName: drop
        displayName: 'Publish build artifacts'

If you do not want the build to automatically run on commits to master set trigger to none.

Schema changes

Note that the only change above to the original script is the following 5 lines and an adjustment to the indentation level of code following our additions.

stages:
 - stage: Build
   jobs:
    - job:
      displayName: "Pack Solution from repository"

Review the Microsoft Docs page for Jobs for examples of how to setup jobs with varying degrees of complexity and the Stages page for similar examples.

Steps to create pipeline variables

For our pipeline to be able to access the variables defined in code, we will need to manually create the variables in our pipeline settings using the following steps:

Click the ellipsis (…) on the top right of the page and in the drop-down click Variables
Click + Add in the pipeline variables view to add a new variable.
Perform step 3 two times to add the following variables
- name: solution.name
  - - value: <desired zip file name. In this example, contosoUniversity>
- name: solution.path
  - - value: <path to repository location of extracted solution. in this example, Lesson-1.5/ExtractedSolution/>
Checkmark both as Settable at queue time
Click the Save & queue drop down and click Save
Enter a comment and click Save

You may now run the script setting the variables for solution name and solution path if you have not defined them in your variable settings. The result should be a new build artifact named drop that contains you packed solution as both managed and unmanaged.

Adding the release stage

Now that we have completed our build stage we can create a new stage to consume and deploy the solution artifact we published earlier. For added utility, we will use a deployment job to track deployments to our downstream environments. For this example, our release stage will be dependent on the success of the build stage using dependencies and conditions. Note that by default stages are sequential so this change is more for demonstration than necessity in this example.

- stage: Release

   dependsOn: Build
   condition: succeeded('Build') 

   jobs:

    - deployment: Deploy
      displayName: "Import solution artifact"

      pool:
        vmImage: 'vs2017-win2016'

      environment: test
      
      strategy:
        runOnce:
          deploy:
      
            steps:

Schema explanation

deployment - a deployment is a special type of job focused on deploying apps to your environments allowing you to track deployments more granularly.
environment - the name of the environment we wish to deploy to. Note that if you provide an environment name for an environment that you have not explicitly created yet one will be automatically created without associated resources, which is what we want in this case.
strategy - at the time of writing only the runOnce strategy is available, this step will run the deployment exactly one time.
deploy - the entry point to our deployment steps.

Download artifacts

Continuing on with our script we will need to download the solution artifact we publisher earlier. The reason we need to download the artifact is because we have logical separation using jobs in which artifacts are not shared across boundaries.

          - task: DownloadBuildArtifacts@0
              inputs:
                buildType: 'current'
                downloadType: 'single'
                artifactName: 'drop'
                downloadPath: '$  (System.ArtifactsDirectory)'

*Note that indentation has been left for copy/paste purposes. If you have issues with the script check your indentation.

While this snippet of code is very simple I will demonstrate adding it via task assistant in the YAML editor as it can be very handy for adding pre-made tasks in a quick and efficient manner.

Open your pipeline editor
On the right, you should see a task pane. If not, click Show assistant in the top right of the editor window.
In the search box type download build artifacts
Click the Download Build Artifacts task
Enter your artifact name (e.g. drop)
Ensure that you have clicked the line in your editor where you would like to have the task added.
Click Add
Update indentation as needed.
Save your pipeline and run to test your changes.

Import solution into target environment

The only change to our actual deployment code from the first blog other than moving to a release stage and using deployment jobs is that we are now importing asynchronously. Importing asynchronously will help us avoid import timeout issues as noted by readers of the first blog.

            - powershell: Install-Module Microsoft.Xrm.Data.Powershell -Scope CurrentUser -Force
              displayName: 'Install Microsoft.Xrm.Data.PowerShell'

            # Don't forget to add the env variables to your pipeline settings
            - powershell: |
                $  connection = Get-CrmConnection `
                  -ConnectionString `
                    ("AuthType = Office365;" + `
                    "Username = $  env:ServiceAccountUpn;" + `
                    "Password = $  env:ServiceAccountPassword;" + `
                    "Url = https://$  env:EnvironmentName.crm.dynamics.com")

                Import-CrmSolutionAsync -BlockUntilImportComplete `
                  -conn $  connection `
                  -SolutionFilePath $  (System.ArtifactsDirectory)\drop\packedSolution$  ($  env:SolutionName)_managed.zip
              env:
                EnvironmentName: $  (environment.name)
                SolutionName: $  (solution.name)
                ServiceAccountUpn: $  (serviceAccount.upn)
                ServiceAccountPassword: $  (serviceAccount.password)
              displayName: 'Import solution'

*Note that indentation has been left for copy/paste purposes. If you have issues with the script check your indentation.

Add deployment variables

Important - We have added some environment variables. We will need to edit our pipeline settings once more following the steps to create pipeline variables section to add the three new variables below.

name: environment.name
- value: <Dynamics 365 CE org name e.g. contoso.crm.dynamics.com, name only>. If your org is in another geo update the YAML script accordingly.
name: serviceAccount.upn
- value: example@contoso.onmicrosoft.com
name: serviceAccount.password
- value: hopefully not hunter2
- For the password variable be sure to select the lock symbol to change the type of the field to secret.

That’s it, now click Save to commit your changes, then click Run to start the pipeline. Once started you will be redirected to the pipeline summary page to see the action in real-time. Click on the various stage cards to view details. Once the build is completed you can click Environments on the left navigation pane to view past deployments to the environment we created earlier.

If you would like to use some pre-made tasks and leverage the task assistant we discussed in the download artifacts section check out Dynamics 365 Build Tools.

In our upcoming blog(s) we will explore templates to make our pipeline code reusable and look at some new tooling to improve our continuous integration process.

Additional Resources

Let’s block ads! (Why?)

Dynamics CRM in the Field

Introduction to SQL for Cosmos DB

March 12, 2019 BI News and Info

One of the interpretations of the term NoSQL is “Not Only SQL.” Whether this is a factual statement, or an aloof remark is open to debate. However, no one can deny that more and more non-relational data sources are being used in a range of environments. This often leads to both SQL and NoSQL technologies being used side by side despite the differences in both the structures of the data itself and the engines that store the information.

The inevitable challenge for traditional database developers and DBAs is the investment in time and effort required to master a new set of technologies and any new languages required to handle NoSQL data. Fortunately for SQL developers (and indeed for anyone with a decent grounding in SQL) the developers of Cosmos DB have spared a thought for the millions of SQL users who need a path into the brave new world of NoSQL. They have achieved this by providing an SQL API to access JSON-formatted documents stored in Cosmos DB.

This approach brings one instant and overarching advantage for SQL Server (or other relational database) programmers: you are instantly at home with the core language used to extract data.

However, JSON documents are far removed from relational structures and NoSQL document stores are very different beasts compared to relational databases. Consequently, the SQL used to query JSON documents is different in many ways to the conventional SQL that you use with SQL Server. Moreover, Cosmos DB SQL is severely limited compared to T-SQL Yet despite its restrictions, the Cosmos DB SQL API provides an easy path to understanding and exploiting document databases. Acquiring a working knowledge of how to query Cosmos DB could open up new horizons that enable you to integrate data from the relational and NoSQL worlds.

The Basics

Cosmos DB is a multi-model NoSql database. Currently it can handle three types of non-relational data:

Document databases
Graph databases
Key-value databases

Only one of these data models can be queried using SQL in Cosmos DB. This is the document database. Indeed, this is probably a good place to add that Cosmos DB SQL only concerns querying document databases. There is no DDL (data definition language) involved.

A document database is a non-relational database designed to store documents that have no fixed structure. The leading document databases use JavaScript Object Notation (JSON) as the format for structuring the data. The good news with a lightweight and easily modifiable approach like this, is that you are not bound by a rigid schema. The bad news is that you are not bound by any schema at all. This fluidity has many sterling advantages but comes at a cost when querying the JSON data. The two main challenges that you are likely to face as a SQL developer are:

Schema on read – Instead of the database schema being part of the database structure, any required structure is defined when the query is written.
Nested structures – JSON documents are objects that can contain the complete data describing a self-contained unit. They can be extremely complex and represent a structure that would require a series of tables in a SQL Server database.

Fortunately, Cosmos DB SQL has been adapted to help you overcome the challenges inherent in using a relational database query language to query non-relational data stored in the nested structures of JSON-as you will see in the subsequent article.

When Would you Need Cosmos DB SQL?

Before leaping in to the minutiae of a new or at least a slightly different-dialect of a programming language, you may be forgiven for wondering why you should need to make the effort to master it in the first place. A few potential reasons are:

Cosmos DB can store JSON documents at a scale that is truly impressive, and that makes it into an excellent repository when dealing with terabytes of JSON files. SQL Server’s capabilities as a JSON document store are completely overshadowed by Cosmos DB.
Cosmos DB can become an ideal complement to SQL Server as a JSON storage service. This is because SQL Server provides analytical capacities that are missing from Cosmos DB. In practice, you can extract finely filtered data from a vast store of documents in Cosmos DB and then load this (infinitely smaller dataset) into SQL Server tables as rowsets for in-depth analysis. This can avoid having to implement a totally different set of technologies to deliver analytics over JSON document stores.
You can use PolyBase in SQL Server 2019 to connect to Cosmos DB collections. In this case you will probably need some basic Cosmos DB SQL to flatten the JSON data so that it can be used as an external table.
Even if you rely only on Cosmos DB to analyze JSON data you will likely need this Cosmos DB flavor of SQL when writing JavaScript-based stored procedures and user-defined functions.
If you are connecting to Cosmos DB using ODBC you are likely to need to flatten the JSON data. This approach will inevitably require the use of Cosmos DB SQL.
You can use Cosmos DB SQL SQL API (and SQL over JSON document collections) as the data source in Azure Data Factory activities.

There are, doubtless many other reasons, but I hope that some of these will encourage you to start looking at Cosmos DB.

The Required Toolkit

You need one of two things to practice SQL in Cosmos DB:

A Cosmos DB account in Azure
The Cosmos DB Emulator which is beautifully explained in this Simple-Talk article.

For the purposes of this article, I will presume that you are using the Cosmos DB Emulator-although either the emulator or Cosmos DB in Azure will work equally well when learning to query JSON documents using SQL.

Sample Data

As this article is a gradual introduction to querying Cosmos DB documents, you will be using simple JSON documents stored in a single collection in Cosmos DB.

The JSON structure that you can use to practice the basics of SQL queries using Cosmos DB is completely flattened, and looks like this:

{

“CountryName”:“United Kingdom”,

“MakeName”:“Ferrari”,

“ModelName”:“Testarossa”,

“Cost”:52000.0000,

“RepairsCost”:2175.0000,

“PartsCost”:1500.0000,

“TransportInCost”:750.0000,

“Color”:“Red”,

“SalePrice”:65000.00,

“LineItemDiscount”:2700.00,

“InvoiceNumber”:“GBPGB001″,

“SaleDate”:“2015-01-02T08:00:00″,

“CustomerName”:“Magic Motors”,

“SalesDetailsID”:1

}

Using the Sample Data

The sample documents are in the attached zip (CosmosDBQueries.zip) file which you should install onto your C: drive in the C:\CosmosDB directory. The seven documents that make up the collection are in the simplecars subdirectory.

The first thing to do is to create a collection. I suggest using /CountryName as the partition key in this example. As creating databases and partitions is explained in the article referenced above, I will not go through it again it again here. Then you need to load the documents. As there are only a handful of them, you can just click the New Document button and paste the contents of each file into a separate document, and then save it.

There is also the Upload option for loading multiple files which you can use instead. However, this does not seem to work at the moment in the Cosmos DB Emulator.

Of course, in the real world, there are solutions that you will need to learn if you are loading hundreds of thousands of documents and terabytes of data. However, these techniques are out of scope for this simple introduction.

Basic Terminology

For the sake of clarity, there are a few basic definitions that should help you bridge the gap between SQL and the world of JSON documents:

A collection can be considered to be a database
A document equates broadly to a recordset (although the nested structure makes it more similar to XML)
An attribute is a field or column

These points of comparison are not destined to be taken too literally and are simply provided as an initial stepping stone to help with your initial understanding if you have never seen document databases before.

Basic SQL Queries

Begin with the simplest possible example. With the Cosmos DB Emulator running, enter and execute the following query:

SELECT * FROM C

You will see all the documents in the current collection returned as the output, each one similar to the sample document shown above.

As simple as this query may be, as it is the first query that you have created with Cosmos DB SQL a few comments are in order. Firstly, the data is returned as JSON, even if you are using SQL to query the documents. In effect, JSON is the data format. A second point is that you cannot specify the collection to query in the SQL. Consequently, you need to ensure that you open a query window from the required collection. However, you can use anything as the definition of the data source. The query could read:

SELECT * FROM thecurrentcollectionbecauseIlikedreamingaboutcars

When running simple queries, the source JSON is returned as it exists in the collection. You will learn how to shape the output JSON later in this article and in the second article in this series.

A final point to note is that, technically, you can also return a complete JSON document with this SQL:

SELECT * FROM ROOT

Now to be a little more selective, try this short query:

SELECT saleselements.InvoiceNumber

,saleselements.TotalSalePrice

FROM saleselements

This is the output you should see the following (here, obviously, is a truncated view of the output):

{

“InvoiceNumber”: “GBPGB001″,

“TotalSalePrice”: 65000

{

“InvoiceNumber”: “GBPGB011″,

“TotalSalePrice”: 89000

}

Executing this query only returns two of the available JSON attributes. What is worth noting here is that you must use the collection name or alias when referencing attributes. It is generally easier to use short aliases for the collection, like this:

SELECT s.InvoiceNumber, s.TotalSalePrice

FROM saleselements AS s

The AS keyword when aliasing collections is optional. Attribute names are case-sensitive, although misspelling them will not stop the query executing it will prevent the attribute from being returned in the output.

Of course, you can add aliases to attributes:

SELECT s.InvoiceNumber, s.SalePrice AS Price

FROM saleselements AS s

Doing this will deliver the following JSON:

{

“InvoiceNumber”: “GBPGB011″,

“Price”: 8500

}

However, when aliasing attributes you need to remember that:

The AS keyword is optional
Aliases are case-sensitive.
Aliases must respect JavaScript naming conventions – you cannot start an alias with a number or other non-alphabetical character except the dollar sign or underscore

You cannot add an alias to * so you cannot write

SELECT c.* FROM c

I realize that the JSON specification calls attributes “members” and the data for each member an element, yet the use of the word attribute to describe the name of a piece of data is so prevalent I prefer to use it.

Cosmos DB SQL will return strings, numbers and dates exactly as they are stored in the JSON document – as you can see if you try this query:

SELECT s.InvoiceNumber

,s.SaleDate

,s.SalePrice

FROM saleselements AS s

The result will be:

{

“InvoiceNumber”: “GBPGB011″,

“SaleDate”: “2015-04-30T00:00:00″,

“SalePrice”: 8500

}

Of course, Cosmos DB SQL can perform basic arithmetic:

SELECT s.InvoiceNumber

,s.SalePrice - s.Cost AS GrossProfit

FROM saleselements AS s

As you can see below:

{

“InvoiceNumber”: “GBPGB011″,

“GrossProfit”: 1700

}

As a variation on a theme, you can always write SELECT clauses like this:

SELECT s[“InvoiceNumber“]

,s[“SaleDate“]

FROM saleselements AS s

Indeed, you can mix and match the ‘alias dot attribute’ notation and the ‘alias square bracket and double quote’ notation (in which case there are no dots used to link the alias and the attribute name) inside the same query.

Simple WHERE Clauses

Time to move on to data selection. After all, Cosmos DB can store terabytes of JSON documents and allows you to scale queries to use more or less processing power so that you can balance the requirements of query time and cost (in the financial sense) to suit each individual requirement.

A Basic Text Filter

Suppose that you want to isolate all invoices where a Bentley was sold. This should not tax your SQL knowledge, and hopefully will reassure you that Cosmos DB SQL cleaves to basic SQL tenets:

SELECT s.InvoiceNumber ,s.SalePrice

FROM s

WHERE s.MakeName = “Bentley“

You should see a result like the following:

{

“InvoiceNumber“: “GBPGB011“,

“SalePrice“: 80500

}

Yes, you are using double quotes to enclose a string. This may come as a surprise to SQL Server developers (although possibly less so to users of other databases). To be fair, you can also use single quotes if you feel that you are otherwise betraying your SQL heritage.

Numeric Filters

Filtering on numbers in JSON attributes is probably what you would expect it to be. You could write a WHERE clause like this one to specify a precise figure:

SELECT s.InvoiceNumber ,s.SalePrice

FROM s

WHERE s.LineitemNumber = 2

Or like this one to define a numeric range:

SELECT s.InvoiceNumber ,s.SalePrice

FROM s

WHERE s.Cost BETWEEN 50000 AND 100000

Here, the result will be:

{

“InvoiceNumber”: “GBPGB011″,

“SalePrice”: 8500

}

And now is the disconcerting part. You can filter on a field and include it in the SELECT clause at the same time-like this (although this will indicate the filter validity in the results):

SELECT s.InvoiceNumber, s.Cost BETWEEN 50000 AND 100000

AS InCostRange, s.SalePrice

FROM s

This query will give the following result:

{

“InvoiceNumber”: “GBPGB011″,

“InCostRange”: false,

“SalePrice”: 8500

}

To conclude the subject of elementary WHERE clauses, you can also reassure yourself that:

The AND, OR, NOT operators function pretty much as they do in T-SQL
=, !=, >=, <=, <> operators allow you to specify threshold values in queries
You can use parentheses to create more complex query logic

Alphabetical Ranges

If you ever need to return documents based on an alphabetical range in an attribute, then one way of obtaining the desired result is to use this kind of SQL:

SELECT s.CustomerName ,s.SalePrice

FROM saleselements AS s

WHERE SUBSTRING(s.CustomerName, 0, 1) BETWEEN “A“ AND “M“

Date and Time Filters

Cosmos DB SQL does allow for some more advanced filtering methods in the WHERE clause. Although the possibilities are, it has to be said, considerably more limited than those offered by T-SQL, you can, nonetheless, include (and exclude) irrelevant JSON documents by filtering on date and time elements.

JSON does not have a date type, as such. Instead it uses a string. Consequently, you risk encountering dates in any of a myriad of formats. The upshot is that you will be delving into the specific string format to filter on dates and times.

The format in the sample data is in something close to the ISO 8601 format, which looks like this: YYYY-MM-DDTHH:MM:SS. This means that searching for a specific date and time means representing the datetime as an ISO string, in this way:

SELECT s.SaleDate

FROM s

WHERE s.SaleDate = “2015-01-02T08:00:00“

If you are looking to set an upper or lower boundary for a date or time you can use the standard comparison operators: >=, <=, <> or !=.

Using Date Ranges

It follows that defining a date range requires nothing more than a simple AND operator in the WHERE clause.

SELECT s.SaleDate

FROM s

WHERE s.SaleDate >= “2015-01-01T00:00:00“

AND

s.SaleDate <= “2015-01-31T23:59:59“

This returns data from three documents:

[

{

“SaleDate”: “2015-01-02T08:00:00″

{

“SaleDate”: “2015-01-25T00:00:00″

{

“SaleDate”: “2015-01-02T10:33:00″

}

]

Or, possibly more elegantly, you can use the standard BETWEEN … AND construction:

SELECT s.SaleDate

FROM s

WHERE s.SaleDate BETWEEN “2015-01-01T00:00:00“

AND “2015-01-31T23:59:59“

This returns data from the same three documents:

[

{

“SaleDate”: “2015-01-02T08:00:00″

{

“SaleDate”: “2015-01-25T00:00:00″

{

“SaleDate”: “2015-01-02T10:33:00″

}

]

Applying Time Filters

Filtering by time essentially means extracting the hour, minute and possibly second or millisecond elements from the date string. So, to find all cars sold between 8:45AM and 12AM you would write:

SELECT s.SaleDate

FROM s

WHERE SUBSTRING(s.SaleDate, 11, 2) BETWEEN “08:45“ AND “12:00“

This should return data from two documents in the collection:

[

{

“SaleDate”: “2015-02-03T10:00:00″

{

“SaleDate”: “2015-01-02T10:33:00″

}

]

The lack of a schema in the JSON documents means that the date could be specified in a completely different string format, or even as a number (say in the YYYYMMDD format where the data shown above would be 20150131).

Clearly the requisite queries would have to be adapted to the date format used, in which case a theoretical query on a totally different collection (where the dates were stored differently) could read:

SELECT s.SaleDate

FROM s

WHERE s.SaleDate BETWEEN 20150101 AND 20150131

Yet again the ‘free-form’ nature of JSON means that you will have to display both ingenuity and mental agility in the queries that you write.

Traps for the Unwary

Cosmos DB SQL requires you to wrap non-standard attribute identifiers in double quotes (and square brackets). As I mentioned above, no dot notation is used for the collection alias in this case. If you want to output one of the collection’s built-in attributes, you need to write SQL like this:

SELECT s.InvoiceNumber ,s.SalePrice, s[“_rid“]

FROM saleselements AS s

It is also worth noting that you cannot use unauthorized elements such as leading numbers in aliases even when you enclose the alias in double quotes.

When filtering on texts that contain special characters you need to know that certain characters are escaped in Cosmos DB SQL. Consequently, this is what you need to enter to include these characters in a WHERE clause:

Single quote: \’
Double Quote: \”
Backslash: \

There are, of course, many others, but I will let you delve into the documentation to find them.

Now that the basics have been covered (and hopefully you are reassured that your SQL skills can be applied to document databases) it is time to look at some aspects of Cosmos DB SQL in greater depth.

Sorting Output

Cosmos DB SQL also contains the ORDER BY clause, so you can write queries like this one:

SELECT s.InvoiceNumber ,s.SalePrice AS Price

FROM saleselements AS s

ORDER BY s.InvoiceNumber

It probably comes as no surprise that you will see a result set something like the following:

{

“InvoiceNumber”: “EURDE004″,

“Price”: 11500

{

“InvoiceNumber”: “EURFR005″,

“Price”: 19950

{

“InvoiceNumber”: “GBPGB001″,

“Price”: 65000

{

“InvoiceNumber”: “GBPGB002″,

“Price”: 220000

}

However, at this juncture a series of limitations appear on the horizon. For, although you can add the ASC / DESC keywords to an ORDER BY clause (as you can in T-SQL) the following restrictions apply here:

The ASCENDING and DESCENDING keywords are not supported
Sorting on multiple attributes is not possible
You cannot sort on an alias for an attribute – you must reference the attribute preceded by the collection alias and a dot as you do in the SELECT clause
You cannot order by anything other than a field name
You cannot sort by a computed value

Summarizing Results

Here are several examples for common aggregates:

Finding the Number of Documents in a Collection

The query to return the number of documents in a collection is a slight extension of standard SQL:

SELECT VALUE COUNT(1)

FROM s

Here you are adding the VALUE keyword to return a number only and not a JSON structure. Consequently, the result looks like this:

[ 7

]

You could also write this query as:

SELECT VALUE COUNT(s.id)

FROM s

Very Basic Aggregations

If you want to aggregate data from a collection, you could try a query like this one:

SELECT VALUE SUM(s.SalePrice)

FROM saleselements AS s

The output is extremely simple:

[

424950

]

However, some fairly profound restrictions appear here (at least with the current version of Cosmos DB).

There can only be one aggregation per query
No alias can be added to the aggregation if you are using the VALUE keyword
You will get an error if the query is a cross-partition query, and you do not use the VALUE keyword. Cosmos DB partitions (as you might expect) are a core storage and query acceleration design approach. However, creating partitions is a subject in its own right that I cannot go into here

Intriguingly, you can get around the forced use of the VALUE keyword when querying across partitions with the use of a subquery like this:

SELECT * FROM

(SELECT SUM(s.SalePrice) FROM s)

In this case, the output looks something like this:

[

{

“$ 1″: 424950

{

“$ 1″: 0

}

]

You are probably wondering where the $ 1 attribute names come from. This is, quite simply, what happens when you do not use an alias for a subquery or a function in Cosmos DB SQL. Think of it as being similar to non-aliased columns in T-SQL appearing as (no column name) in the query output.

Using a subquery also allows you to use multiple aggregate values in a calculation. In the following example you can see the difference between the maximum and average sale prices:

SELECT * FROM

(SELECT VALUE MAX(s.SalePrice) - AVG(s.SalePrice)

FROM s)

As you can see, you do not necessarily have to alias the subquery:

[

159292.85714285716

]

Of course, you can filter the data set that the calculation is applied to:

SELECT * FROM

(SELECT VALUE MAX(s.SalePrice) - AVG(s.SalePrice)

FROM s WHERE s.MakeName = “Ferrari“)

This gives the corresponding output:

[

77500

]

As you can see here, applying the VALUE keyword returns just that-a value-and consequently there is no attribute name in the output.

Shaping Output

Cosmos DB SQL allows you to shape the output of an SQL query in several ways – pretty much as ‘ordinary’ SQL does. Some of the techniques that you can apply are described in this section.

Returning the Top N Documents

Cosmos DB SQL imitates T-SQL when it comes to returning only a specified number of documents from a collection. This one does not even need me to show you the result of the query.

SELECT TOP 3 s.InvoiceNumber, s.SalePrice

FROM s

ORDER BY s.SalePrice DESC

Concatenate Attributes

The Cosmos DB SQL CONCAT() function is simple yet powerful. Take a look at the following example:

SELECT CONCAT(s.CustomerName

,“ Date of Sale: “

,s.SaleDate) AS CustomerName

FROM s

This function allows you to join multiple fields and/or static values to produce a single output attribute-like this:

{

“CustomerName”: “Wonderland Wheels Date of Sale: 2015-04-30T00:00:00″

{

“CustomerName”: “Wonderland Wheels Date of Sale: 2015-04-30T00:00:00″

}

Replacing Values in the Output

Should you need to replace specific values in the resulting JSON, you can use the REPLACE() function. And, yes you can use multiple nested replace operations if you need to.

SELECT REPLACE(

REPLACE(s.CountryName, “United Kingdom“, “Blighty“)

,“France“, “Hexagone“) AS Country

FROM s

Here is the corresponding query output (from the final objects returned by the query):

{

“Country”: “Blighty”

{

“Country”: “Germany”

{

“Country”: “Hexagone”

}

String Functions – Simple Classics

Cosmos DB SQL has most of the basic string functions that you could require. Some of these are used in the following query:

SELECT UPPER(s.CustomerName) AS CustomerName

,LOWER(s.CountryName) AS CountryName

,TRIM(s.CountryISO3) AS CountryISO3

,LEFT(s.MakeName, 3) AS MakeAbb

,RIGHT(s.InvoiceNumber, 3) AS InvoiceNumber

,SUBSTRING(s.InvoiceNumber, 3, 2) AS SaleCountry

FROM s

Here is the result for a single document:

{

“CustomerName”: “WONDERLAND WHEELS”,

“CountryName”: “united kingdom”,

“CountryISO3″: “GBR”,

“MakeAbb”: “Por”,

“InvoiceNumber”: “011”,

“SaleCountry”: “GB”

}

You need to be aware that:

SUBSTRING() is 0 based.
You can also use the LTRIM() and RTRIM() functions if you need to.

A Final Few String Functions

Cosmos DB SQL only has a couple of dozen string functions, and we have seen many of the most useful ones already. Should you need them, you can always add the following to your armoury:

REPLICATE()

LENGTH()

INDEX_OF()

Mathematical Functions

Cosmos DB SQL comes complete with a core set of mathematical functions.

SELECT ROUND(s.TotalSalePrice) AS RoundedSalePrice

,FLOOR(s.RepairsCost) AS RepairsCost

,CEILING(s.PartsCost) AS PartsCost

,TRUNC(s.TransportInCost) AS TransportInCost

FROM s

Take a quick look at the result:

{

“RoundedSalePrice”: 89000,

“RepairsCost”: 250,

“PartsCost”: 225,

“TransportInCost”: 150

}

These are considerably more limited than their T-SQL equivalents, and only carry out basic rounding.

There are a few others such as ABS(), SQRT(), SQUARE() plus a small set of trigonometric functions should you need them.

Logic (Imitation CASE Statements) – Ternary Functions

ANSI SQL has the CASE statement. Cosmos DB SQL instead uses a ternary logic approach. That is, it uses the ? and : constants to break up the code into test ? true : false elements.

To see this in action, take a look at the following Cosmos DB SQL snippet:

SELECT s.CountryName IN (“France“, “Germany“, “Spain“)

? “EU“ : “Other“ AS Region

FROM s

This code tests for any of France, Germany or Spain in the CountryName attribute and returns EU if any of these elements are found-and Other if they are not found, as you can see, below:

[

{

“Region”: “Other”

{

“Region”: “Other”

{

“Region”: “Other”

{

“Region”: “Other”

{

“Region”: “Other”

{

“Region”: “EU”

{

“Region”: “EU”

}

]

Fortunately Cosmos DB SQL lets you nest ternary logic (as you may have done using the IF() function in Excel or the T-SQL IIF() functions).

SELECT s.CountryName IN (“France“, “Germany“, “Spain“)

? “EU“

: s.CountryName = “United Kingdom“

? “Blighty“

: “Other“

AS Region

FROM s

Conclusion

Now that you have a grasp of the basics of Cosmos DB SQL, I hope that you will be tempted to consider using this powerful and scalable document database as a potentially game-changing extension to your relational database environment-and this particular approach to NoSQL as a valuable part of your skillset.

I am not denying that the SQL that is implemented on top of Cosmos DB is severely limited. Although you can often find workarounds to obviate the worst of its restrictions, you are essentially stuck with simple SQL queries that use SELECT, FROM. WHERE and ORDER BY. You will not find any GROUP BY or HAVING clause for instance, and queries are bound to a single collection. Its analytical capabilities are curtailed by the absence of windowing functions.

On the other hand, the sheer simplicity of the query language, as well as the ease with which you can adapt your current skills and learn to query JSON documents without having to learn a new framework and syntax are powerful points in its favour.

However, we have not finished with the guided tour of Cosmos DB SQL yet. A second article will explain some of the more advanced features of the language that can be used to cope with the more complex document structures that JSON allows. This article will also provide some hints and tips on working around some of the limitations inherent in querying JSON documents with SQL.

Let’s block ads! (Why?)

SQL – Simple Talk

Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

March 1, 2019 Microsoft Dynamics CRM

In this blog series, we will explore building out DevOps processes and practices for Dynamics 365 Customer Engagement (CE) by utilizing YAML based Azure Pipelines. In this first blog, we will cover version controlling solutions and the basics of automating deployment.

What is DevOps?

DevOps is the union of people, process, and products to enable continuous delivery of value to our end users. The contraction of “Dev” and “Ops” refers to replacing siloed Development and Operations to create multidisciplinary teams that now work together with shared and efficient practices and tools. Essential DevOps practices include agile planning, continuous integration, continuous delivery, and monitoring of applications. (Sam Guckenheimer, What is DevOps?)

Getting started

If you already have a Dynamics 365 CE environment, Azure DevOps, you’re familiar with the basics, and have a solution ready to go skip to Moving solutions between environments. If you are new to all of this, please continue from here.

The most basic workflow of Dynamics 365 CE development, deployment, and functional testing consists primarily of manual processes. We modify, export, and import a solution(s) into a downstream environment, then manually test to ensure there are no issues with solution layering, integrations, etc. That gets old quick, what we really want is to automate as much as we can. The first step we can take is to implement the practice of version control for our solutions and automate solution imports to various environments.

What we will need

Assumptions

Some experience with customizing Dynamics 365 CE
A basic understanding of version control
- There are a number of tools to make version control easier such as GitHub Desktop, Sourcetree, and GitKraken to name a few. For this series, it is expected that you know the basics or will learn Git for version control.

Version control for solutions

Solutions in Dynamics 365 CE are in essence a package containing any customization we’ve done to our environment that we can export from one environment then import into various environments. When exported from an environment, solutions are in the form of a zip file. When that zip file is unzipped, the output directory contains folders for plugins, web resources, and any workflows we have made as well as XML files defining the schema of any customization we have done. In the zipped format, our schema definition is contained in one massive file. Consider this zip file as a binary, or in layman’s terms, a tidy package with a fancy bow, i.e. It may look nice but it’s not easy to see what’s inside and it’s a poor format for version control.

Enter Solution Packager. Solution packager essentially takes our Dynamics 365 CE solution zip file and breaks it out into a logical folder structure by decomposing the contents. The resulting output shows a more granular view of our solution and is considerably more friendly for version control as you can see from the example screenshots below.

Example zip file directory structure

Example extracted solution directory structure

Note:

The extra granularity of an unpacked solution makes it ideal for team development. Developers can pack and import a solution from a repository into their various development environments, make changes, export, unpack, and commit the solution back to the repository. For more on the benefits of using solution packager and example scenarios check out the article Use source control with solution files on Microsoft Docs.
Solution Packager does not completely remove merge conflicts but does make it easier to manage them.

Download solution packager

To get started we will need to download a copy of the Dynamics 365 CE Core Tools Nuget package locally, this package includes Solution Packager.

Steps to download

Open a PowerShell Terminal
Navigate to the directory that you want the tools downloaded to.
- Note that the code below will create the folder structure Tools\CoreTools in the current directory
Copy & paste the code below into the terminal or new script window if using PowerShell ISE and run the script.

    $  sourceNugetExe = "https://dist.nuget.org/win-x86-commandline/latest/nuget.exe"
    $  targetNugetExe = ".\nuget.exe"
    Remove-Item .\Tools -Force -Recurse -ErrorAction Ignore
    Invoke-WebRequest $  sourceNugetExe -OutFile $  targetNugetExe
    Set-Alias nuget $  targetNugetExe -Scope Global -Verbose
    
    ##
    ##Download CoreTools
    ##
    ./nuget install  Microsoft.CrmSdk.CoreTools -O .\Tools
    md .\Tools\CoreTools
    $  coreToolsFolder = Get-ChildItem ./Tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
    move .\Tools\$  coreToolsFolder\content\bin\coretools\*.* .\Tools\CoreTools
    Remove-Item .\Tools\$  coreToolsFolder -Force -Recurse
    
    ##
    ##Remove NuGet.exe
    ##
    Remove-Item nuget.exe

*The code above was sourced from the Download tools from NuGet Microsoft Docs article.

Extract solution file

Now that we have Solution Packager downloaded we can work on adding our solution to version control. If you are following along with your own setup you will need to export a solution from your environment as both managed and unmanaged. It is recommended to use an unmanaged solution for development purposes and managed for all other environments. If you do not have a solution file handy grab a copy of our example unpacked solution from our GitHub repo in the Lesson-1 folder. Note that Azure DevOps build pipelines integrate with Azure Repos, GitHub, and GitHub Enterprise repository. In this example, I am using GitHub.

PowerShell command to extract Dynamics 365 CE solution:

The following PowerShell command allows us to extract both our managed and unmanaged solution files, this is accomplished by setting the packagetype to both and the zipfile value to the path to our unmanaged solution zip file. Solution packager will search the same directory for a solution file with the name containing the unmanaged solution name but ending in _managed.zip, for example, contoso.zip and contoso_managed.zip.

.\Tools\CoreTools\SolutionPackager.exe /action extract /packagetype both /zipfile <path to file>\contoso.zip /folder <path to output directory>

Once unpacked, submit the output to version control.

Moving solutions between environments using a YAML script

Now that we have an unpacked solution and it has been checked into version control we can work on automating deployment. I tend to follow the GitHub Flow development workflow that assumes anything in the master branch is always deployable. Following this pattern, we will build a simple YAML script that triggers on commits to the master branch and performs the steps outlined below. For a full explanation of YAML schema for Azure Pipelines check out aka.ms/yaml.

YAML script steps

Create a new build pipeline

First and foremost we will need a new build pipeline, so let’s head over to our Azure DevOps project repository to get this started.

Navigate to your Azure DevOps project repository. For example, https://dev.azure.com/{username}/D365-CE-DevOps-Tutorial
Click Pipelines, then click Builds.
Click New, then click New Build Pipeline
On the next screen, we will select the location of your unpacked solution: Azure Repos, GitHub, or GitHub Enterprise. Note that choosing GitHub requires that you authorize Azure DevOps to access the repository, the behavior otherwise is the same in our build pipelines.
Select the repository containing the unpacked solution files.
Configure pipeline using the Starter pipeline option.

*Note that choosing starter pipeline will name you file azure-pipelines.yml so you’ll want to remain the file later and update the path in your pipeline settings.

Define name, trigger, and pool type

Now that a basic pipeline has been set up we can begin building out the steps needed to pack and deploy our solution file. For this example, we will be using the build pipelines only as YAML support for release pipelines is still under development at the time of writing.

We can start by deleting the contents of the starter template and adding our own definitions for the build name, trigger, and pool.

name: $  (BuildDefinitionName)-$  (Date:yyyyMMdd).$  (Rev:.r)

trigger:
- master

pool:
  vmImage: 'vs2017-win2016'

Explanation of schema:

Name - represents build number format. Default build numbers are whole numbers, we can do better than that.
Trigger - specific branch to trigger on, if no branch is provided, commits to any branch will trigger continuous integration.
Pool - essentially the type of OS and tools set you want to have your build run on. In this case, we are using Visual Studio 2017 on Windows Server 2016, but there are other available build agents as well.

Download and install Solution Packager

A build agent is provided to us as a blank workspace, so we will need to download and install the necessary tools to complete our build. We will first download and install Nuget so that we can install the Dynamics 365 CE Core Tools Nuget package. Solution Packager resides in the core tools package and is nested down a few levels. Rather than deal with a long string to point to SolutionPackager.exe, we will do some clean up to make it easier to reference the tool and to make this step easier to reuse in the future.

Append the following code the pipeline script, if you get a syntax error ensure Steps is not indented.

steps:
- script: md tools
  displayName: 'Create tools directory'

- powershell: |
    Invoke-WebRequest `
      -Uri https://dist.nuget.org/win-x86-commandline/latest/nuget.exe `
      -OutFile tools\nuget.exe
  displayName: 'Download nuget.exe'

- powershell: |
    tools\nuget.exe install Microsoft.CrmSdk.CoreTools -O tools
    md "tools\CoreTools"
    $  coreToolsFolder = Get-ChildItem tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
    move "tools\$  coreToolsFolder\content\bin\coretools\*.*" "tools\CoreTools"
    Remove-Item "tools\$  coreToolsFolder" -Force -Recurse
  displayName: 'Install CoreTools'

Note that script above is essentially the same as the script we used in the Download solution packager section.

Save and run to ensure your setup is correct and make adjustments as needed.

Pack Solution from repository

With Solution Packager installed we can turn our attention to packing up our extracted solution for importing into our target environment(s). To do that we will need to pack the solution back into its original zip file state using, this can be accomplished by reversing the extraction process we completed earlier in this blog.

- powershell: |
    Start-Process tools/CoreTools/SolutionPackager.exe `
    -ArgumentList `
      "/action: Pack", `
      "/zipfile: $  (Build.ArtifactStagingDirectory)\packedSolution$  env:SolutionName.zip", `
      "/folder: $  env:SolutionPath", `
      "/packagetype: Both" `
    -Wait `
    -NoNewWindow
  env:
    SolutionPath: $  (solution.path)
    SolutionName: $  (solution.name)
  displayName: 'Solution Packager: pack solution'

/action - available options are pack and extract
/zipfile - the location and desired name of packed solution zip file.
- $ (Build.ArtifactStagingDirectory) is a predefined build variable that points to a directory that is purged with each new build so no need for cleanup in our script.
/folder - the location of our unpacked solution that will be packed.
/packagetype - Defines whether the solution should be packed as managed or unmanaged. Both will create both unmanaged and managed solution zip files.

In the code above you will have noticed that there is now an environment variable section, env, that contains two variables: SolutionPath and SolutionName. These variables allow us to define the path to our extracted solution and desired packed zip file name. We will define the variables in our pipeline settings variables tab and enable the variables for modification at queue time. Note that the solution name that you provide is a friendly name, the actual unique name and localized name(s) that will be used in Dynamics are stored in the solution.xml file of the packed and unpacked solution.

Steps to create pipeline variables

For our script to be able to access the variables defined in our script, we will need to manually create them in our pipeline settings using the following steps:

*Don’t forget to click Save and run before attempting to navigate away.

Click the ellipsis (…) on the top right of the page and in the drop-down click Pipeline settings, this will take you to the visual pipeline designer.
Click the Variables tab.
Click + Add in the pipeline variables view to add a new variable.
Perform step 3 twice and add the following variables
- name: solution.name
  - value: <desired zip file name. In this example, contosoUniversity>
- name: solution.path
  - value: <path to repository location of extracted solution. in this example, Lesson-1/ExtractedSolution/>
Checkmark both as Settable at queue time
Click the Save & queue drop down and click Save
Enter a comment and click Save

*If you queue your build ensure the branch where your YAML file is saved is correct or you may get an error. Or navigate back to the pipeline YAML editor and run from there.

Publish build artifacts

Next, we are going to publish our build artifacts even though release pipelines are not available for YAML builds at the time of writing this blog. In a future update, we will be moving our solution imports to a release pipeline and start breaking our script out into templates. For now, on build completion, you will be able to see and download the artifacts and the build will handle imports.

- task: PublishBuildArtifacts@1
  inputs:
    pathtoPublish: $  (Build.ArtifactStagingDirectory)
    artifactName: drop
  displayName: 'Publish build artifacts'

Save and run to ensure your setup is correct. again, ensure you don’t have extra spaces as the script is space sensitive. You can click the build queue notification message to see the build steps run in real-time and view the execution history. Once the build is complete, you should see a new button labeled Artifacts on the top left of the page. Click this drop-down to view and download your solution file artifact(s). Clicking Release will automatically add build artifacts from this build to your release pipeline, for this blog we will not be doing this because YAML for release pipelines is not out yet.

Import solution into target environment

Finally! Let’s deploy our solution to an environment.

The following snippet will download and install Microsoft.Xrm.Data.Powershell, then perform a synchronous import of our managed solution.

- powershell: Install-Module Microsoft.Xrm.Data.Powershell -Scope CurrentUser -Force
  displayName: 'Install Microsoft.Xrm.Data.PowerShell'

- powershell: |
    $  connection = Get-CrmConnection `
      -ConnectionString `
        ("AuthType = Office365;" + `
        "Username = $  env:ServiceAccountUpn;" + `
        "Password = $  env:ServiceAccountPassword;" + `
        "Url = https://$  env:EnvironmentName.crm.dynamics.com")

    Import-CrmSolution `
      -conn $  connection `
      -SolutionFilePath $  (Build.ArtifactStagingDirectory)\packedSolution$  ($  env:SolutionName)_managed.zip
  env:
    EnvironmentName: $  (environment.name)
    SolutionName: $  (solution.name)
    ServiceAccountUpn: $  (serviceAccount.upn)
    ServiceAccountPassword: $  (serviceAccount.password)
  displayName: 'Import solution'

Important - We have added some environment variables so we will need to edit our pipeline settings once more following the steps in the Steps to create pipeline variables section.

This time for step 4 we will add 3 new variables

name: environment.name - value: <Dynamics 365 CE org name e.g. contoso.crm.dynamics.com, name only>
name: serviceAccount.upn
name: serviceAccount.password
- value: hopefully not hunter2
- For password variable be sure to select the lock symbol to change the type of the field to secret.

*Mark variables that you want to modify at queue time to settable at queue time.

That’s it, now you can click Save and run on the top left of the editor. Once the build has been kicked off click the build name in the notification banner to watch the build run. After the build has successfully completed your solution will be imported into the specified target environment and that’s that.

Stay tuned for more blogs that improve and build upon this example setup.

If you are interested in this topic and would like to do some further self-study I encourage you to check out the Solution Lifecycle Management: Dynamics 365 for Customer Engagement apps white paper.

*[CE]: Customer Engagement

References

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Introduction to SQL Server Security — Part 3

February 26, 2019 BI News and Info

The series so far:

Microsoft introduced contained databases in SQL Server 2012. A contained database is one that is isolated from other databases and from the SQL Server instance that hosts the database. The database maintains much of its own metadata and supports database-level authentication, eliminating the need for server-based logins. As a result, a contained database is more portable than a traditional, non-contained database. It can also simplify database development and administration, as well as make it easier to support Always On Availability Groups.

Controlling access to a contained database is similar to a non-contained database, except for a few important differences. In the first two articles in this series, I touched briefly upon the topic of contained databases when discussing SQL Server access control. In this article, I dig deeper into contained databases and offer several examples that show how to create contained database users, duplicate users across multiple contained databases, and unlink database users from their server-level logins.

Setting Up Your Environments

To try out the examples in this article, you need a test environment that includes a contained database. On my system, I used SQL Server Management Studio (SSMS) to create a simple database and populate it with data from the WideWorldImporters database, although you can use any data that fits your needs.

Before you can implement a contained database, you must enable the SQL Server instance to support this feature, if it’s not already enabled. To use T-SQL to enable contained databases, run the following EXECUTE statement:

EXEC sp_configure ‘contained database authentication’, 1;

RECONFIGURE;

The EXECUTE statement calls the sp_configure stored procedure to set the contained database authentication setting to 1 (on). You should then run the RECONFIGURE statement to implement the changes.

For the examples in this article, create the ImportSales1 contained database, using the following T-SQL script:

USE master;

DROP DATABASE IF EXISTS ImportSales1;

CREATE DATABASE ImportSales1

CONTAINMENT = PARTIAL;

When you create a database, you can specify that it should be contained by including the CONTAINMENT clause in the CREATE DATABASE statement and set its value to PARTIAL. The default value is NONE, which disables the contained database feature. The PARTIAL value is used because SQL Server supports only partially contained databases, as opposed to fully contained databases. Currently, SQL Server does not support fully contained databases.

A partially contained database allows you to implement uncontained features that cross the database boundary. For example, you can create a database user that is linked to a SQL Server login in a partially contained database. Fully contained databases do not allow the use of uncontained features.

After you create the ImportSales1 database, you can add tables and then populate them, just like you can with a non-contained database. To support the examples in the rest of the article, use the following T-SQL script:

USE ImportSales1;

CREATE SCHEMA Sales;

CREATE TABLE Sales.Customers(

CustID INT NOT NULL PRIMARY KEY,

Customer NVARCHAR(100) NOT NULL,

Contact NVARCHAR(50) NOT NULL,

Category NVARCHAR(50) NOT NULL);

INSERT INTO Sales.Customers(CustID, Customer, Contact, Category)

SELECT CustomerID, CustomerName,

PrimaryContact, CustomerCategoryName

FROM WideWorldImporters.Website.Customers

WHERE BuyingGroupName IS NOT NULL;

The script creates the Sales schema, adds the Customers table to the schema, and then populates the table with data from the WideWorldImporters database. The SELECT statement’s WHERE clause limits the results to those rows with a BuyingGroupName value that is NOT NULL (402 rows on my system). If you create a different structure or use different data, be sure to modify the remaining examples as necessary.

Creating Database Users

In SQL Server, you can create users that are specific to a contained database and not linked to server-level logins. Contained users make it possible to maintain the separation between the contained database and the SQL Server instance, so it’s easier to move the database between instances.

SQL Server supports two types of contained users: SQL user with password and Windows user. The password-based user is a database user that is assigned a password for authenticating directly to the database. The user is not associated with any Windows accounts.

To create a password-based user, you must include a WITH PASSWORD clause in your CREATE USER statement. For example, the following CREATE USER statement defines a user named sqluser02 and assigns the password tempPW@56789 to the user:

USE ImportSales1;

CREATE USER sqluser02

WITH PASSWORD = ‘tempPW@56789′;

When a password-based user tries to access a contained database, the user account is authenticated at the database level, rather than the server level. In addition, all authorization granted through assigned permissions is limited to the database.

The second type of contained database user—Windows user—is based on a Windows account, either local or domain. The Windows computer or Active Directory service authenticates the user and passes an access token onto the database. As with password-based users, authorization also occurs within the database according to how permissions have been granted or denied.

When you create a Windows user, be sure that the Windows account is not already associated with a login. If you try to create a Windows user with the same name as a login, SQL Server will automatically associate that user with the login, which means that the user will not be contained.

In the following example, the CREATE USER statement defines a user based on the winuser02 local account, which I created on the win10b computer:

CREATE USER [win10b\winuser02];

Whenever referencing a Windows account in this way, you must use the following format, including the brackets (unless enclosing the account in quotes):

[<domain_or_computer>\<windows_account>]

After you’ve created your contained users, you can grant, deny, or revoke permissions just like you can with any database users. For example, the following GRANT statement grants the SELECT permission on the Sales schema to both users:

GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\winuser02];

You can also add contained users to fixed and user-defined database roles, and assign permissions to the user-defined roles. For more information about creating database users and granting them permissions, refer back to the second article in this series.

Creating Duplicate Database Users

When working with contained databases, you might find that some users need to be able to access multiple databases. For password-based users (SQL user with password), you should create the same user in each database, assigning the same password and security identifier (SID) to each user instance.

One way to get the SID is to retrieve it from the sys.database_principals system view after creating the first user, as shown in the following example:

USE ImportSales1;

SELECT SID FROM sys.database_principals WHERE name = ‘sqluser02′;

The SELECT statement returns the SID value for the sqluser02 user in the ImportSales1 database. The returned value will be unique to that user and will be in a form similar to the following:

0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03

You should use this value whenever you duplicate the user in other contained databases. To see how this works, you can create a database similar to the ImportSales1 database but instead name it ImportSales2, as in the following example:

USE master;

DROP DATABASE IF EXISTS ImportSales2;

CREATE DATABASE ImportSales2

CONTAINMENT = PARTIAL;

USE ImportSales2;

CREATE SCHEMA Sales;

CREATE TABLE Sales.Customers(

CustID INT NOT NULL PRIMARY KEY,

Customer NVARCHAR(100) NOT NULL,

Contact NVARCHAR(50) NOT NULL,

Category NVARCHAR(50) NOT NULL);

INSERT INTO Sales.Customers(CustID, Customer, Contact, Category)

SELECT CustomerID, CustomerName,

PrimaryContact, CustomerCategoryName

FROM WideWorldImporters.Website.Customers

WHERE BuyingGroupName IS NULL;

The script creates the ImportSales2 database, adds the Sales schema to the database, adds the Customers table to the schema, and populates the table with 261 rows of data from the WideWorldImporters database. In this case, the WHERE clause filters for NULL values, rather than NOT NULL.

Next, create the sqluser02 user in the ImportSales2 database, only this time, include an SID clause that specifies the user’s SID from the ImportSales1 database, as shown in the following example:

USE ImportSales2;

CREATE USER sqluser02

WITH PASSWORD = ‘tempPW@56789′,

SID = 0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03;

To create a duplicate Windows-based user, use the same CREATE USER statement you used in the ImportSales1 database:

CREATE USER [win10b\winuser02];

You can also use the same GRANT statement to assign the SELECT permission to the Sales schema for both users:

GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\winuser02];

That’s all there is to creating duplicate password-based and Windows-based users. You can use the same format for creating duplicate users in additional contained databases, depending on your data access requirements.

Running T-SQL Queries

To test the users you created in the contained databases, you can use an EXECUTE AS statement in SSMS to run queries within the execution context of a specific contained user. For example, the following T-SQL sets the execution context to the sqluser02 user, runs a SELECT statement against the Customers table, and then uses the REVERT statement to return to the original execution context:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM Sales.Customers

REVERT;

On my system, the SELECT statement returns 261 rows because the statement ran within the context of the last specified database, ImportSales2. However, the sqluser02 user exists in both databases, sharing the same name, password, and SID, so you should be able to query the Customers table in both databases, as in the following example:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;

Unfortunately, if you try to run the statement, you’ll receive an error similar to the following:

The server principal “S-1-9-3-281107087-1148235999-1775950511-54244962“

is not able to access the database “ImportSales1“ under

the current security context.

The problem is not with how you’ve set up the user accounts or query, but rather with how the TRUSTWORTHY database property is configured. The property determines whether the SQL Server instance trusts the database and the contents within it. Although this might seem to have little to do with contained databases, the TRUSTWORTHY property must be set to ON for the ImportSales2 database because you’re running the query within the context of that database but trying to access data in the ImportSales1 database.

By default, the TRUSTWORTHY property is set to OFF to reduce certain types of threats. You can find more information about the property in the SQL Server help topic TRUSTWORTHY Database Property.

Before setting the property, you must be sure you’re working in the correct execution context. If you’ve been following along with the examples, your session might still be operating within the context of the sqluser02 user. This is because the UNION ALL query in the last example failed, which means that the REVERT statement never ran. As a result, your current SQL Server session is still be running within the execution context of the sqluser02 user. To correct this situation, simply rerun the REVERT statement.

At any point, you can verify the current execution context by calling the CURRENT_USER function:

SELECT CURRENT_USER;

Once you’ve established that you’re working within the context of the correct user, run the following ALTER DATABASE statement to set the TRUSTWORTHY property to ON:

ALTER DATABASE ImportSales2 SET TRUSTWORTHY ON;

Now when you run the following query, it should return the 663 rows from the two tables:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;

You should also receive the same results if you run the query under the execution context of the win10b\winuser02 user, as shown in the following example:

EXECUTE AS USER = ‘win10b\winuser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;

I created and ran all the above examples in SSMS. If you try them out for yourselves, you’ll also likely use SSMS or SQL Server Data Tools (SSDT). In the real world, however, most connections will be through third-party scripts, utilities, or applications. In such cases, the connection string that establishes the connection to the contained database must specify that database as the initial catalog. Otherwise the connection will fail.

Unlinking Database Users from Their Server Logins

Because SQL Server contained databases are only partially contained, they can include users mapped to server logins. The users might have existed before changing the database to a contained state, or they might have been added after the fact. In either case, the database is less portable because of its login connections.

SQL Server provides the sp_migrate_user_to_contained system stored procedure for quickly unlinking database users from their associated SQL Server logins. To see how this works, start by creating the following user in the ImportSales1 database:

USE ImportSales1;

CREATE USER sqluser03 FOR LOGIN sqluser01;

GRANT SELECT ON SCHEMA::Sales TO sqluser03;

The script creates the sqluser03 user based on the sqluser01 login and grants to the user the SELECT permission on the Sales schema. (If the sqluser01 login doesn’t exist on your system, you can also use a different login or refer to the second article in this series for information about creating the sqluser01 login.)

After you create the database user, you can test that it has the expected access by running the following query:

EXECUTE AS USER = ‘sqluser03′;

SELECT * FROM ImportSales1.Sales.Customers;

REVERT;

The query should return all the rows from the Customers table in the ImportSales1 database.

If you view the user’s properties in Object Explorer in SSMS, you’ll find that the General tab shows the associated login as sqluser01 and the user type as SQL user with login, as shown in Figure 1

word image 69 Introduction to SQL Server Security — Part 3

Figure 1. Database user based on a SQL Server login

To unlink this user from the SQL Server login, run the sp_migrate_user_to_contained stored procedure, specifying the database user that you want to migrate, as shown in the following example:

EXEC sp_migrate_user_to_contained

@username = N‘sqluser03′,

@rename = N‘keep_name’,

@disablelogin = N‘do_not_disable_login’;

The sp_migrate_user_to_contained system stored procedure takes the following three parameters:

The @username parameter is the database user.
The @rename parameter determines whether to use the database user or the server login for the name. The keep_name value retains the database user name. The copy_login_name uses the login name.
The @disablelogin parameter determines whether to disable the login. In this case, the login will not be disabled. To disable the login, instead, specify the disable_login value.

After you run the EXECUTE statement, reopen the properties for the sqluser03 user. You’ll find that a login is no longer associated with the user and that the user type has been changed to SQL user with password, as shown in Figure 2.

word image 70 Introduction to SQL Server Security — Part 3

Figure 2. Password-based contained database user

When you unlink a database user from a login, SQL Server assign’s the login’s password to the user, as indicated in the figure. As a security best practice, you should reset the user’s password at this point. If you were to now rerun the following query, you should again receive the same rows from the ImportSales1 database:

EXECUTE AS USER = ‘sqluser03′;

SELECT * FROM ImportSales1.Sales.Customers;

REVERT;

By unlinking the login from the database user, you can take full advantage of the portability inherent in contained databases. Be aware, however, that the sp_migrate_user_to_contained stored procedure works only for SQL Server logins and not Windows logins.

Securing SQL Server Contained Databases

Contained databases can make it easier to move a database from one SQL Server instance to another, without having to worry about duplicating login information between those instances. However, before implementing contained databases, you should be familiar with Microsoft’s security guidelines, described in the SQL Server help topic Security Best Practices with Contained Databases. The topic explains some of the subtler aspects of controlling access to contained databases, particularly when it comes to roles and permissions.

Aside from these guidelines, you’ll find that controlling access to a contained database works much like a non-contained database. You might need to duplicate users across multiple contained databases or unlink database users from their server logins, but these are relatively straightforward processes, much like controlling access in general. Once you understand the basics, you should have little trouble supporting more complex scenarios.

Let’s block ads! (Why?)

SQL – Simple Talk

An Introduction to Cloud-Native Applications

February 6, 2019 TIBCO Spotfire

This is part one in a three-part series on the evolution to cloud-native application development. Next week’s part two will cover cloud-native API management, and tune in February 19 for a special cloud-native announcement.

Applications drive successful digital platforms, and their development and management continue to evolve. While many businesses still operate substantial legacy tech on-premises, many are looking to the cloud for development. But what does it mean to develop a cloud-native app, and why should you care?

Rather than implying where an app resides, cloud-native refers to how it was created and deployed. Cloud-native apps are developed with tools that allow them to take full advantage of cloud benefits, meaning they can be built and changed more quickly, are more agile and scalable, and can be connected with other existing apps more easily.

Now that you understand cloud-native as a concept, let’s explore some of the best practices developers use when creating cloud-native apps, as well as why those elements are vital in driving innovation.

Microservices

Cloud-native apps are developed with loosely-coupled microservices, allowing each microservice to function only as needed, as well as be updated/altered as needed, rather than requiring the full application to run aggregately.

Scalability

Cloud-native apps are deployed on self-service, shared infrastructure, and are built to be elastic and scalable. They are able to scale up to utilize more cloud resources when required while taking up as little bandwidth as possible otherwise. This is beneficial because apps often share tenancy with others, and the ebb and flow of resources can be shared as necessary.

Containerization

Cloud-native apps are built to be a collection of independent, autonomous services packaged together in lightweight containers. These containers are built to be scalable, as well as fully independent from other another. Because the unit of scaling shifts to the containers, the resource utilization is optimized.

Tooling

Best-of-breed languages and frameworks are used in the development of cloud-native apps. In order to create the best app possible, developers will pick and choose which tools to implement into each microservice, which allows the completed application to have the most efficient functionality possible.

DevOps

Each microservice of a cloud-native app goes through an independent life cycle, managed via an agile DevOps process. For the app to function properly, multiple continuous integration/continuous delivery (CI/CD) routes will work together to deploy and manage the application.
There are myriad other features of cloud-native apps that allow them to be faster, more flexible, and more efficient than their legacy counterparts. To learn more about how TIBCO^®can help you learn more about cloud-native technologies, check out TIBCO.com

Let’s block ads! (Why?)

The TIBCO Blog

Introduction to SQL Server Security — Part 1

January 3, 2019 BI News and Info

Security can be one of the most complex issues to contend with when managing a SQL Server instance, yet it’s also one of the most important, especially when sensitive and personal data are on the line. In fact, for many organizations, security is their number one priority, which should come as no surprise, given what’s at stake.

Fortunately, SQL Server includes a variety of tools for protecting data from theft, destruction, and other types of malicious behavior. In this article, the first in a series about SQL Server security, I introduce you to many of these tools, with the goal of providing you with an overview of the options available for safeguarding your data. In subsequent articles, I’ll dig deeper into the different technologies, providing a more in-depth look at how they work and what it takes to protect a SQL Server instance.

SQL Server Authentication and Authorization

Protecting data starts with the ability to authenticate users and authorize their access to specific data. To this end, SQL Server includes an authentication mechanism for verifying the identities of users trying to connect to a SQL Server instance, as well as an authorization mechanism that determines which data resources that authorized users can access and what actions they can take.

Authentication and authorization are achieved in SQL Server through a combination of security principals, securables, and permissions. Before I get into these, however, it’s important to note that SQL Server supports two authentication modes: Windows Authentication, sometimes referred to as integrated security, and SQL Server and Windows Authentication, sometimes referred to as mixed mode.

Windows authentication is integrated with Windows user and group accounts, making it possible to use a local or domain Windows account to log into SQL Server. When a Windows user connects to a SQL Server instance, the database engine validates the login credentials against the Windows principal token, eliminating the need for separate SQL Server credentials. Microsoft recommends that you use Windows Authentication whenever possible.

In some cases, however, you might require SQL Server Authentication. For example, users might connect from non-trusted domains, or the server on which SQL Server is hosted is not part of a domain, in which case, you can use the login mechanisms built into SQL Server, without linking to Windows accounts. Under this scenario, the user supplies a username and password to connect to the SQL Server instance, bypassing Windows Authentication altogether

You can specify the authentication mode when setting up a SQL Server instance or change it after implementation through the server’s properties, as shown in Figure 1.

word image 218 Introduction to SQL Server Security — Part 1

Figure 1. Selecting an authentication mode for a SQL Server 2017 instance

At the heart of the authentication and authorization mechanisms are the principals, securables, and permissions that must be configured to enable users to access the data they need, while preventing unauthorized users from accessing data they shouldn’t.

You can view and work with principals, securables, and permissions through SQL Server Management Studio (SSMS), using either the built-in GUI tools or the available T-SQL statements. Figure 2 shows Object Explorer in SSMS with the expanded Security folder for the WideWorldImporters database and, below that, the expanded Security folder for the SQL Server instance.

Figure 2. Working with security features in Object Explorer

Principals are individuals, groups, or processes that are granted access to the SQL Server instance, either at the server level or database level. Server-level principals include logins and server roles, which are listed in the Logins and Server Roles subfolders in the Security folder:

A login is an individual user account for logging into the SQL Server instance. A login can be a local or domain Windows account or a SQL Server account. You can assign server-level permissions to a login, such as granting a user the ability to create databases or logins.
A server role is a group of users that share a common set of server-level permissions. SQL Server supports fixed server roles and user-defined server roles. You can assign logins to a fixed server role, but you cannot change its permissions. You can do both with a user-defined server role.

Database-level principals include users and database roles, which are listed in the Users and Roles subfolders in the database’s Security folder:

A database user is an individual user account for logging into a specific database. The database user commonly maps to a corresponding server login in order to provide access to the SQL Server instance as well as the data itself. However, you can create database users that are independent of any logins, which can be useful for developing and testing data-driven applications, as well as for implementing contained databases.
A database role is a group of users that share a common set of database-level permissions. As with server roles, SQL Server supports both fixed and user-defined database roles.

For each security principal, you can grant rights that allow that principal to access or modify a set of securables. Securables are the objects that make up the database and server environment. They can include anything from functions to database users to endpoints. SQL Server scopes the objects hierarchically at the server, database and schema levels:

Server-level securables include databases as well as objects such as logins, server roles, and availability groups.
Database-level securables include schemas as well as objects such as database users, database roles, and full-text catalogs.
Schema-level securables include objects such as tables, views, functions, and stored procedures.

Permissions define the level of access permitted to principals on specific securables. You can grant or deny permissions to securables at the server, database, or schema level. The permissions you grant at a higher level of the hierarchy can also apply to the children objects, unless you specifically override the permissions at the lower level.

For example, if you grant the SELECT permission to the user1 principal on the Sales schema in the WideWorldImporters database, the user will be able to query all table data in that schema. However, if you then deny the SELECT permission on the Sales.Customers table, the user will not be able to query that table, but will still be able to access the other tables in that schema.

You can use SSMS or T-SQL to view the permissions that have been explicitly granted to a user on a securable. For example, Figure 3 shows the permissions granted to the user2 principal on the Sales.Customers table. In this case, the user has been granted the DELETE, INSERT, SELECT and UPDATE permissions, with no permissions explicitly denied.

word image 219 Introduction to SQL Server Security — Part 1

Figure 3. Viewing a user’s permissions granted on a schema object

Configuring permissions for multiple principles on multiple securables can be a complex and sometimes frustrating process. If you don’t get it right, you could end up denying permissions to users who should have access to specific data or, worse still, granting access to users who should not. The safest bet is to follow the principles of least privilege, working at the most granular level practical for a given situation.

Additional Access Control Features

SQL Server also provides several other features for controlling access to data. For example, you can implement row-level security on a specific table by creating a security policy that calls one or more predicates. A security policy defines the structure necessary to apply row-level security to a table. A predicate is a table-value function that provides the logic necessary to determine which rows the security policy applies to.

A security policy supports two types of predicates: filter and block. A filter predicate filters the rows available to read operations, and a block predicate blocks write operations that violate the predicate. You can include both filter and block predicates within a security policy, and you can call the same or different predicate functions within that policy.

For example, the WideWorldImporters database includes the FilterCustomersBySalesTerritoryRole security policy, which defines both a filter predicate and block predicate. Figure 4 shows the security policy as it is listed in Object Explorer, along with the policy definition. In this case, both the filter predicate and block predicate call the DetermineCustomerAccess function, which determines which rows the current user can access.

word image 220 Introduction to SQL Server Security — Part 1

Figure 4. Viewing a security policy definition in SSMS

Another SQL Server security feature is the application role, which is similar to the database role, except that it is used specifically to assign permissions to an application. However, unlike database roles, application roles do not contain members. In addition, they’re invoked only when an application connects to SQL Server and calls the sp_setapprole system stored procedure, passing in the name of the application role and password. SQL Server enforces the permissions granted to the application role for the duration of the connection.

Another access mechanism that SQL Server provides is the credential, a server-level object (record) that contains authentication information such as a username and password. The credential makes it possible for a SQL Server user to connect to a resource outside of the SQL Server environment. For example, you can use a credential to run an external assembly or to access domain resources if you’ve logged in using SQL Server Authentication.

SQL Server Data Encryption

Also important to SQL Server security are the encryption capabilities built into the database engine. Encryption provides a way to encode—or obfuscate—data so that only authorized users can view the data in an unencrypted state. To everyone else, the encrypted data looks like gibberish.

Encryption is not an access-control mechanism, that is, it does not prevent unauthorized users from accessing data. However, encryption can limit the exposure of sensitive data should unauthorized users manage to break through SQL Server’s access-control defenses. For example, if cybercriminals acquire encrypted credit card information from a database, they will not be able to make sense of that data unless they’ve also figured out a way to decrypt it.

SQL Server supports several approaches to encryption to accommodate different types of data and workloads. For example, you can encrypt data at the column level by taking advantage of SQL Server’s built-in encryption hierarchy and key management infrastructure. Under this model, each layer encrypts the layer below it, using a layered architecture made up of a public key certificate and several symmetric keys. In this way, the column data is always protected until it is specifically decrypted.

Another tool available to SQL Server is Transparent Data Encryption (TDE), which encrypts and decrypts both data and log files in real-time, working at the page level to ensure that data at-rest is protected. The database engine encrypts the pages before writing them to disk and decrypts them when reading the pages into memory. Unlike column-level encryption, an application does not have to take specific steps to decrypt the data. The entire process occurs behind the scenes.

SQL Server also supports the Always Encrypted feature, which makes it possible for a client application to handle the actual encryption operations, without the encryption keys being revealed to the database engine. However, to implement Always Encrypted, you must first generate the column encryption keys necessary to support Always Encrypted. To do so, you can use the Always Encrypted wizard in SSMS, as shown in Figure 5.

word image 221 Introduction to SQL Server Security — Part 1

Figure 5. Running the Always Encrypted wizard

Once the columns have been encrypted, the data is ready for client access. However, for a client application to connect to encrypted data, it must incorporate a driver that is enabled for Always Encrypted and can handle the encryption and decryption operations.

Another useful SQL Server security feature is Dynamic Data Masking (DDM), a tool for masking all or part of data values. Although DDM doesn’t actually encrypt the data, it does limit the amount of exposed data to non-authorized users. For example, you can use DDM to mask all but the last four digits of a credit card number or national identifier such as a social security number.

SQL Server Tools

SQL Server includes a number of other tools to help protect data and limit risks. For example, you can use SQL Server Configuration Manager to configure startup or connection options, or use the sp_configure stored procedure to configure global SQL Server settings. SQL Server also provides the Surface Area Configuration facets for enabling or disabling features at the instance level, as shown in Figure 6.

Figure 6. Accessing the Surface Area Configuration facets for a SQL Server 2017 instance

You can use these tools to ensure that only those features essential to supporting your users and applications are enabled at any given time, helping to reduce the exposed surface area and consequently the level of risk.

SQL Server also provides tools for identifying potential database issues. For instance, SQL Server provides the TRUSTWORTHY property as one of its database properties. The property shows whether the current SQL Server instance can trust the database and its contents.

In addition, SSMS provides the Data Discovery & Classification feature for classifying, labeling, and reporting on potentially sensitive data in a database, as well as the SQL Vulnerability Assessment (SVA) tool for discovering, tracking, and addressing potential database vulnerabilities. Figure 7 shows the results of running an SVA assessment against the WideWorldImporters database in SQL Server 2017.

Figure 7. Viewing a SQL Vulnerability Assessment report

One of the most valuable SQL Server security tools is SQL Server Audit, which provides a structure for tracking and logging events that occur within the database engine. With SQL Server Audit, you can monitor events at the server level, database level, or both.

SQL Server Audit comprises three primary component types. The first is the audit object, which provides a structure for carrying out the auditing process. The audit object defines a target for the audited events. The target can be log files, the Application log, or the Security log. The audit object also includes configuration settings such as the number and size of the log files.

In addition to the audit object, an audit usually includes a server audit specification, a database audit specification for each applicable database, or a combination of any of these. The specifications determine which events should be audited at the server level or database level. For example, Figure 8 shows a database audit specification that audits UPDATE and DELETE events on the Sales.Customers table.

word image 222 Introduction to SQL Server Security — Part 1

Figure 8. Viewing the audited actions defined in a database audit specification

In this case, both events are specific to the user1 database user. If the user tries to update or delete data in the Customers table, SQL Server Audit will log the event to the target repository.

Along with all these tools, SQL Server also provides a number of catalog views and dynamic management views for accessing security-related data. For example, you can retrieve details about the permissions granted and denied to a specific database user.

Protecting a SQL Server instance

In addition to taking steps within SQL Server to protect data, DBAs should also be certain to implement protections related to the SQL Server instance as a whole, such as disabling unused SQL Server components, applying security patches and service packs in a timely manner, and ensuring that database and backup files are fully protected and secure at all times.

But a protection strategy should not be limited only to SQL Server. The host operating system should also be kept up-to-date and properly patched, with just as much attention paid to surface area reduction.

In addition, DBAs and IT administrators must ensure that the host server is physically protected and that network safeguards such as firewalls and intrusion detection are in place. A SQL Server instance must be both physically and logically protected to achieve the maximum security.

Development teams must also ensure that the applications connecting to a SQL Server instance are properly vetted for security issues. A data-driven application is at risk for a number of attacks, including connection string injections, elevation of privileges, and SQL injections. The teams should factor in data security from the start, when first designing the application, not after it’s been implemented.

Securing SQL Server

SQL Server security is a huge topic, and what I’ve covered here barely skims that surface. You should view this article as only a starting point, meant to alert you to the many security considerations to take into account and the different SQL Server tools available for protecting data.

As this series progresses, I’ll dig deeper into the various security components, fleshing out what I’ve covered here and introducing you to concepts I’ve yet to explore. In the meantime, I recommend you learn as much about SQL Server security as possible, beginning with Microsoft’s own documentation.

Let’s block ads! (Why?)

SQL – Simple Talk