1/16/21

Create a read-only user on Azure SQL Server

Database permissions to enable users to either manage data and/or data definitions object like tables, views and other is a key security concern on a database. In some cases, there is the need to allow a user to access the data only. To do that, we need to create a read-only user on the database. With this article, we look at the steps that are needed to create a read-only user on an Azure SQL Server database.


Note: To follow this article, an Azure subscription with a database already deployed is recommended.

Find the Server Information:

We should start this process by first getting the SQL Server instance URL address from the Azure Console. Login the Azure console and select or search for the SQL Server resources.  Look at the results and select the server hosting the database that needs the additional user profile.

After selecting the server, make sure a server is selected not a database instance, we should see the server information overview. From this view, we can find the server URL name and the administration login account. The password is not visible from the view, so we should get that information from our security software like Key Vault or wherever the passwords are kept. We need this information to be able to login into the database remotely, so we can add the new user profile.

But before we login to the server, we also need to add the client IP address to the server firewall configuration. This enables remote client application to reach the database.  From the server information page, search or select “Firewall and virtual networks”.  We can then click the “+ Add Client IP” button. This reads the client IP address from browser. This information is available because we are using a browser to access the console.  This adds our current IP address information to the firewall rules to enable the access. Once that looks correct, we should now press save to make sure the update is made.

Ready to Connect

We should have all the required information to connect to our database remotely using tools like Visual Studio Code (VSCode) or SQL Server Management Studio (SSMS).  VSCode is a development tool that can target many platforms and languages. SSMS is designed specifically to work on data platforms like SQL Server.

Once your preferred tool is open, we are ready to login, we should connect to the target environment and database by using the server URL and admin credentials. Once the connection is set, we can open a query window, so we can add the following code:

Note:  Make sure to change the login, username and target database to match your environment.

 

-- select master database to create the login profile

USE master;

GO

 

-- creates the login account

CREATE LOGIN [rptLogin] WITH password='add-pw-here';

-- DROP LOGIN rptLogin;

 

-- enable the access to login to the database

CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo];

-- DROP USER [rptUser];

 

-- move to the target database context

USE mydb;

GO

 

-- create the user on the target database

CREATE USER [rptUser] FROM LOGIN [rptLogin] WITH DEFAULT_SCHEMA=[dbo];

 

-- add the data reader role to the user

EXEC sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'rptUser'

 

Gitub Gist


Before we run the script, let us break down the code and understand what is going on.

Create Login Account

To create an account to access the database, we must first create a “Login” profile on the master database which host the system database configuration and security for all the databases. We do this by first switching to the master database context using the “USE” command. We then create the login profile “rptLogin” with the default schema information “dbo”.  We also add a user profile “rptUser” to the master database from the login recently created to enable the user to login to the server even if not database permission has been granted yet to this user profile. This step is optional. It is more important to create the user in the target database.

Create Database Read-Only User

At this point, there is a login account, but the user cannot access any database. This leads us to the next step. We need to switch context to the target database by using the “USE” command. We can then create the same user “rptUser” under this database security context. This is the step to grant the user access to a database.   To make this user read-only, we need to assign a role. This is done by granting this user the “db_datareader” role under the selected database context.

Now that we have understanding on what the script would do for us, we can execute the script and create the read-only user account. if there are no error messages, we should be able to use the login credentials to access the database.  To login with the new account, open another database connection and use the login credentials “rptLogin”.  Please notice that we need to use the login account as this is the profile with an assigned password.  

Conclusion

In cases when users required to access the database for read-only purposes, we can create user accounts with the “db_datareader” role on a specific database.  With this role, the user would only be able to read data by running Select statements. Any attempt to insert, update or delete operations are not allowed as this requires the “db_datawriter” role. By only granting the “db_datareader” role, we limit the access thus making our database more secure from possible wrong operations.

Thanks for reading

Originally published by ozkary.com

12/5/20

Building App Chatbots Power Virtual Agents

Power Virtual Agents Chatbots Overview (6 of 6)


In this video, we discuss the use cases to build chatbots. We show how to build topics, entities to build a chatbot flow that can enable users to search information from the SharePoint data list. We use Power Automate to integrate an OData API call from the Chatbot to SharePoints, so we can filter on items based on a selected filter.

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.


Chatbots by Oscar Garcia @ozkary


