(SQL) Tip of the Day: Contained Users and Firewall Rules

  • Article

Today’s Tip…

Every so often you may run into an issue where you see an authentication error or other timeout caused by your SQL Azure Master database. With SQL Azure you can now contain your database users and firewall rules completely in your user database so that you do not have to contact the Master DB for connections making your database completely contained.

To create the contained database user connect directly to your database and run:

CREATE USER user_name WITH PASSWORD = 'strong_password';

To add the firewall rules use sp_set_database_firewall_rule such as:

-- Enable Windows Azure connections.

EXECUTE sp_set_database_firewall_rule N'Allow Windows Azure','0.0.0.0','0.0.0.0';

Which will allow Windows Azure Services or:

-- Create database-level firewall setting for only IP 0.0.0.4

EXECUTE sp_set_database_firewall_rule N'Example DB Setting 1','0.0.0.4','0.0.0.4';

Which will only allow IP address 0.0.0.4 into the database.