Azure SQL Server for centralized database

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 😉
sqlazure-firewall-settings
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.
sql-database-postdeployscript

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.

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

One Response to Azure SQL Server for centralized database

  1. Pingback: Azure SQL Database upgrade announced | .NET Development by Eric

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s