Welcome!

Microsoft Cloud Authors: Janakiram MSV, Yeshim Deniz, David H Deans, Andreas Grabner, Stackify Blog

Blog Feed Post

Data Security Using SQL Azure



One of the major concerns in using SQL Azure is the security of data such as credit card numbers, Social Security numbers, salaries, bonuses etc. The degree to which data needs to be protected is to be determined by each business entity but generally, on-site data is more secure than data stored in the cloud.
This is a simple example of using SQL Server Integration Services SIS and SQL Server Reporting Services tools to accomplish just that.
We start off with this scenario: The fictitious company SecureAce wants to place one of their Employee tables on SQL Azure, but they do not want to keep any sensitive information such as employee salaries. However from time to time they need to generate report of their employees and salaries to management.
The solution to this scenario is divided in two parts.
In the first part, the on-site data in the employees table is partitioned in such a way that the sensitive information stays on-site and the larger, non-sensitive data is stored on SQL Azure.
In the second part SSIS is used to bring the two pieces of data together and load an Access database (on-site) which is used as a front end for reporting information to management, an entirely realistic way of data management. Although a Microsoft Access database is used, any other destination handled by SSIS can also be used[s1] , such as another SQL Server database. Herein we used MS Access as it is a very common product used in many small businesses.
 It may be noted however that Microsoft is now supporting connecting SQL Azure to MS Access directly, review this link for details: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/05dd7620-f209-43d2-8c41-63b251c62970. With the availability of Microsoft Office Professional Plus 2010, the author was able to directly connect to SQL Azure using an ODBC connection.
Splitting the data and uploading to SQL Azure
This is a preparation for the SSIS task that follows. We will be using Northwind database’s Employee table and splitting it in two parts each containing different columns, a vertical partition. One part will remain on site which contains the salary information of employees and the other which is loaded to SQL Azure will contain most of other information.  In the Northwind database, the employee table does not have a salary column and hence an extra column will be added for this simulation. The procedure is described in the following[s2]  steps[Maitreya3] .
·         Create a table Employees in VerticalPart using the following statement:
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] PRIMARY KEY CLUSTERED NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[HomePhone] [nvarchar](24) NULL,
[Extension] [nvarchar](4) NULL,
[Salary] [money] NULL
)
·         Use Import / Export Wizard to populate the columns (except Salary) of the above table using Northwind's Employees table
·         Modify table by adding salary for each employee
[s6] [j7] There are only few employees and this should not be a problem. When you want to save the table, you may not be able to do so unless you have turned-on this option, in the Tools menu of SSMS. You will get a reply after you save [s8] [j9] the Employees table as shown.

Now run a SELECT query to verify that the salary column has been populated as shown.


Copy the script for Northwind’s Employee table and modify it by changing the table name and removing some columns resulting in the following statement:

CREATE TABLE [dbo].[AzureEmployees](
[EmployeeID] [int] PRIMARY KEY CLUSTERED  NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
[TitleOfCourtesy] [nvarchar](25) NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15)
)
Note that the table name has been changed to AzureEmployees. This is the table that will be stored in the Bluesky database on SQL Azure.
Login to SQL Azure and create the table in Bluesky database by running the above create table statement.
The table will be created with the above schema which you may verify in the Object Browser.

Use Import and Export Wizard to populate the columns of AzureEmployees with data from Northwind. Use the query option to move data from source to destination using the following query.
SELECT EmployeeID, LastName, FirstName,
Title, TitleOfCourtesy, HireDate,
Address, City,Region, PostalCode,
Country
FROM
Employees
Save the query results to the AzureEmployees table you created earlier as shown. 

 
Follow wizard’s steps to review data mapping as shown


Complete the wizard steps as shown.


Verify data in AzureEmployees in Bluesky database on SQL Azure by running a SELECT statement.
By following the above we have created two tables, one on-site and the other on SQL Azure.
Although data transformation of string data types did not present any error due to string length it could present some problems if the string length is over 8000 if the strings are of type varchar (max) and text. In these cases just change them to nvarchar (max) to overcome the problem. For details review the following link:  http://blogs.msdn.com/b/sqlazure/archive/2010/06/01/10018602.aspx
Merging data and loading an Access database
In this section we will reconstruct the Employees table on-site by retrieving data from SQL Azure as well as SQL Server’s VerticalPart database and merge them. After merging them, we will place them in an MS Access database so that simple reports can be authored.
In order to do this we take the following steps.
  1. Click open BIDS from its shortcut.
  2. Create a Integration Services Project after providing a name for the project. Change the default name of the Package file.
The Project folder should appear as shown in the next image. Project name and Package name were provided.

  1. Drag and drop a Data Flow task to the Control Flow tabbed page of the package designer surface.
  2.  In the bottom pane Connection Managers, configure connection managers one each for SQL Azure database; VerticalPart database on SQL Server 2008; and an MS Access database as shown.



