There are a lot of articles and discussion about calling SQL Azure from Windows Azure; however, I am personally fascinated with calling SQL Azure from the Windows’ desktop. This article will talk about some of the considerations of calling SQL Azure from an application running on a user’s local computer, and best practices around security.
Clearly, the biggest benefit is that you can read and write relational data to a remote database using ADO.NET without having to open access to an on premise database to the Internet. Any desktop computer can access SQL Azure as long as it has Internet access, and port 1433 open for outbound connections. To deploy such a system in your datacenter you would need to deal with installing, maintaining redundant copies of SQL Server, opening up firewalls and VPN permissions(to secure the database), and install VPN software on the client machine.
That said, it is up to the architecture of the desktop software to maintain a secure environment to the SQL Azure server and protect the access to the data on SQL Azure. Access to SQL Azure is controlled via two mechanisms: a login and password, and firewall settings.
Best practice for dealing with logins and password on the desktop dictate:
It is very tempting to hard-code a global login and password in your code, giving every user of the desktop software the same access permissions to SQL Azure. This is not a good practice. Managed code is very easy to decompile and it is very easy to read the hard coded logins and password. Even fully compiled code like C++ provides only a little more protection. In any language, do not hard code your login and passwords in your code where users have access to your .dlls and .exes .
Though it is possible to safely store your login and password on your hard drive by hashing it with the windows login token to encrypt it, the code and the security review of the code make it prohibitive. Instead, it is best not to store the login and password at all. The best practice would be to prompt the desktop user for the login and password every time they use your application.
Every desktop user should have their own login and password to SQL Azure. This blog post discusses how to add additional logins and password beyond the administrator account. This allows you to restrict access on a user-by-user basis at anytime. You should not distribute the administrator login and password globally to all users.
SQL Azure maintains a firewall for the SQL Azure servers, preventing anyone from connecting to your server if you do not give their IP address permissions. Permissions are granted by client IP address. Any user’s desktop application that connected to SQL Azure would need to have the SQL Azure firewall open for them in order to connect. The client IP would be the IP address of the desktop machine as seen by the Internet. In order to determine your client IP address, the user could connect to: http://www.whatsmyip.org/ with their web browser and report the IP shown to their SQL Azure administrator.
Do you have questions, concerns, comments? Post them below and we will try to address them.
Isn't the Isolated Storage precisely designed to handle this sort of situation? Asking passwords for every usage (nothing recorded on disk) tend to be weak because users either choose weak passwords or write the password down on a post-it (because it's too tedious to remember complex password).
Like I said above: "Though it is possible to safely store your login and password on your hard drive by hashing it with the windows login token to encrypt it, the code and the security review of the code make it prohibitive"
Isolated Storage is a good start, isolated storage is secured from less-trusted applications, but trusted computer users, administrators, and trusted code have full access to the file system in which isolated storage files are kept. Just using isolated storage doesn't encrypt the data stored in it.
Obviously, security of login/password information is a very complicated topic, and one that we can go back and forth on. I would be remise in this context to give you a security recommend that didn't get review by the Microsoft security team. However with a little searching you can find some really good articles about how to do this. All of which are outside of the scope of the blog post.
One more thing: The users are not picking the passwords for access to SQL Azure, they are assigned by the SQL Azure Administrator. You can make them as strong as you want, and I would suggest you make them strong. That doesn't exclude the post-it note.
I think we could learn some idea from AD. Could we provide a "Integrated Security" authentication to accept the Windows Live ID, even Open ID. or authenticate the client based on the certificate installed in Client. I think that why Microsoft introduce Azure AppFabric Access Control.
Do we have plan to do integrated the SQL Azure authentication with Azure AppFabric Access Control?
There are security risk to integrate Windows Live ID with client application. Nobody know what the client application will do with your Windows Live ID. But the CardSpace could be a good choice.
I am very interested in winforms + sql azure and was glad to find this post. Your security recommendations are no doubt prudent (and thanks for writing about them) but have left me a bit unsure re what a best practice might be. For example, how would one communicate the user's password to them? Via email I supposed but that's not very secure sounding. What about requirements to periodically change the password? It seems that there could be a lot of admin/end user interactions around password maintenance.
It would be great if tooling was added to sql azure which helped with these issues. Is that at all likely?
I know you were not eager to post any direct pointers to some "good articles" re security of login/password in this context but I hope you'd consider doing so anyways. Or some other reader...it's a complex topic and how can a neophyte be sure one is reading a "good article" <g>.
Another issue that I wonder about is the firewall. In general I am glad to have the restriction in place. But to build a distributed winforms app that relies on sql azure, the sql azure admin would have to open an ip address 'manually' for any location that a client might need to access the application from. Currently we use vpns to allow access to data from outside the intranet; it's not difficult to manage because the user does not need to know or report the ip address that they're connecting from (hotel room etc). Certainly the sql azure firewall approach works; but it seems to require more hand-holding. I don't really know anything about AppFabric (which another poster mentioned), maybe it will be a solution eventually?
One other question - any clues re when the interface between sql azure and ssms will catch up with ssms + sql server?
There currently is not a good interface from Microsoft for user login and password management for SQL Azure. It is likely there will be in the future, because that would benefit a lot of our users. This is something that you could build for your application using Windows Azure web role, which would call your SQL Azure database. You could include email confirmation, etc..
I searched for a really good article about Isolated Storage and encrypting the data, I can't find one that I really like or would recommend. It can be done, I have done it before.
You can still use VPNs if you want, just have the user's VPN to your network, and then open up your outside facing IP addresses from your network in the firewall, enforcing that only people inside your network, or those that have VPN'd can access SQL Azure.
Better support for SQL Azure from SSMS is a popular request, one that everyone would like to see done soon.