search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
308 stars 19 forks source link

[Synapse Serverless] SqlPackage Extract is not adding the database scoped credential secret to the generated deployment script #253

Open ruippcunha opened 1 year ago

ruippcunha commented 1 year ago

Steps to Reproduce:

  1. Make sure you have a database scoped credential using Shared Access Signature identity and a secret in your Synapse Serverles SQL pool. In case you don't, you can create this as a sample credential:

CREATE DATABASE SCOPED CREDENTIAL [mycred] WITH IDENTITY = N'SHARED ACCESS SIGNATURE', SECRET = N'$238746ewfhgsdjkfhsdkjfh';

  1. From the command line, make sure you run the SqlPackage using v161.8089.0, as this is the version that added support for serverless SQL pools in Extract and Publish operations.
  2. run the SqlPackage using the EXTRACT action, selecting your Synapse SQL Serverless endpoint and sql pool (used in the 1st step) .
  3. Once the extract is finished, unpack the generated dacpac file to a local folder
  4. From that folder, open the sql model file and then search for "SCOPED CREDENTIAL"

  5. You will see that the CREATE DATABASE SCOPED CREDENTIAL statement is not including the SECRET argument. When deployed to the target database, all objects that depend on this credential will fail to authenticate against the external location.

    This screenshot below is an example of a sql model that was generated without the secret information

image

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage)

dafad-dew commented 1 year ago

Pretty sure this has always been the case - the SECRET clause isn't generated, and for good security reasons too! This is what happens in the latest version against SQL Server 2019. It would be helpful if the generated script showed that the SECRET was missing in some way though as you only find out when you create an external resource which uses the credential.

ruippcunha commented 1 year ago

shouldn't we preserve the sql statement as is from the source? if the user has explicitly provided a value for this SECRET argument, then they require to authenticaticate against the external location using this method, and they expect this syntax to be preserved when deploying the dacpac to the target database.

By omitting this SECRET argument from the statement, we are not only distorting the authentication method but also requiring users to manually recreate the scoped credential and all dependent objects in the target database, as querying these objects will fail due to invalid or missing authorization.

Regarding the security concerns, since these credentials are protected by the Master Key, shouldn't we simply encrypt/mask the secret value as this would allow users to take advantage of using SQLCmd variables to override this value something that they will not be able to if we omitt the clause?