The next image shows the details of the connection manager Hodentek3\KUMO.VerticalPart. Note that SqlClient Data Provider is used. The SQL Server Hodentek3\KUMO is configured for Windows Authentication.



This next image shows the connection xxxxxxxxxx.database.windows.net.Bluesky.mysorian1 for the Bluesky database on SQL Azure. The authentication information is the same one you have used so far and, if it is correct you should be able to see the available databases.


  1. Create an MS Access database (Access 2003 format) and use it for this connection.
Later we also create a table in this database to receive the merged fields from SQL Azure and the on-site server.
For this connection manager we use the following settings and verify by clicking the Test Connection button:
Provider:                 Native OLE DB\Microsoft Jet 4.0 OLE DB Provider
Database file is at:  C:\Users\Jay\AccessSQLAzure.mdb
User name:              Admin
Password:               <empty>

It is assumed that the reader has familiarity with using SSIS. The author recommends his own book on SSIS for beginners, which may be found here: https://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book.
Each of the above connections can be tested using the Test Connection button on them.
Merging columns from SQL Azure and SQL Server
You will use two ADO.NET Source data flow sources, one each for SQL Azure and SQL Server. The outputs will be merged.
  1. Add two ADO.NET data flow sources to the tabbed designer pane Data Flow.
  2. Rename the default names of the source components to read From SQL Azure Database and From SQL Server 2008 database.



  1. Configure the ADO.NET Source Editor connected to SQL Azure to display the following as shown in the next image.
ADO.NET Connection manager: XXXXXXX.database.windows.net.Bluesky.mysorian1
Data access mode: Table or view
Name of the table or view: "dbo"."AzureEmployees"
You must use the server name appropriate for your SQL Azure instance.

Configured as shown and you should be able to view the data in this table with the Preview…button.


  1. Configure the ADO.NET Source Editor connected to SQL Server to display the following as shown in the next image.
Use the following details to configure  From SQL Server 2008 database source used in the ADO.NET Source Editor are as follows:
ADO.NET Connection manager: Hodentek3\KUMO.Verticalpart
Data access mode: Table or view
Name of the table or view: "dbo"."Employees"


Again you should be able to view the data in this table with the Preview…button.
Sorting the outputs of the sources
Since the data coming at the exit point of the sources are not sorted it is important to get the sorting correct and same in both sources before they can be merged.
  1. Drag and drop two Sort dataflow controls from the Toolbox to the design surface just below the ADO.NET data sources.
  2. Start with the one that is going to be receiving its input from the From SQL Azure Database source control.
  3. Click From SQL Azure Database and drag and drop the green dangling line on to the Sort control below it as shown.



  1. Double click the Sort control to display the Sort Transformation Editor and place a check mark for EmployeeID as shown.

  1. Repeat the same procedure for the From SQL Server 2008 Database source. Now we have two sort controls receiving their inputs from two source controls with outputs sorted.
  2. Drag and drop a Merge Join Data Flow Transformation from the Toolbox on to the design surface.
  3. Click the Sort data flow transformation on the left (connected to From SQL Azure Database) and drag and drop its green dangling line on to the Merge Join data flow transformation.
The Input Output Selection window will be displayed as shown.



  1. Select the Merge Join Left Input and click OK.
  2. Repeat the same for the other Sort on the right (this time select Merge Join Right Output).
This Merge control now merges the output from the two sort controls and provides a merged output.
You still need to configure the Merge Join.
  1. Double click Merge Join to open the Merge Join Transformation editor page as shown.
Read the instructions on this window.



  1. Place check mark for EmployeeID in both the Sort lists shown in the top pane. The bottom pane gets populated with Input columns and Output aliases. Make sure the join type is Left outer join as in the above image (use drop-down handle if needed).
We can add for each flow path a Data Viewer so that we can monitor the flow of data at run time by momentarily stopping the flow downstream. We are skipping this diagnostic step.
Porting output data from Merge Join to an MS Access Database
We will be using the merged data from the two sources to fill up a table in an MS Access 2003 database. 
  1. In the MS Access database you created while setting up the Connection Managers create a table, Salary Report table with the design parameters shown in the next image.


  1. Drag and drop an OLE DB Destination component from the Toolbox on to the package designer pane just underneath the Merge Join component.
  2. Drag and drop the green dangling line from Merge Join to the OLE DB Destination component.
  3. Double click the OLE DB Destination component to open its editor and fill in the details as follows:
OLEDB connection manager:   AccessSQLAzure
Data access mode:                     Table or View
Name of the table or view:        Salary Report


  1. Click Mappings to verify all the columns are present.
  2. Build the project and execute the package.
The package elements turn yellow and later green indicating a successful run.
You can verify the table in the access database for the transferred values. This should have all the merged columns from the two databases. Note that in the image, columns have been rearranged to move the Salary column into view.


