locked
Hiding Database List in Azure Sql v12 RRS feed

  • Question

  • Hi, 

    We have switched some of our databases onto Azure Sql V12 databases and historically with Sql 2008/2012 on premises etc we used to be able to restrict users from seeing all of the databases on the same server by running the below 

    DENY VIEW ANY DATABASE TO [the_user_name]

    Is there a new method on Sql V12 for Azure to do similar?

    thanks in advance

    James

    Wednesday, April 6, 2016 1:44 PM

Answers

  •  Master DB in Azure SQL Database works in a very different way than on on-premise SQL Server, for example: for on-premise SQL Server, access to the master DB is granted to public (i.e. any principal with permission to connect to the server will have access to master DB), while in Azure SQL DB, access to master DB must be explicitly granted by creating a user. The reason for this is that the actions allowed on Azure master database are significantly limited other than administrative tasks via membership to the dbmanager & loginmanager roles.

    The only other action that where a user in master DB is needed is to allow users (with logins) to change their own passwords. As a workaround, we could recommend contained users with passwords in the user databases when they only need access to a single database, or using Azure Active Directory based users when they need access to multiple databases. BTW. Using AAD is highly recommended.

     As you mentioned, in case of Azure SQL DB, there is no permission to obfuscate the visibility of DB names to users in master DB since the VIEW ANY DATABASE does not exist. The recommendation is that you limit access to the master DB only to principals who need to perform administrative tasks.

     I hope this information helps.

    -Raul Garcia

      SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, April 6, 2016 7:26 PM

All replies

  • Hello,

    Only the SQL Azure server administrator
    can query system views/DMVs on Standard and Basic tiers. Only via system views and DMVs you can lists the databases.

    On Premium databases, only DBO can query DMVs at the server level.


    In addition, on Master no one has view database permissions.


    Bottom line, logins created on SQL Azure do not have access to list databases by default.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, April 6, 2016 2:23 PM
  • Thanks Alberto,

    Your statement of "sql azure do not have access to list databases" is what I have read, however, this does not appear to be the case on our environment. 

    Our process of creating the user to connect and create the database tables is

    CREATE LOGIN [username] WITH password='mypassword'GO

    CREATE USER [xxxxxxx] FOR LOGIN [xxxxxxx] WITH DEFAULT_SCHEMA=[dbo]GO

    which we run against master

    Then,

    CREATE    USER[xxxxxxx]FORLOGIN [xxxxxxx] WITHDEFAULT_SCHEMA=[dbo]GO

    EXEC sp_addrolemember 'db_owner', 'xxxxxxx';GO

    which runs against our database.

    when logging in via management studio we can see a list of the other databases, granted we can't access anything within.

    Regards

    James

    Wednesday, April 6, 2016 2:42 PM
  •  Master DB in Azure SQL Database works in a very different way than on on-premise SQL Server, for example: for on-premise SQL Server, access to the master DB is granted to public (i.e. any principal with permission to connect to the server will have access to master DB), while in Azure SQL DB, access to master DB must be explicitly granted by creating a user. The reason for this is that the actions allowed on Azure master database are significantly limited other than administrative tasks via membership to the dbmanager & loginmanager roles.

    The only other action that where a user in master DB is needed is to allow users (with logins) to change their own passwords. As a workaround, we could recommend contained users with passwords in the user databases when they only need access to a single database, or using Azure Active Directory based users when they need access to multiple databases. BTW. Using AAD is highly recommended.

     As you mentioned, in case of Azure SQL DB, there is no permission to obfuscate the visibility of DB names to users in master DB since the VIEW ANY DATABASE does not exist. The recommendation is that you limit access to the master DB only to principals who need to perform administrative tasks.

     I hope this information helps.

    -Raul Garcia

      SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, April 6, 2016 7:26 PM