Subscribe to this blog

Follow by Email

Search This Blog

Pages

Securing Windows Azure SQL using service accounts

When you create an SQL server in Windows Azure,you need to create an administrator username and password . This will be the super user account for that server, using which you can carry out any operation in any databases of the databases. That means you can also delete or rename databases using this account.Hence you need to be very careful if you are planning to use this credentials in your application to access the Azure SQL database.

Creating service accounts for SQL is a safe option to restrict access to you database , and also to avoid use of the super admin account.You could create service accounts and add them to appropriate SQL roles which has required permissions in the database, say read, write, execute etc..Lets see how to achieve this:

First create an SQL login after connecting to the Master DB. Note that you would need your super admin account for connecting to the master DB.

CREATE LOGIN <ServiceAccountname> WITH password='<password>'

For eg: CREATE LOGIN testuser1 WITH password='Password'

Service accounts are intended to connect to a specific database. As the next step connect to your target database and create a new user from the login you created above

CREATE USER <ServiceAccountname> FROM LOGIN <ServiceAccountname>;

For eg: CREATE USER testuser1 FROM LOGIN testuser1

Now you have created the service account in the database, you will need to assign required level of permissions for the user in the database. We will accomplish this using SQL roles with the correct permission levels.Connect to the target DB and execute the following to create what we can call as a service account role

CREATE ROLE <rolename>

GO

For eg:

CREATE ROLE rolserviceaccount

GO

Now assign the required rights for the service accounts role (again to be executed on the target DB)

EXEC sp_addrolemember N 'db_datawriter', N '<rolename>'

EXEC sp_addrolemember N'db_datareader', N'<rolename>'

EXEC sp_addrolemember N'db_ddladmin', N'<rolename>'

For eg:

EXEC sp_addrolemember N 'db_datawriter', N 'rolserviceaccount'

EXEC sp_addrolemember N'db_datareader', N'rolserviceaccount'

EXEC sp_addrolemember N'db_ddladmin', N'rolserviceaccount'

Please that the roles used above are inbuilt sql roles, which had read,write and ddladmin rights as the names indicate.You are adding the role that you created as member of those inbuilt roles for getting the required permissions

If you need to provide execute permission, first you could create a db_execute role and provide it execute permissions, and then later make your service account role a member of db_execute

CREATE ROLE [db_execute] AUTHORIZATION [dbo]

GO

GRANT EXECUTE TO [db_execute]

GO

EXEC sp_addrolemember N 'db_execute', N '<rolename>'

The last step is to make your service account as member of the corresponding serviceaccount role

For eg:

EXEC sp_addrolemember N'rolServiceaccount', N'testuser1'

You can verify that the permissions are all set correctly using the following sql query

A working mom with head in the cloud!! Literally.. I have been working in IT industry for close to 12 years now. Starting with Datacenter management, Virtualization and now cloud computing. Currently, I am working as a Technical Consultant in Microsoft focusing on Cloud and Infra technologies. I enjoy sharing my experiences in my blog, but the opinions expressed in this blog are my own and does not represent those of people, institutions or organizations that I may be associated with in professional or personal capacity, unless explicitly stated.