This is an excerpt of Chapter 6 from my book:
Book published by http://www.packtpub.com/






 [s1]Do you want to elaborate on this a bit and put it up as a tip for the readers?
 [s2]This sounds like an incomplete sentence. Please complete it
 [Maitreya3]'....in the following procedure:' or a similar term can be used. This statement sounds incomplete.
 [s4]This looks out of place. Do we need an explanation under this or do we have it as a part of the explanation above?
 [j5]Modified. Part of a number of steps, now bulleted.
 [s6]How about a numbered bullet list here?
 [j7]Modified
 [s8]Save what?
 [j9]Modified

Read the original blog entry...

More Stories By Jayaram Krishnaswamy

Jayaram Krishnaswamy is a technical writer, mostly writing articles that are related to the web and databases. He is the author of SQL Server Integration Services published by Packt Publishers in the UK. His book, 'Learn SQL Server Reporting Services 2008' was also published by Packt Publishers Inc, Birmingham. 3. "Microsoft SQL Azure Enterprise Application Development" (Dec 2010) was published by Packt Publishing Inc. 4. "Microsoft Visual Studio LightSwitch Business Application Development [Paperback] "(2011) was published by Packt Publishing Inc. 5. "Learning SQL Server Reporting Services 2012 [Paperback]" (June 2013) was Published by Packt Publishing Inc. Visit his blogs at: http://hodentek.blogspot.com http://hodentekHelp.blogspot.com http://hodnetekMSSS.blogspot.com http://hodnetekMobile.blogspot.com He writes articles on several topics to many sites.

@ThingsExpo Stories
Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...
"Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
"IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
"Cloud Academy is an enterprise training platform for the cloud, specifically public clouds. We offer guided learning experiences on AWS, Azure, Google Cloud and all the surrounding methodologies and technologies that you need to know and your teams need to know in order to leverage the full benefits of the cloud," explained Alex Brower, VP of Marketing at Cloud Academy, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clar...
It is of utmost importance for the future success of WebRTC to ensure that interoperability is operational between web browsers and any WebRTC-compliant client. To be guaranteed as operational and effective, interoperability must be tested extensively by establishing WebRTC data and media connections between different web browsers running on different devices and operating systems. In his session at WebRTC Summit at @ThingsExpo, Dr. Alex Gouaillard, CEO and Founder of CoSMo Software, presented ...
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, whic...
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
In his session at 21st Cloud Expo, Carl J. Levine, Senior Technical Evangelist for NS1, will objectively discuss how DNS is used to solve Digital Transformation challenges in large SaaS applications, CDNs, AdTech platforms, and other demanding use cases. Carl J. Levine is the Senior Technical Evangelist for NS1. A veteran of the Internet Infrastructure space, he has over a decade of experience with startups, networking protocols and Internet infrastructure, combined with the unique ability to it...
"MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...
"There's plenty of bandwidth out there but it's never in the right place. So what Cedexis does is uses data to work out the best pathways to get data from the origin to the person who wants to get it," explained Simon Jones, Evangelist and Head of Marketing at Cedexis, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, introduced two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a multip...
SYS-CON Events announced today that Telecom Reseller has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
SYS-CON Events announced today that Evatronix will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Evatronix SA offers comprehensive solutions in the design and implementation of electronic systems, in CAD / CAM deployment, and also is a designer and manufacturer of advanced 3D scanners for professional applications.
Leading companies, from the Global Fortune 500 to the smallest companies, are adopting hybrid cloud as the path to business advantage. Hybrid cloud depends on cloud services and on-premises infrastructure working in unison. Successful implementations require new levels of data mobility, enabled by an automated and seamless flow across on-premises and cloud resources. In his general session at 21st Cloud Expo, Greg Tevis, an IBM Storage Software Technical Strategist and Customer Solution Architec...
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. In his session at @BigDataExpo, Jack Norris, Senior Vice President, Data and Applications at MapR Technologies, reviewed best practices to ...
An increasing number of companies are creating products that combine data with analytical capabilities. Running interactive queries on Big Data requires complex architectures to store and query data effectively, typically involving data streams, an choosing efficient file format/database and multiple independent systems that are tied together through custom-engineered pipelines. In his session at @BigDataExpo at @ThingsExpo, Tomer Levi, a senior software engineer at Intel’s Advanced Analytics gr...
When talking IoT we often focus on the devices, the sensors, the hardware itself. The new smart appliances, the new smart or self-driving cars (which are amalgamations of many ‘things’). When we are looking at the world of IoT, we should take a step back, look at the big picture. What value are these devices providing? IoT is not about the devices, it’s about the data consumed and generated. The devices are tools, mechanisms, conduits. In his session at Internet of Things at Cloud Expo | DXWor...
Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution. In his session at @ThingsExpo, Akvelon expert and IoT industry leader Sergey Grebnov provided an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
SYS-CON Events announced today that Synametrics Technologies will exhibit at SYS-CON's 22nd International Cloud Expo®, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Synametrics Technologies is a privately held company based in Plainsboro, New Jersey that has been providing solutions for the developer community since 1997. Based on the success of its initial product offerings such as WinSQL, Xeams, SynaMan and Syncrify, Synametrics continues to create and hone inn...