Azure DB permission issue RRS feed

  • Question

  • I'm relatively new to Azure Sql Database coming from on-prem. Moved to database firewall rules from server level so only "dba's" can connect to all DBs with elevated permissions. A developers AD group was the AD admin and was replaced with a new DBA AD group. I was in this developers group but was removed after being added to the DBA AD group. The server admin is not the developers group. Added relevant IPs to db firewall rules and removed all but our public IP from server firewall rules. Added the developers group as a contained user in each db with Select, Execute and View Definition at the db level. All seemed fine but the intention is to restrict developers group from creating objects on this server. However, I can connect using developers@mycompany.com and can create dbo objects. The developers group is not in any db roles and is not a user in master and does not own the db in question.  I dropped the developers group as a db user but was still able to connect although I didn't wait long before adding it back. What am I missing? Thanks in advance!

    Friday, March 29, 2019 5:30 PM

All replies

  • Hi,

    In your specific case, you may have some remaining role assignment that is granting access to developers@mycompany.com such as in the Active Directory role/group assignment?! Hard to reverse engineer without looking around. What I have detailed below is the process for setting this up. 

    This is the document when it comes to configuring Azure Active Directory authentication with Azure SQL. It covers all PaaS deployment options (SQL Single Instance, Elastic Pools, Managed Instance, Data Warehouse) and covers all authentication methods. You do need to be aware that there are explicit instructions for "Managed Instance" and "SQL Database Server". So, assuming you have an Azure SQL Database (Single Instance or Elastic Pools), you would step through the document as follows:

    Create and populate an Azure Active Directory

    Followed by the next two sections:

    Associate or add an Azure subscription to Azure Active Directory
    Create an Azure AD administrator for Azure SQL server

    At this point you should have an SQL Administrator (created when you set-up the instance at that which is currently used to access the database) and you now have an Active Directory SQL Admin configured in the directory. The next step is to provision that AAD SQL Admin to the Azure SQL instance.

    Just a note about using individual accounts versus distribution account (email alias). You are limited in the AAD authentication method available based upon using groups versus individual account (AAD Password, MFA, AD Integrated). If this is going to be a group, this should be db_owner privileges only. There is only one level of permissions here and that is the same as SQL Admin.

    The provisioning process is unique to Managed Instance and Azure SQL Database Server:

    Provision an Azure Active Directory administrator for your Azure SQL Database server

    - and -

    Configure your client computers

    Managed Instance provisioning process:

    Provision an Azure Active Directory administrator for your Managed Instance

    This is where you add your developers and other users you do not SQL Admin privileges who require AAD authentication. This also includes application access. 

    "Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, the same way as you grant permission to any other type of user. Typically grant permissions to database roles, and add users to roles."

    Create contained database users in your database mapped to Azure AD identities

    I hope this information help. Please let us know if you have additional questions.


    Friday, March 29, 2019 7:53 PM
  • Thanks Mike. This is a SQL single instance with 6 databases I'm working with. I had followed the instructions for changing the AD Admin and assume it was successful as the previous account was removed from the master database and the new one was added.  I'm also assuming Azure AD is set up correctly as that is outside of my responsibility. The DBA Azure AD group is of type 'synced' so I'm guessing my office domain credentials are synced with Azure AD.  I can connect to the server using mylogin@mycompany.com via SSMS using <default> as the db name and ,as expected, can see all databases on this server. I had a developer who is a member of the Developers group connect to a database via SSMS with his joe@mycompany.com account and he received an error trying to create a dbo table. However, he then connected using Developers@mycompany.com and was able to create a dbo table. The Developers group is an external group user in the database. Neither joe@mycompany nor Developers@mycompany.com are database users and I would not expect they need to be. The Developers group has no user or master database role membership.  I concur that some residual permission my be outstanding. Can you suggest some other places to look? Maybe punt and create a new developers group? 
    Monday, April 1, 2019 2:37 PM
  • You will need to run a query (or set of modified queries based upon T-SQL provided) on the master database and query on each of the user databases (x6). By modified queries, you may choose to narrow the scope of these queries down to the developers@mycompany.com login identify the differences specific to this entity.

    Run the following T-SQL on the master database:

    select l.name as [login name],u.name as [user name] from sysusers u inner join sys.sql_logins l on u.sid=l.sid

    Run the following T-SQL on each User database:

    SELECT * FROM sys.sysusers;

    Now you will need to perform the following exercise:

    • To find the login mapped for a user, look at the sid column from sys.sysusers.
    • This value corresponds to the sid column from sys.sql_logins in the master database.

    The unfortunate part is that you cannot discover the login name for the SID while connected to the user database. You will need to connect separately the master database once you have the sid and query sys.sys_logins to get the name.

    With this information you should be able to identify any logins that have database roles that should not exist. There is a process for populating the Azure Active Directory tenant in a federated manner but since that appears to be working, it is more about identifying the permissions currently assigned and making corrections to those that need attention. The T-SQL steps provided above should help you extract that information. 

    "Azure Active Directory (Azure AD) Connect lets you configure federation with on-premises Active Directory Federation Services (AD FS) and Azure AD. With federation sign-in, you can enable users to sign in to Azure AD-based services with their on-premises passwords--and, while on the corporate network, without having to enter their passwords again."

    Monday, April 1, 2019 7:57 PM
  • you should also be able to get some insights using the following query when being connected to a user database

    select * from sys.user_token

    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Tuesday, April 2, 2019 2:51 PM
  • The direction, and better practice, on this is to phase out the use of the common developer@mycompany.com and have developers connect with their credentials which I have no problem auditing. Thank you very much for you responses. I sincerely appreciate it.  
    Tuesday, April 2, 2019 5:04 PM
  • Thanks Andreas. The sys.user_token is documented as unavailable in Azure Sql Database so I cannot rely on the results even though it returns information.
    Tuesday, April 2, 2019 5:08 PM
  • Thanks Andreas. The sys.user_token is documented as unavailable in Azure Sql Database so I cannot rely on the results even though it returns information.

    Thanks for pointing this out.

    I can assure you sys.user_token is supported in Azure SQL Database. I will fix this in the documentation.

    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Friday, April 5, 2019 5:30 PM