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>
       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


Comments

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

Install nested KVM in VMware ESXi 5.1

In this blog, I will explain the steps required to run a nested KVM hypervisor on  Vmware ESXi. The installation of KVM is done on Ubuntu 13.10(64 bit). Note: It is assumed that you have already installed your Ubuntu 13.10 VM in ESXi, and hence we will not look into the Ubuntu installation part. 1) Upgrade VM Hardware version to 9. In my ESXi server, the default VM hardware version was 8. So I had to shutdown my VM and upgrade the Hardware version to 9 to get the KVM hypervisor working. You can right click the VM and select the Upgrade hardware option to do this. 2)In the ESXi host In /etc/vmware edit the 'config' file and add the following setting vhv.enable = "TRUE" 3)Edit the VM settings and go to VM settings > Options  > CPU/MMU Virtualization . Select the Intel EPT option 4) Go to Options->CPUID mask> Advanced-> Level 1, add the following CPU mask level ECX  ---- ---- ---- ---- ---- ---- --H- ---- 5) Open the vmx

Virtual fibre channel in Hyper V

Virtual fibre channel option in Hyper V allows the connection to pass through from physical  fibre channel HBA to virtual fibre channel HBA, and still have the flexibilities like live migration. Pre-requisites: VM should be running Windows Server 2008, 2008 R2 or Windows Server 2012 Supported physical HBA with N_Port Virtualization(NPIV) enabled in the HBA. This can be enabled using any management utility provided by the SAN manufacturer. If you need to enable live migration, each host should be having two physical HBAs and each HBA should have two World Wide Names(WWN). WWN is used to established connectivity to FC storage.When you perform migration, the second node can use the second WWN to connect to the storage and then the first node can release its connection. Thereby the storage connectivity is maintained during live migration Configuring virtual fibre channel is a two step process Step 1: Create a Virtual SAN in the Hyper-V host First you need to click on Virtual