We are doing a project for our Business Intelligence team. For this we need a central database that is always available. This is where Azure comes to mind. I’ll describe here how I managed to configure Azure SQL Server to be available to a WPF application on laptops connected to internet.

First we created a new SQL Database. In the wizard / blades we opted for a new SQL Server instance, provided the database name, the server name, an Admin account name with secure password and waited for the creation process to complete. This is the easy part.

To enable access for all members of the BI team we allowed all ip-adresses in the Azure SQL Server firewall. Some people see this as a security risk, we see this as a business requirement 😉

Just to be a little prepared we enabled the Auditing of every statement to Azure Tables in a newly created storage account.

From within Visual Studio we created a Login in the master database for every member of the BI team and one for the logging. We used the connection provided by the Azure portal and just changed the database to master. The logging user (and password) will be in the application config in the log4net section.

CREATE LOGIN logging WITH password='******';

Our solution has a SQL Server Database Project for versioning of the database objects. In there we have a PostDeploy script for adding the logging user and granting only INSERT rights on the log table. The script is below.

IF DATABASE_PRINCIPAL_ID ('logging') IS NOT NULL
	DROP USER [logging];
GO	
CREATE USER [logging] FOR LOGIN [logging];
GO
GRANT INSERT ON OBJECT::[dbo].[Log] TO [logging];
GO

Now when we have a centralized database for our BI team that is always available.