"The target database schema provider could not be determined" With VSDBCMD but Only When /ConnectionString is Specified

Question

I have been developing a deployment script to deploy our database project to multiple database instances. I'm testing it by running it manually. It is a very simple script, and just runs VSDBCMD against a deployment manifest created to deploy our base, or template database. In order to deploy to different databases on the same server, the script uses the deployment manifest, but then also specifies the /p:DatabaseName and /p:TargetDatabase properties.

This has been working all day.

We are using SQL Server authentication for these databases, and I wanted to be able to specify the username and password as parameters to the script, so that they wouldn't have to be hardcoded. The only way I could see to do that was to use the /ConnectionString switch. That's what caused the failure. Even when I specify the exact same connection string as is stored in the deployment manifest, the script fails with the error "The target database schema provider could not be determined". The script:

function DeployDatabase([string] $manifestPath, [string] $password, [string] $instance, [string] $server, [string] $user)
{
    $vsdbcmdPath = "c:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy"
    $vsdbcmd = "$vsdbcmdPath\vsdbcmd.exe"
    $outputScriptPath = "$instance.sql"
    $logPath = "$instance.log"
    $connectionString = "Data Source=$server;User ID=$user;"
    & $vsdbcmd /dd /dsp:SQL /manifest:$manifestPath /a:Deploy /p:DatabaseName=$instance /p:TargetDatabase=$instance /script:$outputScriptPath /cs:"$connectionString" | 
        Out-File -FilePath $logPath
}

# Actual call is omitted to protect the $server, $user and $password

Simply omitting the /cs switch allows this to succeed again. Note from the connection string, above, that it's not even necessary to specify the password in the connection string in order to cause a failure.

The target database server is running SQL Server 2008 R2, and is up to date on patches.

I'd like to know how to avoid this error, or else I'd like to know a better way to specify the username and password when deploying, without requiring the password to be in the deployment manifest in cleartext.

Answer 1

I am having the same issue. Did you try to set the instance name?

The SQL Instance name in the connection string could be wrong.

Also doublecheck if User Id and Password are set, if you are not using Integrated Security.