General Notes on Security in Azure SQL Data Warehouse

, 2017-05-22 (first published: )

I’m still learning about Azure SQL Data Warehouse (ADW, cause I’m lazy). ADW is a deceptively deep topic. Initially you think that it’s just SQL Server, what’s the big deal. Then you start to understand the underlying architecture and things get complicated. Then you begin to understand the implications of the architecture and things get down right arcane. However, I’d like to talk about some relatively easy concepts around security in your Azure SQL Data Warehouse. For lots more detail, see the excellent documentation provided by Microsoft.

Firewall Security

The single most important aspect of security in and around Azure is the fact that for the public facing aspects (and the database stuff is public facing), there is a built-in firewall. This firewall is enabled by default and actually can’t be disabled, although you can choose to completely bypass it (why you would choose this is beyond me, but you can choose to eat lead paint too, so….).

Let’s say that someone knew that you had an Azure SQL Server (the container for both Azure SQL Database (ADB, lazy again) and ADW, not to be confused with an instance of the SQL Server boxed product) out there named “securitytestdw”. If they knew anything about Azure, they’d know that the string to connect to this is “securitytestdw.database.windows.net” which gives them an attack vector of sorts. However, with a hole in the firewall, the best they get is an error stating “Your client IP address does not have access to the server.” In short, they’re not getting in if you haven’t allowed for their IP address.

Now, can you shoot yourself in the foot and expose your server to all IP addresses? Yes.

an example of bad firewall security

However, you’d have to intentionally do this. Don’t. Then you avoid the problem.

Login Security

You have two core choices on logins. First, you have to create a SQL login at the server level for both Azure SQL Database and Azure SQL Data Warehouse. You can’t remove this or disable it (to my knowledge, and I’ve tried), so make the password a good one (and don’t lose it). You can then create other SQL logins, but this is not a recommended best practice. In fact, I wouldn’t do it at all unless I was forced because of some third party product (few of which currently support Azure anyway).

The next choice, the preferred choice, is to set up Azure Active Directory. With Azure AD you get all the functionality you’re used to with your local AD. Further, you can federate Azure AD with your local AD to control and manage the logins from within your network. You also get multi-factor authentication with Azure AD. We are talking real security here. Read through the documentation on setting up authentication to get it right. You can do the whole thing using Powershell commands, so there’s no excuse on automating it.

Auditing

Auditing and Threat Detection are services that you pay for. They’re off by default. You can set them for the server or for individual databases. You’ll have to add storage for the audit logs. The threat detection looks for sql injection and sql injection vectors an anomalous client logins. Remember, if you set this for a server, it includes all the databases under by default. You can change the databases individually through Powershell, or here in the GUI:

Conclusion

The basics of security within Azure SQL Data Warehouse are pretty clear and easy to understand. Leave that default firewall in place and only poke the holes through it that you actually need to, and you’ll be fine. Get Azure Active Directory set up so that you can use that (along with multi-factor authentication) to completely lock things down. Finally, you can pay for the audit and threat detection. With all these options, you can secure your Azure SQL Data Warehouse properly.

The post General Notes on Security in Azure SQL Data Warehouse appeared first on Grant Fritchey.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads