SSDT: how to protect the password contained in the publish profile?

Question

I want to create a publication task in Jenkins to automatically publish my database changes along with my application.

If I understood correctly, a common practice is to create a publish profile that includes the database name as well as the account (login and password) of the account used for the deployment.

This means that the deployment account username and password will be stored in clear text on each developer computer as well as on the version control server and the continuous integration server.

Even though I created a specific login and password for the deployment, it seems pretty unsecured to me.

Is there a workaround? I can only think of replacing the password within the msbuild command line on the continuous integration server.

Answer 1

tl;dr version

Windows Authentication is the preferred, secure method of connecting to your SQL Server instance and if it's possible to use that then it's recommended to use that for connections.

If SQL Authentication is used then the default in publish profiles is that the password isn't saved. For build servers and other shared profile scenarios you may need to accept lower levels of security (by editing the publish profile to add the password, or setting it as a parameter in the build configuration) or work around it in some other way (custom script that reads it from some kind of a secret store, such as an encrypted value).

Long version

Windows Authentication: If at all possible use Windows Authentication, giving permissions as required to users who need it. For Continuous Integration scenarios you would need to give appropriate permissions to the account the build server executes under - full details are in the recent whitepaper on the SSDT blog.

SQL Authentication: If you look at the publish profile (Open With... Xml Editor) you'll see that the password information isn't actually stored there.

• If you choose "Save Password" you'll have "Persist Security Info=True;" stored in the connection string rather than the password itself.
• When a connection is made to a server/database in SSDT with "Save Password" enabled, the connection info is encrypted and stored in the registry under "HKEY_CURRENT_USER\Software\Microsoft\SSDT\ConnectionStrings". This has to be present on the machine in order to successfully publish using the publish profile.
• Hence in a team environment every user would need to connect at least once before that publish profile would work for them. However, the password would be safely encrypted on user machines.
• For the build server, your options are more limited. One possibility is to manually log in as the build server user and then connect to the database, but this isn't very scalable. To avoid the less secure options you mentioned you'd need to implement your own logic for persisting the password securely. You can look at the Protected Data API which can be used to do something similar to what SSDT does but on a per-machine level, or use an encrypted configuration file.

If you have to use SQL Authentication I think passing the password into the publish action as part of the build configuration may be the "best" way to go in terms of a tradeoff between ease of development and security. At least that way you can restrict who can view and edit the build configuration in TFS and regular developers won't see it.