SQL Server Management Studio (SSMS) only supports a subset of the SQLCMD commands, and SQL Server Data Tools (SSDT) only supports a subset of the SQLCMD commands that are allowed in SSMS. The
[:]!! SQLCMD command, while supported by SSMS, is not supported in SSDT Pre-Deployment or Post-Deployment SQL scripts. This is most likely due to the large amount of confusion that would be caused by people not realizing that the command that is executed by the
[:]!! SQLCMD command actually runs on the machine where SQLCMD is running, which is not guaranteed to be consistent across executions, and is not necessarily the same machine that SQL Server is running on.
For example, when doing development in Visual Studio on your local workstation or laptop and running this deployment, the command would execute on your local workstation or laptop. Then, when deploying to testing / QA, it would run on whatever machine does that deployment, which is typically not your local workstation. And then when deploying to Production, it would again run on the build server, which may or may not be the same server that handled the deployment to the QA server.
If you change your Post-Deployment to do just the following and then do a build:
then you would see this error:
72006: Fatal scripting error: Command Execute is not supported.
You have three choices (that I can think of, at least):
xp_cmdshell (which will run under the security context of the Log On account associated with the SQLSERVER service). This option allows you to manage the call to
aspnet_regsql.exe within the deployment SQL script and will get executed whenever you do a build.
Edit the "Post-build event" to call this same command line. This will allow
aspnet_regsql.exe to be executed in the security context of whatever Windows / Active Directory Login runs the deployment. But, the execution of
aspnet_regsql.exe is not in the SQL script, so the publish process needs to use SqlPackage.exe so that it knows to execute whatever commands are in the "Post-build event". But, this is at least still part of the SSDT publish process and will get executed when you do a build.
Handle this outside of the SSDT deployment by creating a CMD script that will be executed on the server running SQL Server, and that calls SQLCMD to run your deployment SQL script and then
aspnet_regsql.exe. This option, however, is outside of the SSDT publish process and won't get executed when doing a build in Visual Studio.