Platform Review

  • AI-powered chatbots to resolve common dialogs with users
  • Natural language understanding
  • Integration with other services via Power automate
  • Deploy to websites, mobile apps, MS Teams channels and other collaboration tools

Use Cases

  • Enable business processes
  • Customer support
  • Automate dialog driven tasks
  • Integrate with other skills for voice automation , Text-To-Speech scenarios and integrations


Originally published by ozkary.com

12/3/20

Building Apps Power Apps Overview

PowerApps on Microsoft 365 Overview (5 of 6)


In this video, we build a mobile application with PowerApps. We integrate the app with a SharePoint data list and PowerBI online dashboard all in real-time. We start by creating an app from the SharePoint data list. After looking at the boiler-plate app features, screens and navigation, we make changes to support application level variables, toast notifications, flyout menus. We also add a new screen to embed a PowerBI dashboard.

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.


PowerApps by Oscar Garcia @ozkary


Platform Review

  • Look at data connectors and services
  • Building mobile apps

Build an App with SharePoint

  • Create a new App – SharePoint connector
  • Look at gallery, view and edit screens
  • Review Navigation

Advanced Concepts

  • Input Validation, notification and error handling
  • Visibility, Filtering, sharing data between screens
  • Demo real use case

Publish and Share App

  • Review how to publish and recover previous versions
  • Export App to deploy on other tenants

Using PowerApps Mobile App

  • Install on your device
  • Connect with your credentials and run the published app




Originally published by ozkary.com

12/1/20

Building Apps PowerBI Overview

Power Platform on Microsoft 365 Overview (4 of 6)


In this video, we provide an overview of the Microsoft 365 PowerBI Desktop and Online service. We talk about the different areas of this platform. For example, the desktop application which is free to use provides the ability to design the reports. This also enables us to also publish reports to the Online services, so we can build dashboards. 

To show the capabilities, we build a report in real-time by connecting to a SharePoint data list. This is done to enable us to build visualization reports to see what our data really means. After we publish a report, we embed the report in our PowerApps app.

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.


PowerBI Desktop and online by Oscar Garcia @ozkary

Microsoft PowerBI

  • Set of services, apps and connectors that enables us to build data visualization solutions
  • PowerBI Desktop for pro-editing
  • Online SaaS service to publish and share dashboards
  • Mobile apps for Windows, Android and iOS devices
  • Aggregate data from multiple sources for analysis and visualization
  • Embed dashboards on PowerApps solutions




Originally published by ozkary.com

11/15/20

Building Apps Power Platform Overview

Power Platform on Microsoft 365 Overview (3 of 6)


In this video, we provide an overview of the Microsoft 365 Power Platform. We introduce the major services that are available, and we talk about how to leverage them when building an application.  As an example, we take a SharePoint Web form and enhance it with a PowerApps Web form. We also integrate the SharePoint app with Power Automate by pushing new records to Microsoft Team to show team collaboration capabilities.

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.


Power Platform by Oscar Garcia @ozkary

Platform Review

  • Build low-code apps quickly
  • Integration with data connectors
  • Integration with Azure apps and security (Azure AD)

PowerApps

  • Create mobile enabled apps for Web, Android and iOS devices
  • Data model first development

Power Automate

  • Workflow Automation
  • Process improvements
  • Connect to multiple services

PowerBI

  • Unify Data from different sources
  • Visualize your data and integrate with other apps
  • Online dashboards with PowerBI online

Power Virtual Agents

  • Create conversational chatbots  to engage with users
  • Enable the search of information

Originally published by ozkary.com

11/12/20

Building Apps SharePoint Overview

SharePoint Microsoft 365 Overview (2 of 6)


In this video, we provide an overview of SharePoint Web Portal features, content and document management systems. Since we are building an application, we focus on building a data list which functions as a database table to store our app data. We take a model-first approach to define the model meta-data and constraints. We then build the application front-end.

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.


SharePoint Platform by Oscar Garcia @ozkary

What is SharePoint?

  • Enterprise web portal and Sites
  • Content Management System (CMS)
  • Web apps, business process, document management
  • Communication, collaboration 
  • Low-code portal for external users via federated security

Data List

The video covers the following implementation details in real-time.

  • Like a database table stored in the content database
  • Use it to build apps with CRUD operations
  • Views, Filters, Alerts and Workflows
  • Integration with PowerApps and Power Automate to build business processes


Originally published by ozkary.com

11/11/20

Building Apps Microsoft 365 Overview

Microsoft 365 Overview (1 of 6)


