11/16/2018

Getting Started with Azure Data Studio Extension SERVER DASHBOARD

Azure Data Studio is a new product which is similar to SQL Server Management Studio, yet way cooler.  You can manage traditional SQL Server Databases, write T-SQL Queries, as well as new features.

Azure Data Studio has a plethora of Add-In features.  

In this demo, going to outline a new feature: Server Dashboard for Azure Data Studio

You can find the tutorial steps here as well.

After opening Azure Data Studio, we click on "Extensions" button on the left hand side panel, select "Server Reports":




Next, we execute a statement to Alter our database set QUERY_STORE = ON:




I had to add permissions to the local SQL Database to execute the query, it did run:




Next, in Azure Data Studio, a cool new features select View from the dropdown list, select "Command Palette..." a window opens, a list of commands appear alphabetically, type in "SETTINGS", select "Preferences: Open User Settings":




Next, select Dashboard Database Widgets:




Next, follow the web tutorial instructions:




In Azure Data Studio, you will see 2 panes left and right.  The Left hand side is not editable (you can't eat it!), so what I had to do is copy the contents from Left hand pane, paste into the Right hand pane, splice carefully or the JSON will show red highlight that it's not formatted correctly:




When you close out, it will prompt to save, or click Ctrl+S :


Next, back in the Servers window, select the Server, right click, select "Manage":




A window opens to the right, containing our new widget "SERVER DASHBOARD":




Not much activity on this database, if there were, you can analyze deeper.

And that concludes our demo on Azure Data Studio Extension for Server Dashboard.  There are many other Extensions to download / install as well so this should keep us busy for a while.


Thanks for reading~!

11/13/2018

Getting Started with Dynamic Data Masking in Azure SQL Database

Azure SQL Database provides Dynamic Data Masking out of the box. 



This blog reviews the commands required to set up a Dynamic Data Masking, and bundles in Row Level Security as well.

First, we logged into Azure Portal with valid credentials and subscription  Then selected Azure SQL Database:


Next, we created a new database.  The wizard prompted for information:





Next, we created a new Resource Group for this demo and created a new Server and Database:



Our newly created Azure SQL Database was provisioned:





We already had the new Azure Data Studio downloaded and installed on laptop.  It's a new tool similar to SQL Server Management Studio (SSMS).  

We connected to our newly created Azure SQL Database.   Azure SQL Database requires a setting in Azure SQL Database Firewall, so we need to add current IP Address to allow access from remote laptop:




Using traditional SQL, we ran some  scripts to test the Data Masking functionality, as follows:


------------------------------ -- DROP USERS IF THEY EXIST   DROP USER IF EXISTS TestUser;   DROP USER IF EXISTS TestUser2;   DROP USER IF exists TestUser3; -- DROP TABLE IF EXISTS   DROP table IF EXISTS dbo.Membership; ------------------------------
-- CREATE DATABASE TABLE FOR TESTING CREATE TABLE dbo.Membership    (MemberID int IDENTITY PRIMARY KEY   UserName varchar(100) NULL,    FirstName varchar(100)   NULL   LastName varchar(100)    NULL   Phone varchar(12)        NULL   Email varchar(100)       NULL,    SSNbr varchar(11)        NULL);  -------------------------------- -- INSERT 3 ROWS 1 FOR EACH USERNAME INSERT dbo.Membership (UserName,FirstName, LastName, Phone, Email, SSNbr) VALUES   ('TestUser2','Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com','123-45-6789'),  ('TestUser3','Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co','9876-54-321'),  ('TestUser', 'Juan','Mu', '555.123.4569', 'ZMu@contoso.net','555-44-3211');  ------------------------ -- CREATE A SCHEMA FOR SECURITY CREATE SCHEMA Security; ------------------------ -- CREATE A FUNCTION TO TEST ROW LEVEL SECURITY CREATE FUNCTION Security.fn_securitypredicate(@Username AS sysname    RETURNS TABLE  WITH SCHEMABINDING  AS      RETURN SELECT 1 AS fn_securitypredicate_result   WHERE @Username = USER_NAME() OR USER_NAME() = 'TestUser2'; --------------------- -- CREATE A SECURITY POLICY THAT REFERENCES ABOVE FUNCTION CREATE SECURITY POLICY FilterDemo ADD FILTER PREDICATE Security.fn_securitypredicate(UserName)   ON dbo.Membership  WITH (STATE = ON);  --------------------- --CREATE TEST USER 1 CREATE USER TestUser WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser; ----- --CREATE TEST USER 2 CREATE USER TestUser2 WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser2; ----- --CREATE TEST USER 3 CREATE USER TestUser3 WITHOUT LOGINGRANT SELECT ON dbo.Membership TO TestUser3; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser' RETURNS 1 ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser2' RETURNS 3 (ALL) ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser2'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser2' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RUN SELECT STATEMENT AS USER 'TestUser3' RETURNS 1 ROW -- AS ROW LEVEL SECURITY IS IMPLEMENTED EXECUTE AS USER = 'TestUser3'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser3' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------
The first query returns 1 row, run as "TestUser".

The second query returns 3 rows, run as "TestUser2"
.

The third query returns 1 row, run as "TestUser3".

Reason being, we implemented a function, to handle Row Level Security.  Row Level Security is another Security feature out of the box in Azure SQL Database.  You can see the LastName and SSNbr fields are not masked, yet.

To get Dynamic Data Masking, we add the following code snippets:
------------------------ -- APPLY DATA MASKING TO LASTNAME FIELD OF MEMBERSHIP TABLE
ALTER TABLE dbo.Membership  ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'default()'); --- -- APPLY DATA MASKING TO SSNBR FIELD OF MEMBERSHIP TABLE ALTER TABLE dbo.Membership  ALTER COLUMN SSNbr varchar(100) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'); ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser' RETURNS 1 ROW -- 2 FIELDS ARE MASKED AS EXPTECTEDEXECUTE AS USER = 'TestUser'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser2' RETURNS 3 (ALL) ROW -- 2 FIELDS ARE MASKED AS EXPTECTED EXECUTE AS USER = 'TestUser2'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser2' as QueryType,User,* FROM dbo.Membership; 
REVERT; ------------------------ -- RE-RUN SELECT STATEMENT AS USER 'TestUser3' RETURNS 1 ROW -- 2 FIELDS ARE MASKED AS EXPTECTED EXECUTE AS USER = 'TestUser3'SELECT USER_NAME()  as UserQuery,'Run as User=TestUser3' as QueryType,User,* FROM dbo.Membership;  REVERT; ------------------------
Here's the output from all 3 queries. You can see the Dynamic Data Masking. Fields are LastName & SSNbr.

------------------------ -- TO GET A LIST OF FIELDS THAT HAVE DYNAMIC DATA MASKING, RUN THIS SCRIPT SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  FROM sys.masked_columns ASJOIN sys.tables AS tbl       ON c.[object_id] = tbl.[object_id]  WHERE is_masked = 1; ------------------------
Source code for this demo was found here. Thanks for reading~!

11/12/2018

Using Microsoft Data Migration Assistant

Worked with Microsoft Data Migration Assistant.  Simple and powerful application.

Simply download, open program, Click + for new, selected Migration...





Connect to Source Database...




Select the source database you wish to migrate from the available list...





Had to create an Azure SQL Database destination in Azure Portal to migrate too...




Connect to Destination Database...



Select the objects to migrate...




The database scripts get generated automatically...




Next, execute the scripts...opening the Azure SQL Database from SQL Server Management Studio:



We can see the table got created in Azure SQL Database...




You can store off the scripts for re-use, and you can push the data to Target database from within the application as well.

Nice product offering from Microsoft to Migrate data to and from places.

Thanks for reading~!

Getting Started with Azure Serverless Functions

Today decided to test out Azure Functions.  Functions are stand alone server-less functions that reside on Azure.  Here's a get started link.

In Azure Portal, we select "Create a Resource" --> "Serverless Function App":




Selected Serverless Function App...configured:




Objects were created:




Going to Resource Group, we select our newly created objects:




Viewing Dashboard:




Viewing objects in Microsoft Azure Storage Explorer:



Back in Azure, we can see the Services information:



Create a new Function:




More functions to choose from:



And more functions to choose from:



We selected "HTTP Trigger" which should send back a Response based on a web Request.


New Function:




It dynamically created the output code, we select the hyperlink to copy the URL:




Opened a new browser, appended "&name=Jon which resulted in:




There's also a console which expands, to test the Function, simply copy the URL string, add a Parameter "name" & "JonB", be sure to change to "Get" rather than "Post" as Get allows query string parameters, ran, success!




Can also view the "logs" in real time:




Going back to Microsoft Azure Storage Explorer, we now have more data to view:




Then we deleted the Resource Group which removed all artifacts associated with this example.

Again, here's the link I followed to get started.

As always, thanks for reading~!

And now for some Microsoft Azure BlockChain Basics


Today I like to get started with Microsoft Azure Blockchain.  

Blockchain is a distributed ledger on the internet that allows for secure transactions in near real time guaranteed by smart contracts.  Although fairly new technology, Microsoft has a solution bundled into its Azure Portal site.

From my knowledge, distributed ledgers can be applied to many use cases.  Anytime you need to keep track of transactions, Blockchain places the next transaction on top of the current stack.  It's based on global unique identifiers or GUIDS in the form of HASH.  These HASH are created by using the prior HASH key, so you can not place an invalid transaction into the stack without the correct key.  This ensures secure transaction with guaranteed placement in near real time.  It kind of solves our security issue on the cloud.

So not only could it be used in financial transactions, good example is Crypto Currency, it could be used for tally results for Elections, Insurance transactions, any time you need to keep a Ledger account.  And the transactions are dispersed across the internet on multiple hosts to allow for distributed accounts.  This has great potential as the use cases are endless, and from my understanding, the only draw back to widespread adoption has been "scalability" as in millions of transactions per second.

With that said, lets get started with Microsoft Azure Blockchain solution example.

Logging into the Azure Portal, when you locate the Blockchain item, we see:




Click on "Learn more"...




Click on Documentation links towards the bottom of page, we find good examples on getting started:


Blockchain Workbench Preview

https://azure.microsoft.com/en-us/features/blockchain-workbench/






Azure Blockchain Workbench Documentation

https://docs.microsoft.com/en-us/azure/blockchain/workbench/




Create a blockchain application in Azure Blockchain Workbench

https://docs.microsoft.com/en-us/azure/blockchain/workbench/create-app



Need to add Azure Active Directory

https://docs.microsoft.com/en-us/azure/blockchain/workbench/manage-users#add-azure-ad-users




I don't have Azure Active Directory loaded, so going to pivot to IDE.

For an IDE to develop the code, we can download using this link, explains how to download Visual Studio Code and apply the necessary installs for editing Solidity smart contracts.

Download Visual Studio Code:










Open Visual Studio Code, to change the color scheme, File -> Preferences -> Color Scheme, selected Visual Studio Light:




To load "solidity" framework for VS Code, click on button in red, enter "solidity" in search, "Ethereum Solidity Language for Visual Studio Code":





Now we can download a blockchain template located here:


Logging into GitHub account, we see the project artifacts to clone:





First, would like to download the GitHub Desktop:




After cloning the repository we see the project in GitHub Desktop:





Need to download the GitHub Extensions for Visual Studio:









We can download the sample scripts here:

To clone the repository:




We first need to download Git:











Back in Azure Portal, we create a BlockChain application:




Step 2 requires Active Directory, get prompted to create Storage Account:




Azure Blockchain requires an active Azure Active Directory, which I don't have, so I'll conclude the blog here.  If you'd like to continue on your own, here's the link to get back to the tutorial

Thanks for reading~!