Last post Mar 24, 2011 11:31 AM by Kyralessa
Mar 18, 2011 05:54 PM|Kyralessa|LINK
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?
Mar 18, 2011 08:19 PM|Kyralessa|LINK
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?
Mar 24, 2011 06:38 AM|XiaoCheng Fan - MSFT|LINK
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:
I hope this can be helpful for you.
Mar 24, 2011 11:31 AM|Kyralessa|LINK
After more experimentation over the last two or three days, I figured out the problem.