In this video, we provide an overview of the Microsoft 365 platform. We explain some platform services, so developers may find a use case applicable for their needs. In order to make this practical, we build an actual application in real-time. We do this to show the capabilities of the platform. 

 

Welcome to the building apps with Microsoft 365 series. This is a series of six videos. In each video, we discuss a technology area on the Microsoft 365 platform. If you follow this series, you will be able to build an application which can run from your mobile device by downloading the PowerApps mobile app from the app store.




Microsoft 365 Platform by Oscar Garcia @ozkary


Product Management App

  We build this app by looking at the following areas:

  • Data and Web application with SharePoint and PowerApps
  • Process Automation and collaboration with Power Automate and MS Teams
  • Mobile app with PowerApps
  • Data visualization with PowerBI
  • Chatbot Search Automation with Virtual Power Agents




Originally published by ozkary.com

11/9/20

Microsoft 365 Power Platform Overview


This is a presentation for the Microsoft 365 Developer Bootcamp.

powerapp solution


In this bootcamp, we build an app by using the following technologies:
  • SharePoint Data List
  • PowerApps Web Forms
  • Power Automate flows with MS Teams
  • PowerBI desktop and online dashboards
  • PowerApps mobile app with PowerBI dashboard
  • Power Virtual Agent(chatbot) with Power Automate flow and SharePoint data list OData  calls
Github Repo:  https://github.com/ozkary/sp-addin-todo

Chatbots

ozkary chatbot



Originally published by ozkary.com

10/10/20

SOAP API to REST with Azure API Management and Visual Studio Code

A SOAP WSDL (Web Service Description Language) is an XML-based interface description language that is widely used by older APIs. With the modernization of Web standards, most APIs today use REST (Representational state transfer) and JSON (JavaScript Object Notation).  In some cases, there may be a need to convert a SOAP API to REST without having to refactor the API. Luckily for us, Web development tools and Azure can help us get this done.

Azure API Management Service

With the evolution of Web tooling and Cloud computing, we can continue to use SOAP APIs by importing a WSDL definition and creating an OpenAPI (JSON) definition using Visual Studio Code (VS Code) and Azure API Management service. Let us see how we can quickly do this.

We first need to open VS Code and have an Azure subscription. We can get a free Azure subscription by visiting azure.com. Once VS Code is running, we need to install the Azure API management extension from the extension’s menu.  This extension enables us to connect to Azure and provision new API services.

From the Azure API Management extension menu (Azure Icon – Left Bar), select the target subscription. Resources need to be created under a subscription. Right click and select Create “API management in Azure”.  This action opens an edit box on VS Code command palette (top-center) which prompts for the API name.  We should try to use something that is unique to your brand or company to avoid name collisions from other users. 

After the API management service is provisioned, the VS Code extension should display the recently created service. At this point, we can add a new API from a WSDL document. To do that and due to limitations on this extension, we need to open the API service from the Portal.  We can do this by doing a right click on the API service name and selecting “Open in Portal”.

Once the Azure Portal loads on the browser, we can click on the “Add API” menu option. This action loads a view which shows all the supported standards.


API Standards

For our case, we should select WSDL. At this point, we need to have either a link or the actual WSDL file.  We also need to have the single WSDL file that contains all the web operations.  We can easily get that file by loading the SOAP API endpoint with the URL parameter singleWsdl as shown below:

 

https://api.ozkary.com/endpoint.svc?singleWsdl

 

 

Note: Replace the URL with the target endpoint

When we have either a link or the actual file, we can import it into the Azure API Management console.  For the import process, select the “SOAP to REST” option. This option enables us to convert the XML metadata into a REST metadata which is used to build the Web operations with JSON.  We should also use the API URL suffix to separate different areas of the APIs. This appends the suffix to the endpoint-based URL.

Import WSDL to REST

 

Recursive Error

 

Error: Parsing error : Unable to import API from WSDL: Element named 'http://schemas.ozkary.com/sample.xsd:Product' has a recursive definition. Recursive types are not supported.

 


If there is a recursive error, the import will not be able to complete the process. The error indicates that in one of the SOAP complex types, there is a property that has itself as type definition, and this causes the engine to do a recursive look up. This leads to infinite lookups which will never end thus causing the error.  The only way to fix this error is to change the complex type definition by removing the recursive type reference.

