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:
:!! dir
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):
Run aspnet_regsql.exe
via 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.