[This article was contributed by the SQL Azure team.]
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.
The Benefits
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.
Passwords
Best practice for dealing with logins and password on the desktop dictate:
- The login and password to SQL Azure should never be stored in the code running on the desktop,
- The login and password shouldn’t be stored on the the user’s hard drive.
- Every desktop user should have their own login and password to SQL Azure.
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.
Firewall Settings
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: https://www.whatsmyip.org/ with their web browser and report the IP shown to their SQL Azure administrator.
Summary
Do you have questions, concerns, comments? Post them below and we will try to address them.