Welcome!

Silverlight Authors: Steven Mandel, Gerardo A Dada, Srinivasan Sundara Rajan, Yeshim Deniz, Greg O'Connor

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
We’ve worked with dozens of early adopters across numerous industries and will debunk common misperceptions, which starts with understanding that many of the connected products we’ll use over the next 5 years are already products, they’re just not yet connected. With an IoT product, time-in-market provides much more essential feedback than ever before. Innovation comes from what you do with the data that the connected product provides in order to enhance the customer experience and optimize busi...
In his session at @ThingsExpo, Chris Klein, CEO and Co-founder of Rachio, will discuss next generation communities that are using IoT to create more sustainable, intelligent communities. One example is Sterling Ranch, a 10,000 home development that – with the help of Siemens – will integrate IoT technology into the community to provide residents with energy and water savings as well as intelligent security. Everything from stop lights to sprinkler systems to building infrastructures will run ef...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
SYS-CON Events announced today that Stratoscale, the software company developing the next generation data center operating system, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Stratoscale is revolutionizing the data center with a zero-to-cloud-in-minutes solution. With Stratoscale’s hardware-agnostic, Software Defined Data Center (SDDC) solution to store everything, run anything and scale everywhere...
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...
Angular 2 is a complete re-write of the popular framework AngularJS. Programming in Angular 2 is greatly simplified – now it's a component-based well-performing framework. This immersive one-day workshop at 18th Cloud Expo, led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and the product company SuranceBay, will provide you with everything you wanted to know about Angular 2.
You think you know what’s in your data. But do you? Most organizations are now aware of the business intelligence represented by their data. Data science stands to take this to a level you never thought of – literally. The techniques of data science, when used with the capabilities of Big Data technologies, can make connections you had not yet imagined, helping you discover new insights and ask new questions of your data. In his session at @ThingsExpo, Sarbjit Sarkaria, data science team lead ...
SYS-CON Events announced today that Men & Mice, the leading global provider of DNS, DHCP and IP address management overlay solutions, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. The Men & Mice Suite overlay solution is already known for its powerful application in heterogeneous operating environments, enabling enterprises to scale without fuss. Building on a solid range of diverse platform support,...
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus inter...
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
Increasing IoT connectivity is forcing enterprises to find elegant solutions to organize and visualize all incoming data from these connected devices with re-configurable dashboard widgets to effectively allow rapid decision-making for everything from immediate actions in tactical situations to strategic analysis and reporting. In his session at 18th Cloud Expo, Shikhir Singh, Senior Developer Relations Manager at Sencha, will discuss how to create HTML5 dashboards that interact with IoT devic...
Artificial Intelligence has the potential to massively disrupt IoT. In his session at 18th Cloud Expo, AJ Abdallat, CEO of Beyond AI, will discuss what the five main drivers are in Artificial Intelligence that could shape the future of the Internet of Things. AJ Abdallat is CEO of Beyond AI. He has over 20 years of management experience in the fields of artificial intelligence, sensors, instruments, devices and software for telecommunications, life sciences, environmental monitoring, process...
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
There is an ever-growing explosion of new devices that are connected to the Internet using “cloud” solutions. This rapid growth is creating a massive new demand for efficient access to data. And it’s not just about connecting to that data anymore. This new demand is bringing new issues and challenges and it is important for companies to scale for the coming growth. And with that scaling comes the need for greater security, gathering and data analysis, storage, connectivity and, of course, the...
SYS-CON Events announced today that DatacenterDynamics has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY. DatacenterDynamics is a brand of DCD Group, a global B2B media and publishing company that develops products to help senior professionals in the world's most ICT dependent organizations make risk-based infrastructure and capacity decisions.
Machine Learning helps make complex systems more efficient. By applying advanced Machine Learning techniques such as Cognitive Fingerprinting, wind project operators can utilize these tools to learn from collected data, detect regular patterns, and optimize their own operations. In his session at 18th Cloud Expo, Stuart Gillen, Director of Business Development at SparkCognition, will discuss how research has demonstrated the value of Machine Learning in delivering next generation analytics to im...
This is not a small hotel event. It is also not a big vendor party where politicians and entertainers are more important than real content. This is Cloud Expo, the world's longest-running conference and exhibition focused on Cloud Computing and all that it entails. If you want serious presentations and valuable insight about Cloud Computing for three straight days, then register now for Cloud Expo.
IoT device adoption is growing at staggering rates, and with it comes opportunity for developers to meet consumer demand for an ever more connected world. Wireless communication is the key part of the encompassing components of any IoT device. Wireless connectivity enhances the device utility at the expense of ease of use and deployment challenges. Since connectivity is fundamental for IoT device development, engineers must understand how to overcome the hurdles inherent in incorporating multipl...
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...