SSDT: How to protect the password contained in the publishing profile?
I want to create a publish task in Jenkins to automatically publish my database changes along with my application.
If I understand correctly, it is common practice to create a publish profile that includes the database name as well as the account (login and password) of the account used for deployment.
This means that the username and password for the deployment account will be stored in clear text on each development machine, as well as the source control server and the continuous integration server.
Even though I created a specific username and password for the deployment, this seems insecure to me.
Is there a workaround? I can only think of changing the password on the msbuild command line on the continuous integration server.
source to share
tl; dr version
Windows Authentication is the preferred secure way to connect to your instance of SQL Server, and if possible, it is recommended that you use it for connections.
When SQL authentication is used, by default the publishing profiles specify that no password is saved. For build servers and other sharing scripts, you may need to accept lower security levels (by editing the publish profile to add a password or set it as an option in the build config) or bypass it in some other way (a custom script that reads it from some secret storage, such as an encrypted value).
Long version
Windows Authentication: If at all possible, use Windows Authentication by granting the permissions needed by the users who need it. For continuous integration scenarios, you will need to grant the appropriate permissions to the account that the build server runs incomplete data in a recent white paper on the SSDT blog.
SQL Authentication: If you look at the publish profile (Open With ... Xml Editor), you can see that the password information is not actually stored there.
- If you choose "Save Password" you will have "Persist Security Info = True"; stored in the connection string, not the password itself.
- When connecting to a server / database in SSDT with Save Password enabled, the connection information is encrypted and stored in the registry under "HKEY_CURRENT_USER \ Software \ Microsoft \ SSDT \ ConnectionStrings". This must be present on the machine for a successful publishing using the publishing profile.
- Therefore, in a team environment, each user will need to connect at least once before this publishing profile will work for them. However, the password will be securely encrypted on the user's machines.
- For a build server, your options are more limited. One possibility is to manually log in as a user of the build server and then connect to the database, but this is not very scalable. To avoid the less secure options you mentioned, you will need to implement your own logic to securely store the password. You can look at the Secure Data API , which can be used to do something similar to what SSDT does, but at the per machine level, or use an encrypted config file .
If you need to use SQL authentication, I think that passing a password to the publish action as part of the build configuration might be the "best" tradeoff between ease of development and security. At the very least, you can restrict who can view and edit the build configuration in TFS without regular developers seeing it.
source to share