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    

select m.name as Member, r.name as Role

from sys.database_role_members

inner join sys.database_principals m on sys.database_role_members.member_principal_id = m.principal_id

inner join sys.database_principals r on sys.database_role_members.role_principal_id = r.principal_id