Azure has just introduced another tool to help in the fight against SQL injection known as SQL Database Threat Detection. You can go and read all the Microsofty bits there or watch it work in a real live app here.
Firstly, this is threat detection, not prevention. In a nutshell, this feature will tell you when an attack is mounted against your database and in order to do that, the upstream app has to have a vulnerability in it that’s allowing the attack to get that far. Now before you give it a bit of “well that’s pretty useless then”, the main reason this makes sense is that you can go and enable it with a single checkbox tick and it won’t break your things. Plus, even if the code is solid and you have a device or a service like a WAF, this is just one more layer that’s good to have in place. Let’s just jump into it.
This is a useful tool. If you’re using Azure SQL Databases, go forth and activate this.
At the bottom of the portal, there is a New link and a Delete link. These are for creating and deleting databases.
After clicking the New link, I went through a series of screens to create my database.
The first screen asked me for the name of my database and what size database I wanted to create. This is an important step, since it will affect my monthly charges. Remember, I only have $150 in free credits each month. You can go here to see the pricing for the various service tiers and the performance levels. I chose to create the smallest database I could (2 GB, and 5 DTUs). I also created this database on a new SQL Database Server (I kind of have to, since it is the first database).
Both of the products, the On Premises versions and the Azure SQL Database versions are part of the Relational Database family of products. They share a common base, and a common purpose: to work with relational data. They look basically the same, and operate mostly the same, and serve (at their core) very same purposes.
As such I will make sure that all of the scripts that end up in the final book have been validated on the different editions of SQL Server (as I have always done), and have been executed at least once on Azure SQL Database as well. What I won’t do is go into many details of how to connect to an Azure SQL Database, mostly because it takes quite a few pages to do so (I just tech edited a book that covers such details, and I will direct readers to that book for details on how to connect… Peter Carter “Pro SQL Server Admin” http://www.springer.com/gp/book/9781484207116).
We’re already seeing Microsoft move to a cloud-first philosophy, so get in on Azure if you’ve avoided it thus far.
Microsoft Azure is a cloud computing platform and infrastructure, created by Microsoft, for building, deploying and managing applications and services through a global network of Microsoft-managed and Microsoft partner-hosted datacenters. Included in this platform are multiple ways of storing data. Below I will give a brief overview of each so you can get a feel for the best use case for each, with links provided that go into more detail:
There are several options available, running the gamut from unstructured data (blob storage, file & disk storage), semi-structured data (data lake store), to structured data (Azure SQL Database) and a few points in between.
If you have an Azure account (possibly through your MSDN subscription) here is the easiest way to get up and running with SQL Server 2016.
First go to the Azure Portal – http://portal.azure.com
Search and find the SQL Server 2016 CTP3 in the Data and Analytics Marketplace in Azure.
My preference is to grab the ISO and build a local VM, or install it on a server in my environment. But if your server infrastructure lives on Azure or you’ve got those MSDN credits to burn, this is a good alternative.
After you provision a Microsoft Azure VM with SQL Server there are a few more steps that you need to take to make remote connections. The procedure below starts with a fresh Azure VM provisioned and walks through the process of establishing a connection via SQL Server Management Studio, installed on an on-premises work station.
Note that this is Azure IaaS, not Azure SQL Database.
Our basic architecture was:
- Multiple VLANs containing SQL Servers to be monitored
- VLAN containing the monitoring server
Probably not the best for what we were wanting to do, but you work with what you’re given. I installed SQL Monitor, fired it up, and nothing worked.
After much trial and error, and a lot of network monitoring by a very enthusiastic young infrastructure guy, here are the inbound rules that we needed to put in place on each SQL Server VLAN to get this working
Note that this is Azure IaaS, not Azure SQL Database.
Microsoft is pushing U-SQL pretty hard. Here’s a tutorial by Jonathan Gao to whet your appetite:
U-SQL is a language that unifies the benefits of SQL with the expressive power of your own code to process all data at any scale. U-SQL’s scalable distributed query capability enables you to efficiently analyze data in the store and across relational stores such as Azure SQL Database. It enables you to process unstructured data by applying schema on read, insert custom logic and UDF’s, and includes extensibility to enable fine grained control over how to execute at scale. To learn more about the design philosophy behind U-SQL, please refer to this Visual Studio blog post.
You do need Data Lake Tools for Visual Studio, but it looks like you can run it locally.
The VS blog had something a month ago on the topic. I’m not saying get on it…yet…
Customers can already run Linux on Azure, but the new partnership will expand support for running so-called “hybrid clouds,” in which applications may exist in both private data centers and on public cloud services. More significantly, Microsoft and Red Hat support teams will work together from the same facilities to support Red Hat customers using Azure. Microsoft vice president of cloud and enterprise Scott Guthrie said during a webcast today that this is the first time that he knows of that Microsoft has “co-located” support teams with another company.
The deal is the latest example of Microsoft playing nice with a former rival. “When we started [Red Hat Enterprise Linux] I never would have thought we’d do this,” Red Hat president of product and technology Paul Cormier said during the webcast.
Free speculation with no evidence: at some point, Microsoft will offer SQL Server on Linux. My guess is 3-5 years from now, but other co-speculators have suggested maybe even as soon as 18 months. Whatever the case, I’ll be a happy man when I can run SSMS in Linux.
Bad news. The error message is the same.
Working within Azure SQL Database, trace flags are not a part of your tool set.
Everything with Azure needs a timestamp. Come back in a year and this may be different.