locked
VS 2010 One-Click Deploy uses different SQL Server user from packaged deploy? RRS feed

  • Question

  • User1426717342 posted

    I'm working on an ASP.NET MVC 3 project which uses One-Click Deploy, with Web Deploy running on the development server I'm deploying to.  The project includes a SQL Server database.  I have it set up in the project properties, completely with a deployment-only connection string (in Connection string for destination database) with a SQL Server login (call it BuildGuy) that has dbcreator rights.

    I'm also using a build server (Jenkins).  I have it set up to use the command line to build my project, run tests, create a deployment package, and then deploy to our shared development server.

    All of this works from the build server except the SQL Server deployment piece.  I'm using ScriptDropsFirst in Presource in my project to drop the tables, then re-create them.  When I do the one-click deployment from Visual Studio 2010, it works fine.  When the build server deploys from the package, the SQL Server piece fails with this message: "The user does not have permission to perform this action."  The SQL Server deploy only succeeds from the build server if the login, BuildGuy, has the sysadmin role.

    Meanwhile, I tried removing both sysadmin and dbcreator from BuildGuy and found that the deploy still works when I do it from Visual Studio 2010.  It appears that Visual Studio 2010 is using *my* credentials in SQL Server instead of BuildGuy's credentials from the deployment connection string.

    Has anyone else seen this behavior?  Is there anywhere a list of exactly what roles or rights a SQL Server user needs to have in order to successfully do the SQL Server deployment piece of a One-Click Deploy?

    Friday, March 18, 2011 5:54 PM

Answers

  • User470413944 posted

    Hi,

    As far as I know, One-Click Deploy will use your user for deploying website and the user put in connection string is for connect to SQL Server on runtime.

    For more about One-Click Deploy:

    http://msdn.microsoft.com/en-us/library/dd483479.aspx

    I hope this can be helpful for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 6:38 AM
  • User1426717342 posted

    After more experimentation over the last two or three days, I figured out the problem.

    • The source connection string is used to read from the source database, and the destination connection string is used to create tables, users, etc. in the destination database.  Thus neither of these has to be the same as the application's read/write connection string.
    • Further, the user in each one has to actually have access to that database.  One problem I had is that SQL Server Express doesn't allow remote connections by default, so my build server user couldn't get to the model database on my local instance of SQL Server Express.
    • I haven't had this confirmed from Microsoft, but it appears that a SQL Server user on the destination database needs to have (at least if the database and/or its objects will be dropped) the roles dbcreator and processadmin.  dbcreator is used to create the database objects, obviously, and processadmin is used to kill any existing connections to the database before it or its objects are dropped.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 11:31 AM

All replies

  • User1426717342 posted

    To further experiment, I tried running Visual Studio 2010 as a different user, one without sysadmin rights in SQL Server, and found that the One-Click Deploy failed.

    So my question is: Why does a One-Click Deploy from Visual Studio 2010 use my user's credentials instead of the credentials of the user I put in the deployment connection string?

    Friday, March 18, 2011 8:19 PM
  • User470413944 posted

    Hi,

    As far as I know, One-Click Deploy will use your user for deploying website and the user put in connection string is for connect to SQL Server on runtime.

    For more about One-Click Deploy:

    http://msdn.microsoft.com/en-us/library/dd483479.aspx

    I hope this can be helpful for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 6:38 AM
  • User1426717342 posted

    After more experimentation over the last two or three days, I figured out the problem.

    • The source connection string is used to read from the source database, and the destination connection string is used to create tables, users, etc. in the destination database.  Thus neither of these has to be the same as the application's read/write connection string.
    • Further, the user in each one has to actually have access to that database.  One problem I had is that SQL Server Express doesn't allow remote connections by default, so my build server user couldn't get to the model database on my local instance of SQL Server Express.
    • I haven't had this confirmed from Microsoft, but it appears that a SQL Server user on the destination database needs to have (at least if the database and/or its objects will be dropped) the roles dbcreator and processadmin.  dbcreator is used to create the database objects, obviously, and processadmin is used to kill any existing connections to the database before it or its objects are dropped.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 24, 2011 11:31 AM