none
Passwords and Connection Strings

    Întrebare

  • My app is ASP.NET (non-core) running in an App Service and using Azure SQL

    I'm transitioning from development to operations.  I have a staging DB and a production DB, both on the same Azure SQL Server.

    I need to accomplish the following:

    1)  Implement separate passwords for the staging and production DB's (for external access via for example PowerBI)

    2)  Hide the production connection strings (which include DB passwords) from the developers

    It's a small team and in the transition the developers are doing the deployments, and will continue to do so for a while. 

    For 1) it seems like the DB password is for the Server, not the DBs.  So I'm thinking that means I need to get two separate servers,  for one for the staging DB and one for the production DB.

    => Is that correct?

    For 2)

    2a) I'm thinking that I could, as Azure admin, just after a deployment to production, update the production DB (server) password and the DB connection strings, and put those passwords someplace safe, not shared with the developers. 

    2b) then, is there a way for the developers to still deploy the code to production, without knowing or being able to see/read the connection strings? 

    2bi - I guess first they should simply --not overwrite-- the existing connection string config file.   But still, if they have access to do the deployment, it seems that they would be able to see the existing connection strings.

    2c - I also know vaguely that .Net provides encrypted connection strings, and that Azure has a key vault service.  Then there are deployment blades...

    An answer to 1) and any suggestions on how to accomplish 2) would be appreciated.

    Thanks!

    luni, 2 iulie 2018 00:33

Răspunsuri

  • Yes, that is correct. If you would like to use the SQL Server Admin account with separate passwords, you will need to have two separate Azure SQL Database instances deployed. You could, however, create two separate login IDs (that mirror the SQL Server Admin account but have access only to a specific DB) for a single Azure SQL Database that allow access to your Production Database and to your Pre-Production. This is role based access.

    Note: When I say 'Azure SQL Database' instance, it infers SQL Server. Since there are multiple SQL offerings with Azure, Azure SQL Database is one specific deployment offering. If you choose to deploy a second Azure SQL Database, you can choose to add it to an existing SQL Server instance or you may deploy an additional SQL Server instance to host the new database. Other Azure SQL offerings include Azure SQL Managed Instance, Azure SQL Elastic Pools, and Azure SQL inside a VM. 


    • Marcat ca răspuns de codequestor vineri, 13 iulie 2018 01:05
    marți, 10 iulie 2018 19:07
    Moderator

Toate mesajele

  • Hi Codequestor,

    Pertaining to your first question:

    So, with SQL Database, the users + credentials are kept in the system database. There is no way to keep them separate, unless you set-up two Azure SQL Databases. One for dev/pre-production and a second for production.

    Pertaining to your second question:

    You can create role specific users/logins that only allow users to access specific databases within a single Azure SQL Database instance. The connection string is the same, and it can be encrypted.

    Additional Information:

    I am going to present a couple links that walk you through most of the questions you are asking. I think that creating a production instance and a dev/pre-production instance is the best way to go.

    Controlling and granting database access to SQL Database and SQL Data Warehouse

    Tutorial that walks you through the various aspects of securing and granting access to Azure SQL Database:

    Secure your Azure SQL Database

    Please ask additional questions to continue the dialogue if the above information is not helpful or if additional questions arise.

    Regards,

    Mike

    luni, 9 iulie 2018 21:38
    Moderator
  • Hi Mike,

    Thanks for your input.

    1) To clarify the first question (and please pardon the bolding):  I'm looking at the difference between a Azure SQL Server, and an a Azure SQL Database.  Right now I have both staging and production databases on the same server, and a single user id and password pair for the server.  I'm assuming that if I want to have separate passwords for the databases, I need to put them on separate servers (and thus pay for two servers).  Is that correct?  Or is there a way to have different passwords for databases on the same server?   

    2) The links are on target;  I'll review them in more detail and post back here if there are additional questions.

    Thanks!

    marți, 10 iulie 2018 17:03
  • Yes, that is correct. If you would like to use the SQL Server Admin account with separate passwords, you will need to have two separate Azure SQL Database instances deployed. You could, however, create two separate login IDs (that mirror the SQL Server Admin account but have access only to a specific DB) for a single Azure SQL Database that allow access to your Production Database and to your Pre-Production. This is role based access.

    Note: When I say 'Azure SQL Database' instance, it infers SQL Server. Since there are multiple SQL offerings with Azure, Azure SQL Database is one specific deployment offering. If you choose to deploy a second Azure SQL Database, you can choose to add it to an existing SQL Server instance or you may deploy an additional SQL Server instance to host the new database. Other Azure SQL offerings include Azure SQL Managed Instance, Azure SQL Elastic Pools, and Azure SQL inside a VM. 


    • Marcat ca răspuns de codequestor vineri, 13 iulie 2018 01:05
    marți, 10 iulie 2018 19:07
    Moderator