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

  • 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 connectiion 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.

    BTW, I notice this forum is within the "Archived Forums Forums", and yet it seems to be active. I hope I'm posting to the correct place.


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects


    Wednesday, May 8, 2013 1:30 AM

Answers

  • Hi,

    It also works if we leave TargetConnectionString in the manifest file. If we put TargetConnectionString and use /cs option at the same time, they cannot be combined together, but the command line switch /cs will take precedence.

    Below is the connection string specified in the manifest:

    <TargetConnectionString>Data Source=server1\instance;Integrated Security=True;Pooling=False</TargetConnectionString>

    Below is the command to deploy:

    "c:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe" /dd /dsp:SQL /p:TargetDatabase=KJ  /a:Deploy /manifest:MaBase.deploymanifest /cs:"Data Source=server2\instance;User ID=sa;Password=myPassword"

    it also successful, and it will be deployed to server2, not server1. 

    Windows authentication is much more preferred then SQL authentication, and more customer uses Windows authentication. If we leave SQL password in the manifest or other places, it is a security hole. Also, consider that user may change SQL password, and the original password may not work. In the Deployment tab, if we enter the password, the password will not be stored in the manifest. The purpose of the password entering is for "TEST CONNECTION", (there is a TEST CONNECTION button in the Connection properties), it is not for password saving, because if we leave password some places, and later transfer the project to other person, it is a BIG security hole.

    • Marked as answer by John Saunders Monday, June 3, 2013 10:31 PM
    Tuesday, May 14, 2013 6:28 AM

All replies

  • Hello,

    Thank you for your post.

    As far as I know, this error probably means there's a problem with your connection string and VS cannot connect to the target database. Can you recheck your connection string? If you deploy the database project from VS IDE, in the Connection Properties dialog, where the connection string is set, whether the Test Connection button reports Success?

    For more information, please see this thread with similar issue:

    http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/08ebf2b1-af25-4c73-9261-237ea3843987

    In addition, please refer to the example of specifying the deployment manifest on the command line in the following thread and try on your side:

    http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/dabd14fa-a805-4855-b3f1-33b37b16c5b1

    If you directly use /cs switch with the correct connection string(excluding the provider) instead of defining $connectionString, will you see this issue?

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, May 9, 2013 1:44 AM
    Moderator
  • The connection string is correct. In particular, I tried it with the connection string literally in the /cs. The connection string was hand-copied out of the deployment manifest. Further, if I add the password to the connection string in the deployment manifest, and if I do not include /cs on the command line, then the process works.

    Naturally, I would like to avoid hand-editing the deployment manifest. That won't work very well in an unattended situation.


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects

    Thursday, May 9, 2013 6:56 PM
  • Hello,

    As my personal point of view, if you want to specify password using that script when deploying rather than specifying it in deployment manifest, I am afraid that the connection string without password in deployment manifest and another connection string with the specific password in script may cause conflicts.

    For a better way to avoid hand-editing the deployment manifest to specify the password so that you can deploy database project to multiple database instances, I did some search and don’t find any approaches. So I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 10, 2013 2:21 AM
    Moderator
  • Hi,

    Could you please try with command line option to see whether it is successful or failed?  In my test, I removed <TargetConnectionString></TargetConnectionString> from the manifest file.

    and run command like below:

    "c:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe" /dd /dsp:SQL /p:TargetDatabase=KJ  /a:Deploy /manifest:MaBase.deploymanifest /cs:"Data Source=MYSERVER\MYINSTANCE;User ID=sa;Password=myPassword"

    It is successful.

    Sunday, May 12, 2013 12:24 PM
  • That experiment worked:

    1. Remove the <TargetConnectionString> element from the manifest
    2. Deploy using /manifest and /cs

    If I can't use both, then I would prefer to store the password in the manifest file. This is because I have a different server specified for each build configuration. I have not been able to get the password to appear in the manifest file.

    What do people generally do for deployment into production environments? Use Integrated Security?


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects

    Monday, May 13, 2013 5:37 PM
  • So, here's the bottom line. I need one of two things:

    1. In the ideal world, I wouldn't have passwords stored in database projects (and therefore in database manifest files). The user doing the deployment would supply the password at deployment time. In this case, there would be no password in the deployment manifest file, but the connection string in that file would still exist, as it would specify the server name and instance to use, based on build configuration.
    2. For deployment into our dev and QA environments, I would permit the password to be stored somewhere, so that our TFS automated builds can deploy the database automatically. In this case, it would be acceptable for the password to reside in the deployment manifest file. Unfortunately, I have not yet been able to get a password into that file, except by hand editing.
    3. How is this normally done? Using Windows Authentication?
    4. It was an unpleasant surprise to find that I can enter the password on the Deployment tab of the database project, and yet the password is not stored anywhere. I found it quite embarassing that other developers were unable to perform the same deployment I had just performed.

    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects

    Monday, May 13, 2013 6:52 PM
  • Hi,

    It also works if we leave TargetConnectionString in the manifest file. If we put TargetConnectionString and use /cs option at the same time, they cannot be combined together, but the command line switch /cs will take precedence.

    Below is the connection string specified in the manifest:

    <TargetConnectionString>Data Source=server1\instance;Integrated Security=True;Pooling=False</TargetConnectionString>

    Below is the command to deploy:

    "c:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe" /dd /dsp:SQL /p:TargetDatabase=KJ  /a:Deploy /manifest:MaBase.deploymanifest /cs:"Data Source=server2\instance;User ID=sa;Password=myPassword"

    it also successful, and it will be deployed to server2, not server1. 

    Windows authentication is much more preferred then SQL authentication, and more customer uses Windows authentication. If we leave SQL password in the manifest or other places, it is a security hole. Also, consider that user may change SQL password, and the original password may not work. In the Deployment tab, if we enter the password, the password will not be stored in the manifest. The purpose of the password entering is for "TEST CONNECTION", (there is a TEST CONNECTION button in the Connection properties), it is not for password saving, because if we leave password some places, and later transfer the project to other person, it is a BIG security hole.

    • Marked as answer by John Saunders Monday, June 3, 2013 10:31 PM
    Tuesday, May 14, 2013 6:28 AM