Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

Question

I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database project.

The following command creates the database for me if it's not yet in place:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d ASPSTATE -E

At first I tried running in as part of a SQLCMD script in general. The following approach in SQLCMD mode:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d myStateDb -E

Then I tried to include it in the Post Deployment script basically the same way:

:!!C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -ssadd -sstype c -S localhost -d TESTdb -E

This ends up with the Visual Studio compiler complaining about a backslash exactly in this line.

"SQL72007: Thy syntax check failed. Syntax error close to '\' in the batch near: [The command above again]"

Since it's working in SSMS I do not understand why my Post-Deployment script keeps complaining?

Answer 1

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):

1. 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.

2. 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.

3. 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.