Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Protecting Your Data in Windows Azure SQL Database

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
Okay—hey, everyone awake? (audience laughs) Sorry about that, guys. I think the people back in Redmond heard me. So of the people who aren't using it for production, what are the main reasons that people have been waiting to put their data up in the cloud? Yes? Security. Anyone else have another reason? Management. Okay, anything else? Okay. It turns out that security habitually comes out as the top or the number 2 reason why customers don't put their data up in the cloud, okay? And the reason is this, right? If you look at this picture right now, what you're used to is the picture on the left, which is you start on your machine. It's on your desktop, inside your corpnet, behind some application servers. It's all good, right? You've got full control over it. And then what Azure SQL DB is is that it's the thing on the right, okay? It's basically—a really easy way to explain this is that SQL server is probably something you can install and run on your laptop, and Azure SQL DB is probably something that you probably don't want to install on your laptop or try to run. And try to—just to try to give you an idea of the scale difference between the 2. Just from a security perspective, Azure SQL DB handles roughly about a million, a million and a half authentications per day, per datacenter, okay? So if you think about just in the US alone where we have—I think it's getting close to 9 to 12 datacenters— I don't have exact details, we're talking an awful lot of authentications, right? This is massively— it's basically all the traffic from all the customers who come through the U.S. and to kind of state this kind of succinctly, Azure SQL DB is internet facing, multi-tenanted, massively distributed public service. Now what part of that statement actually kind of jumps out at you and makes you concerned? Public, multi-tenanted, and internet facing is one of the big things that actually concern us, because most people don't put SQL server on the web directly, right? If you do—you can but most people don't, because you're hiding behind some kind of web server, application server, but SQL DB, by itself, is a web service, right, which is why it's put directly on the internet, which means that it's opened up to everyone, okay? Everyone sees IP addresses, everyone can actually go and try to connect to it, everyone can see the ports, everything else, okay? And so that's typically a major concern, and as I go through this talk, you'll see kind of the things that we did to step up the security in SQL Azure— kind of step forward in terms of trying to protect against that network threat. So the first thing I want to talk about is protection against network threats. By the way, please feel free to jump and ask me questions if you want. This talk is actually not going to be that long. It's fairly short. I'm counting on having some questions, and if we have time at the end, I'll just go through a really, really simple demo of just going through and creating a new database, and you'll see the firewall and everything else take place, so feel free to jump in. When you think about network protection, what's the first thing people normally think about? And it's usually encryption, right? They usually think about channel encryption, and so this is no real big surprise. SQL server has supported SSL encryption since the 2000 days, right? For a web service, this is almost a necessity. If you don't have encryption, then you don't do business on the web. And so the TLS encryption is always enabled, okay? This is a big difference between what SQL server does and what we do on the cloud, and SQL server— you have the option of turning on or turning off. And actually even if you turn off encryption in SQL server, we do SSL negotiation, because we always encrypt the log-in packet at the very least so there's 1 packet that potentially contains your credentials, and we make sure that's encrypted, but afterwards it's your choice, and a lot of customers— a lot of times they do not actually put encryption on, and that's because it actually can degrade performance and they feel protected within their own intranet, and so they don't bother turning on the encryption. Their choice, but that's not a luxury that we have with SQL Azure, okay? So the server will always enforce encryption to be enabled, and what this means is that even if a client doesn't say, "I want encryption," the server's going to force it to use encryption, okay? All the clients we support connecting to Azure SQL DB support encryption at this point. The only ones that didn't were the really old ones that aren't supported with Azure SQL DB, so at this point right now, there should be no clients that connect to Azure SQL DB that can't participate in this TLS handshake and this TLS channel encryption. Another big distinction is the fact that we don't use self-signed certs. Does anyone know why a self-signed cert is potentially harmful? I'm sorry. (off-mic commentary) Gentleman upfront says it's worthless. It's not quite—well, it depends, I guess, but it does have some concerns around self-signed certs. Self-signed cert is basically a cert that doesn't have a route authority. You yourself are the route authority. I generate the cert, and I vouch for it, so that makes it very hard for another party to say, "Hey, you're right. You vouch for your own certs; therefore, I trust it." It kind of makes sense. Well, what SQL server used to do is that if a lot of people didn't install a proper cert— they didn't install it on the machine store, and when SQL server started up and tried to actually go and find the cert and couldn't find one— it's like—oh, if I don't have a cert, I can't do SSL, so why don't I just generate one? It generates its own cert and used it to actually do the SSL handshake, which is better than nothing, but it's definitely not as good as having a real cert that you bought that has a proper signing authority. And one of the consequences of using a self-signed cert was that you can't actually validate it, right? Like I said before, the client has no way of actually saying that— wow, sounds like a train going through! There's no way for the client to actually go and validate the fact that this cert is actually legitimate. It can come from anyone, because if I'm bad guy, and I give you my own cert, of course I'm going to say, "Yes, this is legitimate. Please trust me." So that makes it very, very difficult to do, but the problem is that because— like I said, a lot of the customers didn't have a cert and they actually relied upon a self-signed cert in the SQL server days that if they tried to request encryption on the client side, because what that does is that tells a client, "Hey, I want encryption. I'm explicitly saying I want encryption; therefore you should validate to make sure that cert is legitimate." If I don't ask for encryption, and I do encryption, then I don't really care, obviously, that the cert comes from where, because I didn't ask for it, but if I ask for it, then I want to know that this cert is legitimate, and the problem is that with the self-signed cert, I can't tell if it's legitimate, and so we added this other keyword called trust server certificate, and what that meant was that even if I force encryption— if I get a certificate that I can't validate, then let it go and discontinue. So what does this do? Well, it opens myself up to what's called a man in the middle attack, and I'm sure you guys hear— all probably know what a man in the middle is, but just for the odd chance you don't, it basically is— it's a hacker who inserts himself between you and your server and pretends to be the server, so typically what they'll do—in a bad case, they'll eavesdrop. They'll do the communication with you, then they'll take your communication and relay it back to the server. In between, it's just listening to everything that happens—they'e eavesdropping. In the worst case, they're actually modifying data, so they're actually making changes that you don't know about, and this is one of the reasons that SSL is very helpful, because if you can actually use SSL to encrypt the channel so people can listen, and furthermore, you know that the encryption is set up with a party that you know is actually valid, then you have a fairly high confidence that you're not being attacked by a man in the middle, and just a little bit of animation here—it's not really fancy, but when you first make a connection to SQL server or SQL Azure or if there's a man in the middle pretending to be one of those, then one of the first things we do is we do the SSL handshake, and as part of the SSL handshake, the server is going to return you as the public part of a certificate. So it sends the certificate back, and then what happens is that if we are able to actually go and validate that the certificate is not where it should be— so here in Alice's case, she says "The certificate doesn't look right. I'm checking it. It doesn't look like it's coming from the person I expected it to come from." And so the client will disconnect—very, very useful thing, and so you should always tried to turn on client-signed encryption, even if the server is saying that you must encrypt, there is value in making sure that the client turns it on itself, as well. Don't set the trust service certificate equals true. By default, it's false, so if you don't put anything in there, then by default, the client will actually not trust the server certificate if you force encryption, but you can actually explicitly just say trust server certificate was false if you want to be explicit. Here are some connection strings examples. There's lots of examples. I just discovered this morning in the Azure Management Portal that they actually give you this list, so they made it that simple for customers, but as you can see for ADO.Net, ODBC, PHP, that the keywords are all the same. You can put encrypt equals true, trust server certificate equals false. I believe true and yes are interchangeable in most cases, and false and no are interchangeable in most cases, as well, so you can use either or. But the kind thing here is that the client should actually put encrypt=true. The trustservercertificate=false is optional, but if you want to be extremely sure about this, then I would put it in there, anyway, okay? It does no harm, and it also catches the fact if someone does change this, then you'll have 2 keywords and it will throw an error. That's about the SSL encryption. Any questions on that? All right. The next thing I want to talk about is database firewall. This is something that's specific to Azure SQL DB; SQL server doesn't have this. One of the things about Azure SQL Database is that if you're used to SQL server, you know you have the choice of many different protocols. You can do TCP, main pipe, shared memory, via— you can do all sorts of things, right, but Azure SQL DB only accepts TCP/IP and only in 1433. Now that's significant, and the reason that's significant is that in a SQL server case, because you have named instances, and because even though the default instance, you can make it listen to a different port— well what happened is that the client didn't know what that port was. It would have to go and contact the SQL browser service. You guys are all familiar with the SQL browser service? Some of you. Do you guys know why it's not the best thing in the world? Yeah? Yeah? Maybe? Well—sorry? (off-mic question) Yes, people can see it from everywhere. SQL browser is an unauthenticated UDP service, so it means that basically— it will take a request from anyone and respond to anybody that wants it, and if you guys have been around for awhile, you guys know that probably about a decade or so ago, there was the whole thing with SQL slammer. Well that was directly tied to the SQL browser process, so since then, we've made fixes to the browser, but at the same time, it's a much more secure scenario to actually just shut off the browser service so that people can't actually reach that endpoint. So we only have 1433 in Azure SQL DB. No browser necessary, you know exactly what port you're going to go to, you can—we only need to open that 1 port for connections inbound. The database firewall is basically an IP filter based on the client address, so it means that I can basically limit a particular IP address from which a client's allowed to connect, or I can give it a range, so it's up to you. Obviously the more narrow you have it, the better. And another thing you have to keep in mind is that when you actually connect to SQL Azure, especially if you're coming from a corpnet, you're probably behind a net. You've got network address translation, so the address you see internally is not going to be the same address that comes out externally, so you have to find out what your external address is and then add that to the firewall rules, and if I have time I'll show you a bit later on. One other thing is that I'm sure everyone here—if you play with SQL Azure have hit this before, you create your server, you think, "Hey, this is great. Let me connect, open up SSMS. Let me connect to it," and you get a protocol timeout error. You get that TCP error code 0, and the reason is that by default, the database firewall blocks all IP addresses coming inbound. So the very first thing you have to do after you create your service is to go and add a rule to your firewall to say let me connect in, or else it's not going to accept any TDS connections. This is a service security feature. I know it got me—how many other people got that problem, as well? I don't if any class has ever hit this before. The diagram next to you here, which is a diagram I took from the online documentation basically has really quickly described how the firewall works. So we have rules at the database level and at the server level, so if I set a rule at the server level, it means that anything that fits within these IP addresses I have to specify can come in and connect any database in my server, whereas I can also do it more granularly at the particular database. For example, I may have 1 database that's only used by a particular set of applications, and so I can set the firewall rule for that 1 application so that those clients can connect to that database but they can't access the other databases, as well. It's a really nice feature for partitioning out your surface area and security exposure. When traffic comes in through the Internet or from within another Windows Azure service, one of the first things we do is validate the firewall rules. So we'll check to see whether— does this IP address allowed by the server firewall rules? If the answer is yet, then we let you go through. Of course you do the authentication, authorization, and all that stuff. But if the server firewall blocks your connection, then we go in and say that— okay, for the database they tried to connect to, did they have a rule there that let this guy through? If the answer is yes, then everything continues as normal. If no, we drop the connection. It's very, very straightforward. The other things is that— obviously this is a static IP list, and so if you're using dynamic IP, then you have to account for the fact that your IP may change once in awhile when your leash runs out. Yes, please. (off-mic question) Yeah, so the question is whether— for Azure services, there's— right now there's a rule that allows all Azure services to connect—or none. If there's more granularity in that in terms of allowing certain services to connect or not— is that basically what you're trying to ask? Yeah—at this point, the answer is no. So it's the all-or-nothing approach. I know that there are some teams looking at ways that we can actually correct this, but for now— if you add their address, 0000, to your IP firewall rule, then it basically tells the database firewall that anything that comes from within Azure is allowed. Not ideal, but it gets around the fact that the IP addresses, like you said, can change very wildly. The other thing you could do also is if you have also— especially if you're coming from external—from the Internet and you have dynamic IP, you can try to find out what the range of dynamic IPs you have, and you can set that as an IP filter rule. Obviously not as good as having one particular IP. Yes? (off-mic question) That's a very good question. So the question is whether you add rules dynamically or if you have to restart the service. The answer is— is that it's dynamic, okay? There's no way for you to actually go start and stop your service. The database is upwards, and when you actually go to the management portal and try to add a new rule, it does offer documentation. They do give you a warning that says, "May take up to 5 minutes for the rule to go into effect," but my experience so far is that it has been almost instantaneous. It's dynamic, for sure. Is there another question? Okay, maybe someone's scratching. The next thing I want to talk about is DOSGuard. This is basically a service that's inherent to Azure SQL DB, and again, this is something we don't have with SQL server, and it's directly attributed to the fact we're a big service on the Internet that's getting pinged all the time and attacked all the time, but it provides some protection against denial-of-service attacks, and denial-of-service attacks is actually something that is very, very prevalent these days and especially when you hear the news around foreign governments trying to launch service attacks, everything else. Azure is a big target, and what this does is it provides some mitigation against some sort of attacks, and what happens is that the service actually detects when there's a repeated number of bad connection attempts coming in, so it detects bad requests, such as someone repeatedly tries to connect with an invalid user or password, and once it detects and sees that— "Hey, this is abnormal. It's a little bit too much," then it blocks that IP address at multiple levels. It can be at the database, the server, the service level for up to 5 minutes before it resets, so this is actually a way for it to actually prevent— at least kind of mitigate some of the denial-of-service attacks. There's not a whole lot else I can say about this right now. It's something that we don't divulge publically in terms of everything that—around how this works for security reasons, but I will mention that this is not the only form of DOS prevention that we've implemented. There's also other internal efforts to try to prevent things like DDOS and everything else, so we're continuously trying to make sure that the service is up and stable and secure. Obviously for a service that's up on the Internet, this is one of the big things you do have to be concerned about, and there's nothing for you to be able to configure or for you to be able to control, but I just want to let you know that we do have something in place to help prevent some of these outages and attacks from a DOS perspective. Question? (off-mic question) Good questions—the first question is whether this applies also to Windows Azure IP's, and it applies to all IP's, so if we detect that there's a particular IP address that's causing a lot of traffic— that's bad traffic, bad connections, then this will kick in, then the second question you had was around throttling, and this is not related to throttling at all. Throttling is more of a load-balancer type of behavior where they're trying to load balance and mitigate the risk of the machine getting really, really slow. (off-mic commentary) No, that's—throttling is a separate thing. Yes? (off-mic question) Well, I think there's a tradeoff here between blocking for too long or blocking at all, and the 5 minutes was something that a group of people did a study and found that when someone's doing a DOS attack—typically what they do is that they're flooding the service with sometimes hundreds or thousands of connection attempts over a very, very short period of time, so having them actually go and have to wait 5 minutes, and when they do it again, they get blocked for another 5 minutes, so it's not that it's 5 minutes and then they're free. It's 5 minutes and then they may potentially get blocked again, and so this actually does greatly reduce a number of attempts that actually go through the service. All right. The next topic that I want to discuss is actually protection within the service. So now that we've actually gone through all the network safeguards, we get into the service, what happens or what do we do to actually keep things safe? So this diagram is a very, very basic block diagram that shows that first of all, we come in through the Internet, and assuming that we don't get blocked by DOSGuard and we don't get blocked by the server firewall, then the frontend node, which is also the TDS gateway— Azure SQL DB being a massively distributed system has several types of different types of machines that serve different purposes, and when you actually go and connect in, it actually goes in and has 1 frontend node or a bank of frontend nodes—I just represent it as 1 machine— that actually service your connection request, and in this frontend node, they do authentication, so you come in. It'll go and take your credentials, and then use a virtual master to actually go and validate that first of all, the password that you send me matches the password has that's in the master database. So authentication occurs right then. And then once I validate that, "Hey, this person is authenticated, and he is meant to be connecting to our service," then we'll do a lookup and find out— well, he once connected to database X or database 1, where is database 1, and let's route him. Then the frontend node serves a purpose of routing the connection to the proper backend. Now the one thing is that once you actually go and connect to the backend— for example, suppose this connection went to DB1, then once you get to DB1, what happens is that we actually go and create a security context. In our terms, we create a user token, and that user token is crafted in such a way that's only valid in that database. It's not valid anywhere else, so if you were just—to be able to steal that token, which you can't, but if you did and move it to a different database, it would be completely invalid. It's only valid in that 1 place. So this is quite different than SQL server, and SQL server—you have the case where I had a user token, but I also had a login token, and that login token represented the proof that I've authenticated myself to the SQL server service, and I have access to all the databases where I have users to find. That's not the case here. You don't really have a login token; you only have a user token, and that user token is sandboxed to each database, which is why it's a security boundary. Now if you use SQL Azure, then you know that we already have the— it's a single database type of system. You can't go and use 4-part names and connect to the other databases or use USE. If you try to move to another database through a USE team, for example, then that's not going to work—that's to valid. So you really are locked to that database. What I'm trying to say here is that we have security boundaries built into the system around each of the databases— between servers and between databases, and so it really does provide database isolation— more than it ever did in SQL server, and SQL server—there's lots of ways you can move the context from one database to another. There's cross database chaining. There is also—there's a lot of things like that, but there's no such thing in SQL Azure. So just to kind of rehash— the security context is isolated to the database. The generated context is invalid outside of authenticating database. If you want to move to a different database— here comes the train again. If you want to move to a different database, then it does require you to actually go and re-authenticate. You have to go out and make a new connection to that second database if you want to go there. You can do it from within the same session you've already established, and you can use a USE command to get to another database, unless you're using federations, which is sort of a slightly different thing. It's a special case, because the case with federations— each of those databases are shards, and they're supposed to be a part of 1 larger, logical database, right? So when you use between shards and a federation, you're basically using between different parts of the same database, which is why it's allowed. Any questions? So next I want to talk a little bit about server principals. There's a big difference between server at the Azure SQL DB and server at SQL server. Azure SQL DB— a server is nothing more than a logical and administrative container for databases. In the SQL server world, the server actually meant that you owned everything under there. It was not just a concept, but it was actually the object that encapsulated all the databases underneath it. Azure SQL DB is a massively parallel system that's distributed where the databases can be all over the place. They're not on a single machine. They're definitely not in a single process. So the server is just a concept that groups together databases that are owned by the same server admin, so that's one thing to actually keep in mind. The second thing is that the server admin— which in the books online, unfortunately, they call it server-level principal— which is a pretty dumb thing, I think, but it's basically equivalent to your 'sa' that you have in SQL server. So this is a person that actually has full access to all of the databases. They own everything, and just like 'sa,' they can connect to any database, even without having a user defined in there, and that's because, just like 'sa,' when you connect in through there, they actually go and map you to a DBO. So a highly privileged user. The other thing in SQL DB is that unlike in SQL server, where you have a lot of different server roles— and in fact, you actually even have flexible server roles. You can create your own roles at the server level, if you want. There's are only 2 fixed administrator roles, and those are the only things that you can use. In fact, they're not even server roles; they're database roles—manager. One's called the dbmanager, and one's called the loginmanager, and the dbmanager is obviously for managing databases, so you can create, alter, drop databases. The loginmanager—you guessed it. It's for create, alter, drop logins. The server admin implicitly has these 2 rights already, so they don't need to be added explicitly, but since these things are not actually real server roles, because like I said before, there's no true server. If you want to add someone to these roles, you have to go to master, create a user for that login, then add those users to the database. It's a little bit different of a concept than it is in the boss world. Like I said before, there's no server permissions or user-defined server roles. Those 2 roles are it, okay? In my opinion, it's not the most ideal situation, but it is something we have to address coming up. For now, you've kind of restricted to— if you're not the server admin, you can either have someone manage your database, manage your logins, but there's not much more granularity beyond that. On the database scope— and this isn't really a server thing, but I just wanted to call out that at the server level, I just described that we have a big difference in our permission model in terms of capabilities, what we offer, but at the database level— because the database is essentially the same as you're used to on SQL server, you have all the same permissions— all 200-plus permissions and roles—and you can create your own roles, and you can do all sorts of stuff and deal with their very full-featured, which means complex permission model, so you get to do all that kind of stuff. That's all still there. It's at the server level, but it's different. One thing I want to call out is that normal logins can't change your own password by default. So you're probably used to the fact—I have a login. I want to change my password. I can just do an alter login, give my name, password equals blah, old password equals blah, and then I change it, right? Well that doesn't work, and the reason is that in order to— I'll talk about it next. The reason is that in order to change the password of a login, you'd have to be a login manager, or you have to have a user created in MasterDB. Again, this is all going to the fact that there's no true concept of— it's not really the same server that you're used to in SQL server. It's more of objects and principals within MasterDB itself. You can do one of these 2 things, but I don't recommend you do the second thing, and the reason is that we have a problem— that any user in MasterDB will be able to see most of the database and catalog views, and it's because when they originally created the virtual master and they set the permissions and everything, I believe that the design assumption was there wouldn't be very many users in Master, so they made most of the views open to public. If you had access to Master, you had access to these views, and they made the assumption that you would be a privileged user at that point, but that's no longer the case, because obviously being able to change your own password, I believe, is something that people should be able to do. These are the assorted views. It's a big list. You just have to look on the books online to see which ones they are, but there are some here that you probably don't want everybody to see, and things like—let's see, there's event logs, there's sys.databases, sys.database permissions, database principals, tables, firewall rules, database firewall rules. You get the point, right? Some of them are kind of locked down; for example, I tried to do a query from sys.SQLlogins, and that does get blocked—that does really do a check to make that you are a login manager, but a lot of these other ones are open to public, so you have security concern. If you start creating users to master, okay? This is something we're worried about, and I'm trying to get it changed. Any questions about that? No? Okay. I want to kind of finally talk about some of the other security differences. I mentioned some of the main differences between SQL server and SQL Azure and some of the key things that SQL Azure does, but there are actually a bunch more, and I'm going to go through all the ones I can think of. Some of them I've already mentioned, but a lot of them I haven't. The first one is around authentication. Everyone has probably noticed the fact that you can only do SQL authentication user ID and password based, right? There's no Windows authentication in Azure SQL DB. That's the case right now. Doing Windows authentication through the cloud is surprisingly harder than you would think it would be, but all I'll say right now is that that's the way it is at this point, and it is something that we're looking at. Fixed password policy. When you try to create a password for a login in Azure SQL DB— if you try to do something like abc123, it's going to say, "This isn't complex enough." It's because there is a fixed password policy. I think it's 8 characters and probably you need a mix of lowercase and uppercase and numeric or something like that, but people who are coming from the SQL server world have it a little bit better, because I can define the password policy and my domain, and I can get that pushed down into SQL server, as well. Even when I created logins in SQL server, it will be the password policy set forth by the Windows group policy, which is great, because now I can unify all the password policies, but that's not the case for Azure SQL DB. Idle connection timeout. This is actually a very, very good feature that we don't have in SQL server. Basically if you have a connection that's opened, and you leave it idle for 30 minutes, then the service will actually terminate that connection automatically. Yes? (off-mic question) So the question is can you configure that lower? The answer is no. That's fixed at this point. We could talk afterwards. Right now the value is 30 minutes, and after 30 minutes, it will disconnect. So this prevents the situation where you have someone who just leaves their window open and basically leaves the service exposed. It's all about leaving the service exposed, so we try to mitigate or reduce the impact of that. No native data encryption at rest. There's no DB or cell-level encryption at this point. Again something that we're looking at, but at this point right now, if you are going to use encryption with the cloud service, you need to do the encryption at that application level or ahead of time before you send it out there. In any case, some people would do that regardless of what we provide, because they want to make sure they achieve maximum separation between the cloud operator and their data, so as long as the cloud operator has keys, they can never see your data, even if the data's stolen, so that's kind of thinking. Auditing. In Azure SQL DB, the auditing has to be performed by the application, or you can set up a series of triggers to trigger some of the DML/DDL. Unlike SQL server, which has its own auditing feature that allows you to audit all actions at both the server and the database level. Now there's a few more differences I want to go through. So we talked about the database firewall. Great feature—SQL Azure only. SQL server—you could do something similar using login triggers. It's obviously more work. It's not as easy. It catches the requests at different levels in the stack than it does in the firewall. The firewall actually catches it very early. You could crash something similar on SQL Server if you wanted to. DOSGuard. Azure SQL only. No native DOSGuard in SQL Server. You could potentially use something with login triggers, again, but it would be quite a bit of work, because you would have to try to keep track of all IP addresses and activity and stuff, so it's definitely not trivial. The virtual MasterDB with virtual logins. Then you have MasterDB with login principals. We discussed this already. This fixed roles in MasterDB as opposed to user-defined server roles and server-level permissions. We've discussed. Then the automatic patching with no downtime. This is a big thing, right? If you've run your own SQL server instance before, you know that every time there's a patch, you have to schedule some downtime, unless you're using some of the hadron features—always-on features, excuse me, and you have to do the patching yourself and everything. In the Azure SQL DB service— and this is one of the big values adds— not only do we actually maintain several copies and give you three nines of HA—of high availability guarantees, but we'll make sure that the code is always patched with anything urgent right away or as soon as possible with no downtime to your application, okay? So this is a very big thing. It means that you can not have to worry about always having the latest security patches in place with your code—with our service. Don't dismiss that as something that's really, really useful. Question? (off-mic question) The question is if there's any kind of notification when the patch is being applied. I'm not 100% sure about that. I think the answer is no, but if you drop by afterwards, I'll get confirmation on that, but I believe the answer is no— that the system takes care of it automatically—under the covers. Any other questions? All right. So I'm going to try something here where I'm just going to play around with the computer and hopefully things don't break. I was having problems with the Internet this morning, so hopefully things are back on track, and this will be more useful for people who haven't used the service yet, because you get to see how things are. If you've used the service before, then this may not be as useful, but we'll give it a try. So the first thing I want to do is I want to show you what the portal looks like. So can everyone see? Speak up if you guys can't see, and I'll try to increase the font size. I have a subscription here, and it's got 3 different servers to find. They're all online right now. What I want to do is that— I'm just going to go and—I already created these services ahead of time, because I didn't want to take the time in case it took a long time to generate. I'm going to go ahead and create a new service here. Let me go back. Okay. Let me go here, and then I'll manage. So notice at the bottom right now— it does a really nice thing—it tests what my IP address is coming into the service. It says I don't have firewall rule for this. Do I want to set one up? Again, this is a new service. I just created it this morning. The firewall's completely closed to everyone It recognized the fact that in order for me to connect, I'm going to have to open up 1 port— I mean 1 IP address, and so I'm going to say yes, and it goes through and adds it. Do you want to manage the TestService2 now? And I say yes. Then it brings me— it should bring me eventually. By the way, this is my first time doing a demo over the Internet. I've always been really scared of doing this, because I've seen some really bad situations. If it goes bad, then I'll probably not do this again. Okay. I'm connecting to my service right now through the management portal, and I'm going to log on. So this is taking a little bit of time. Come on! So what it's doing in the background right now is that it's going through the Azure Portal— and so Azure SQL DB is a small portion of Azure Portal. We're going through the same framework that all the Azure customers do, and then when we go and manage the SQL service, then it goes through our portion of the server, so you can see here, okay? So let's see. Overview, databases. There was one place where I remember seeing something around—import/export. Just give me 1 sec. Okay. So there was one place here where I should have been able to see the firewall rules. Don't know where that is. Let me go back. It's in the management port, right? Okay, let me go back. Manage server. Here we go. It's not there. Let me try that 1 more time. It's changed portal bits since the last time I've used it, so you have to forgive me a bit. Oh, there we go. Thank you! Okay. Here right now, you'll see that this is a server-level firewall. When I clicked on yes, you saw that this is a second entry right there that added my client IP address to this rule. Now I should be able to— and here, you can also say that I want to allow the Windows Azure services. If you do this, it adds to this rule of IP address 0000, which is a special IP address that symbolizes Windows Azure. Now that I've opened up the port 2 myself, I should be able to connect, right? I can do things like— dashboard, databases—here we go. You'll see that they do some nice things here. For example, I can see what my server name is, and you can see the history— everything else in here. There was 1 place in here, also, that actually showed us the connection string. Where was it? Let me play around with this a bit. I'm going to go to management studio now. I'm going to connect. Enter my password. Remember when I told you about making sure you set the encryption turned on for your clients? You can do that through SSMS, as well. Just make sure that checkbox is checked. What'll happen now is that my client will validate the server cert, and if it turns out to be an invalid cert, then it will throw an error, and it will disconnect. Just remember to set that on—unfortunately, it's not set by default. (off-mic question) So the question is if I don't set that, it won't validate the certification, but it will still establish SSL. That's absolutely correct. By not setting that, what's happening is that the server is forcing the client to use SSL, but the client's logic is that, "Well, I didn't actually request it, so I don't care where the certificate came from." But the client says that I want to do SSL, then it means explicitly that I do care about using encryption. I do care about where the certificate came from. So that's why you should turn it on. So now that I'm connected, you'll see here that this kind of looks the same, except that all the administration—the security administration, at least, is not really down through—whoops— is not really done through this panel here where you have logins. That still shows up, but a lot of it is actually done through Master, okay? So you'll see things here like the tables, views, everything—security here. Now what I'm going to do is— I'm going to create a user— and so just to make sure I know who I am. I'm me. I'm logged in as serveradmin. I'm in Master right now. Unlike in SQL Server to issue a lot of these—create logins, create database commands, create everything else, you have to be a master. In SQL Server, you were able to do things for user databases in a lot of cases. Here you have to explicitly connect to Master. You can't be in the user database to do this. Here I'm going to go ahead and create a login. It's not complex enough. Let's try this. Let's try— Okay. I create a user test now, right, and if I look into system views—system tables here, you'll see that—where is it? It's not showing up yet. If I do—select, star from sys.SQLlogins, you'll see that the test exists down there, okay? (off-mic question) Am I on a different server? You're right. Thank you very much. So there we go. Right there. Before you were able to query the sys.serverprincipals, but in SQL Azure, you have to use a SQLlogins, okay, because there is no Windows Principals; there's just SQLlogins. The other thing I can do is I can do things like select star from sys.firewallrules, and then you see all the firewall rules— same thing that we saw in the portal. If I want to see what's set for the database and the database in this case is Master, obviously, I can do from sys.databasefirewallrules, and I don't have anything set right there. Now if I did want to have something created for the firewall rules, I could do something like this, and I've actually kind of shortcut this. I could do something like—here which is kind of the spec product, so SP set database firewall rules, and you give it a name, then you give it the beginning and the end range, and if I do that, then if I do another query in sys.databasefirewallrules, and you see that it exists there, and if I want to erase it, it's simple. I use a different SP, but I just say, "Delete firewallrule" instead of "Set firewallrule." I do that, and if I query this again, it's gone. Question? (off-mic question) So the question is if the Master database has to have every single rule allowed in order to— yes, kind of. So remember the Master database is just another database. I can great a user and give them access to database 1 but not give them access to Master, and if I set up a server-level firewall rules that says allow the IP address from that customer to come in, then he'll be able to get through into the database 1 but not into Master. The server-level firewall covers everything. It's a higher level than the actual individual database firewall rules. (off-mic question) I can have a server-level rules that says, "Allow this person to come in from this IP address to this database," or "Allow this person to come in." Then he'll be able to come in to any database in this server including Master, but if I decide that I'm not going to allow his IP address to come in at the server level— I only enable for one particular database, then he'll only be able to connect to that one particular database not to Master. (off-mic question) It will not block the logins. It's blocking direct connections to Master. So it's as simple as that. Now the only other thing that I really want to show you guys is that— remember that thing I told you about—the change password? I want to show you the consequences of doing that. So what I will do is that I will—first of all, if I remember what the password was— what's my password? 1234567. I'm going to go and connect here. Options—remember to turn on encryption. Connect. Let me try that again. Cannot connect to Windows. Did I type in the right password? My password, 1234567. Try that again. So I'm going to connect. That's right. That's right. Test. Then Options, Encrypt Connection, and Connect. Hm. Cannot open Master database requested by login. Login failed. Oh, you know why? My bad! So this is actually—inadvertently I showed you exactly what I told you about before. I haven't given him access to Master. If I create a user Master, then you'll see it. What I need to do over here again— what I need to do over here again is I need to create user— Create user test from login test. So now we have a login called test, right? Now if I go back here—and let me try to park this thing back here— and I connect, then that should work. The alternative thing I could have done is I could just create another database and gave him access to that database, right? And set the encryption connection, connect, and now he's in, okay? So now that he's in right now— I haven't given him any particular access— you can't see me adding to any particular role. He was just a normal user, but the dangerous this is that even though now he may be able to change his password, because remember when I told you to create alter login commands only work if you're in master— that consequent of that is if I go in and select myself—just to prove to you— woops—I guess this already had a connection in here. If I prove to you here that I am test, okay? I am in Master right now, so exactly what I said. If I try to select any of these tables, I'm actually getting data back. In this case here, I'm seeing all the principals to find in this database. The sys.logins that I mentioned before— that does seem to block it. It tells me that I don't have access—that's because I'm not a login manager, but I can do things like— I can see the firewall rules now. Doesn't mean I can change it but it does mean I can actually see data that I potentially should or even things like sys.databases, which not all people would want people to know exactly what databases are on the system, but myself as just a normal user now is able to actually go and see all of these different sets of metadata that you probably don't want to be exposed to the public. So this is why I"m saying that you probably don't want to create users in Master for people who are not administrators until we can do something about this. Any questions on that? All right. So I'm just going to wrap up now. This is to rehash some of the stuff we already talked about here. First of all if I haven't stressed enough, configure your client applications to validate the server's certificate. There's a lot of good it does here, and it actually doesn't change any of the performance or anything else from your application's perspective. It's already doing SSL encryption but without setting the encrypt on the client, you're not actually validating the server cert, so please do so. Use the most restrictive firewall rules—settings as possible. It does you no good if you decide to open up a firewall to all the IP addresses out there, because you might as well not have a firewall in that case, so use a firewall, have a very, very restrictive range of IP's, and if possible, limited to the database itself so that even if someone does attack a particular database, they'll be blocked from attacking other databases. It's not always possible, but if you can, you should. Minimize the number of users in the admin roles. This is kind of a general statement around security for everything. You should try to do the principal least privilege. Not everyone needs to be admin and carefully control who is. I admit that we don't have a lot of granularity in this case, but for now you should try to be very careful. As I just showed, avoid creating users in Master. There's exposing a lot of data to people that don't necessarily need it, and lastly what I want to mention is watch for future enhancements, okay? Take the security in the service very, very seriously. We're continuously trying to improve it. A lot of the things that you see— a lot of the things that we do may not be something that you necessarily see— maybe something that we do behind the covers. Like I said before, we may be doing infrastructure work or doing things to help prevent denial-of-services attacks, but watch for news to come in the near future. I think this year will be a very interesting year for security and for Azure SQL DB. With that, I'm doing the final mentor slides, I guess. I'll be at the Data Insights booth from 12:15 to 2:30 if anyone does want to come talk to me. I'll also be at the booth Wednesday and Thursday mornings, as well, in case you want to see me. The same Data Insights booths. Then the rest of these are just resources and stuff that I'm supposed to go through or else they don't invite me back. All right, with that said— oh, and please complete your evaluation. Not only do you get some free—potentially get some cool stuff, but it's good for us to know whether the content was useful or not, and I do appreciate the honest feedback. So with that said, thank you very much. If you have any questions, I'll stick around for a little bit longer, but thank you. (audience applause)

Video Details

Duration: 58 minutes and 6 seconds
Country: United States
Language: English
Genre: None
Views: 5
Posted by: asoboleva99 on Jul 9, 2013

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B314#fbid=kG7OLm6xV3l

Languages to MT: Fra, Ger, Spa, Bra, Kor, Jpn, Rus, CHT, Ita

Caption and Translate

    Sign In/Register for Dotsub to translate this video.