To illustrate this problem, let us look at an example. In the following Product complex type, we can see that there is a Product property which uses the same type (Product). This causes a recursive error as it endlessly looks at the type definition.

 

<xs:complexType name="Product">

    <xs:sequence>

        <xs:element minOccurs="0" maxOccurs="1" name="Name" type="xs:string"/>

        <xs:element minOccurs="0" maxOccurs="1" name="SKU" type="xs:string"/>

        <xs:element minOccurs="0" maxOccurs="1" name="Product" type="tns:Product"/>        

    </xs:sequence>

</xs:complexType>

 

 

Testing the API

Once the WSDL is processed successfully, we can now look at the operations from the portal and even test them using the REST format.  This would be like how we can test using Postman or Swagger UI.  Note that there are two ways of testing the API. We can either use the Azure Portal or go back to VS Code and test the operations from the IDE. Independently of the selected method, the goal is to validate that the REST endpoint work.

Exporting to OpenAPI

Exporting the API metadata is very convenient for the purpose of generating documentation and client code for multiple languages like CSharp, JavaScript etc.

To export the API with all its operations using REST, we can click the ellipses (…) next to the API title and click “Export API”. At this point, we want to select of the OpenAPI standard. Most tools support both JSON and YAML formats

The export enables us to download all the operations in the format needed to properly document the APIs using tools like Swagger UI which shows all the API definitions and JSON payloads.

Conclusion

By using VS Code and Azure API Management service, we can quickly transform a legacy SOAP API into the modern OpenAPI standard without the need to re-code the API. Keep in mind however, that the SOAP API continues to be a system dependency under the hood. Nevertheless, we have taken an approach that can quickly help us provide a REST endpoint and use modern test and documentation tools to continue to support our SOAP APIs.

Thanks for reading.


Originally published by ozkary.com

9/12/20

Dataverse for Business Applications

With the improvements to the Microsoft 365 platform, there are ongoing changes and new concepts added to the platform. The former Common Data Service is now branded as the Dataverse.  This is a cloud-based solution that provides database and integration services to business applications hosted on Microsoft 365 Power Platform.  The main objective of Dataverse is to be the central data repository for all the business information, but this service is more than just a database service.

Dataverse


This service provides core features that can enable the rapid development of business solutions including those with low-code approach like PowerApps. This accelerates the building of business solutions by focusing only the application components and rely on the platform for the cross-cutting technical concerns.  The areas of these core features include security, logic, data, storage, and integration.

Security

Authentication is managed by Azure AD (Active Directory). The authorization feature provides support at the row and field level on a database. It also includes an auditing capability for compliance purposes.  This means that developers do not have to implement login features, permissions to access some data and creating audit logs to track activities on the data.  Of course, there may be some use cases that are not supported, and additional implementation would be required. But for the most part this should handle a high percentage of the security concerns for an application.

Logic

Business rules, duplicate detection, calculated fields and workflows at the data level are also supported. This enables the management of business logic to be centralized at the data level, so regardless of what application or API access the data, the same business logic is maintained.

Data

Dataverse also provides data transformation, data modeling, reporting, data validation features.  These features can help us shape the data in compliance with the needs of consumer applications or other systems. This also enables reporting tools like PowerBI to build better reporting by using the existent models or extending them based on the business need.

Storage

The data is stored in the Azure cloud in the form of Relational databases (SQL Server), files, blobs, semi-structure data, data lakes.  Depending on the type of solution we are building, there is plenty of storage methods that can be used. This enables us to centralize the data from other sources and different data formats.

Integration

There are multiple integration methods to support a business solution. We can use web hooks, APIs, event notification to build integration points with web application. There are also data export capabilities which can enables us to build a data warehouse or export data to a reporting system.

The Microsoft Dataverse provides many features and capabilities that can enable a development team to build business solutions much faster by focusing only on the application business components instead of the cross-cutting features. 

Technical Considerations

Like any technology, we need to also understand some technical considerations from the platform. The obvious one is that this is hosted on the Azure/Microsoft infrastructure, so there needs to be affinity with the Microsoft cloud platform.  This also works best for internal users as the security is managed by Azure AD.  Both of those considerations go very well when the business organization is already using the services from the Microsoft 365 platform, so there are probably platform experts within the organization.

We have covered just a few of the features of the Microsoft Dataverse service. There are so many other areas to understand and learn, but we have provided information on the basic areas that are related to building business applications for an organization.

Thanks for reading.

Originally published by ozkary.com