Skip to main content

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>

      For eg:
      CREATE ROLE rolserviceaccount

  • 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]
      GRANT EXECUTE TO [db_execute]

     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 as Member, 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


Post a Comment

Popular posts from this blog

Windows server 2012: where is my start button??

If you have been using Windows Server OS for a while, the one thing that will strike you most when you login to a Windows server 2012 is that there is no start button!!.. What??..How am I going to manage it?? Microsoft feels that you really dont need a start button, since you can do almost everything from your server  manager or even remotely from your desktop. After all the initial configurations are done, you could also do away with the GUI and go back to server core option.(In server 2012, there is an option to add and remove GUI). So does that mean, you need to learn to live without a start button. Actually no, the start button is very much there .Lets start looking for it. Option 1: There is "charms" bar on the side of your deskop, where you will find a "start" option. You can use the "Windows +C" shortcut to pop out the charms bar Option 2: There is a hidden "start area"in  the bottom left corner of your desktop

Use Diskpart to make drives online

Issue: In disk management, disk is shown as missing or Offline in Windows Resolution: The disks can be made online by using diskpart utility - Open a command prompt->type diskpart -Inorder to list the disks in the system type: list disk -Note down the number of the disk that you want to make online -Select that disk to operate upon, For eg:, if the disk number is 1, type: Select disk 1 -Now that particular disk will be selected as teh active disk. If you type "list disk" command once more, you can see a * symbol on the left side of the selected disk -Inorder to make the selected disk online type : online disk - If the disk is made online, you will get a message that the operation is completed successfully

Kubernetes best practices in Azure: AKS name space isolation and AAD integration

Once you have decided to run your workloads in AKS service in Azure, there are certain best practices to be followed during design and implementation. In this blog we will discuss two of these recommended practices and the practical aspects of their implementation- Azure AD integration and name space isolation While AAD helps to authenticate users to your AKS cluster using the existing users and groups in your Azure AD, name space isolation provides logical isolation of resources used by them. It is useful in multi tenant scenarios where the same cluster is being used by different teams/departments to run their workloads. It is also useful in running say a dev, test and QA environment for organization in the same cluster. Combining AAD integration with name spaces allow users to login to their namespace using their Azure AD credentials AAD integration with AKS : The following Microsoft document will get you started  with AAD integration of AKS cluster.: https://docs.microsof